Saving format changes to .csv file (2000 SR-1)

Topic

New Reply

I’m using VBA within Access, to create .csv files from temporary querydef recordsets… I create the file, then have to open the file and change the formatting to two columns, save and close the workbook (preferrably without prompting the user)… Everything is working fine until the workbook closes… The format changes are not being saved… If I step through the code it does make the changes required, but it isn’t changed when I re-open the file later… and it keeps prompting me asking me if it should replace the existing file… I’ve tried setting DisplayAlerts to False, no luck… I’ve tried using SaveAs, no luck… I’m missing something here… Can someone help me?

Here’s the code I’ve got so far… (and please feel free to help me clean it up if I’m doing something silly… I’m not the best at using Excel VBA within Access yet)

Sub FormatCSVs(strFile As String)
On Error GoTo Err_FormatCSVs

Dim xApp As Object
Dim wbkJournal As Workbook

Set xApp = CreateObject(“Excel.Application”)
xApp.Visible = True
On Error Resume Next
xApp.UserControl = True

Set wbkJournal = xApp.Workbooks.Open(strFile)

xApp.ActiveSheet.Columns(“A:A”).NumberFormat = “yyyy-mm-dd”
xApp.ActiveSheet.Columns(“E:E”).NumberFormat = “0.00”
xApp.ActiveWorkbook.Close SaveChanges:=True
xApp.Quit

Exit_FormatCSVs:
Exit Sub

Err_FormatCSVs:
MsgBox Err.Number & ” – ” & Err.Description
Resume Exit_FormatCSVs
End Sub

As always… TIA!!

Reply | Quote

Replies

Hi Trudi,

One problem might be that you don’t release the object memory by setting the objects to Nothing. A minor point is that Excel doesn’t need to be visible, and UserControl doesn’t need to be set to True; this shouldn’t prevent the workbook from being saved, however. Finally, I would refer to the workbook explicitly. Modified code follows below; I recommend restarting your computer before testing it, to remove residus of unreleased objects from memory.

Sub FormatCSVs(strFile As String)
‘ Declare types explicitly if you have a reference
‘ to the Microsoft Excel 9.0 Object Library.
‘ Otherwise, declare both As Object
Dim xApp As Excel.Application
Dim wbkJournal As Excel.Workbook

On Error GoTo Err_FormatCSVs

‘ Start Excel invisibly
Set xApp = CreateObject(“Excel.Application”)
‘ Open workbook
Set wbkJournal = xApp.Workbooks.Open(strFile)

‘ Modify format
With wbkJournal.ActiveSheet
.Columns(“A:A”).NumberFormat = “yyyy-mm-dd”
.Columns(“E:E”).NumberFormat = “0.00”
End With
‘ Close and save workbook
wbkJournal.Close SaveChanges:=True

Exit_FormatCSVs:
‘ Clean up
On Error Resume Next
Set wbkJournal = Nothing
xApp.Quit
Set xApp = Nothing
Exit Sub

Err_FormatCSVs:
‘ Inform user
MsgBox Err.Number & ” – ” & Err.Description
‘ Always clean up, even if error occurred
Resume Exit_FormatCSVs
End Sub

Reply | Quote

Thanks Hans… but it didn’t work…
I still got prompted to save again… and the formatting didn’t save…

Reply | Quote

Thanks Hans… but it didn’t work…
I still got prompted to save again… and the formatting didn’t save…

Reply | Quote

Hi Trudi,

One problem might be that you don’t release the object memory by setting the objects to Nothing. A minor point is that Excel doesn’t need to be visible, and UserControl doesn’t need to be set to True; this shouldn’t prevent the workbook from being saved, however. Finally, I would refer to the workbook explicitly. Modified code follows below; I recommend restarting your computer before testing it, to remove residus of unreleased objects from memory.

Sub FormatCSVs(strFile As String)
‘ Declare types explicitly if you have a reference
‘ to the Microsoft Excel 9.0 Object Library.
‘ Otherwise, declare both As Object
Dim xApp As Excel.Application
Dim wbkJournal As Excel.Workbook

On Error GoTo Err_FormatCSVs

‘ Start Excel invisibly
Set xApp = CreateObject(“Excel.Application”)
‘ Open workbook
Set wbkJournal = xApp.Workbooks.Open(strFile)

‘ Modify format
With wbkJournal.ActiveSheet
.Columns(“A:A”).NumberFormat = “yyyy-mm-dd”
.Columns(“E:E”).NumberFormat = “0.00”
End With
‘ Close and save workbook
wbkJournal.Close SaveChanges:=True

Exit_FormatCSVs:
‘ Clean up
On Error Resume Next
Set wbkJournal = Nothing
xApp.Quit
Set xApp = Nothing
Exit Sub

Err_FormatCSVs:
‘ Inform user
MsgBox Err.Number & ” – ” & Err.Description
‘ Always clean up, even if error occurred
Resume Exit_FormatCSVs
End Sub

Reply | Quote

Try explicitely saving the file. The following has several changes.

Sub FormatCSVs(strFile As String)
    On Error GoTo Err_FormatCSVs

Dim xApp As Object
Dim wbkJournal As Workbook

    Set xApp = CreateObject("Excel.Application")
    xApp.Visible = True
    On Error Resume Next
    xApp.UserControl = True
    
    Set wbkJournal = xApp.Workbooks.Open(strFile)
    On Error GoTo Err_FormatCSVs
    If Not wbkJournal Is Nothing Then
        xApp.ActiveSheet.Columns("A:A").NumberFormat = "yyyy-mm-dd"
        xApp.ActiveSheet.Columns("E:E").NumberFormat = "0.00"
        Application.DisplayAlerts = False
        xApp.ActiveWorkbook.Save
        xApp.ActiveWorkbook.Close
        Application.DisplayAlerts = True
        xApp.Quit
    End If
Exit_FormatCSVs:
    Set wbkJournal = Nothing
    Set xApp = Nothing
    Exit Sub
    
Err_FormatCSVs:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_FormatCSVs
End Sub

Reply | Quote

Thanks Legare… Using your procedure I didn’t get prompted on save….
However, the formatting still didn’t save…

Reply | Quote

If you want the formatting to save, then you can not save it as a .CSV file. CSV files are basically text files with commas between data values. There is no way to save the formatting. You will have to use SaveAs and change the file type to .xls to save the formatting.

Reply | Quote

I know what you’re saying Legare… but check this out…

The reason I needed to do this is that the corporate brokerage I work for decided to start using this third party journal posting program (Say bye-bye to a few data entry employees… ) We have to import batch data into the program through .csv files… They provided .csv “business event” templates for me to use, but the data has to be in proper format for the import to work… I know these are essentially comma delimited text files but there’s definitely something different about them that I don’t understand…

I was playing with it again yesterday, and it turns out that the data is actually saved in the right format using a mix of Hans and your code… The weird thing is that if you open the file (after saving and closing) in Excel it doesn’t show the changes, but if I import the data it works… If I do not do the “change formatting and save” part of the code, the data does not import… Strange, but true…

I even tested this by opening the .csv file in Notepad both with and without doing the formatting/saving… The data shows different…

I guess I was worried over nothing… Go figure… As long as it works right?

Thanks again for everyone’s help!

Reply | Quote

I know what you’re saying Legare… but check this out…

The reason I needed to do this is that the corporate brokerage I work for decided to start using this third party journal posting program (Say bye-bye to a few data entry employees… ) We have to import batch data into the program through .csv files… They provided .csv “business event” templates for me to use, but the data has to be in proper format for the import to work… I know these are essentially comma delimited text files but there’s definitely something different about them that I don’t understand…

I was playing with it again yesterday, and it turns out that the data is actually saved in the right format using a mix of Hans and your code… The weird thing is that if you open the file (after saving and closing) in Excel it doesn’t show the changes, but if I import the data it works… If I do not do the “change formatting and save” part of the code, the data does not import… Strange, but true…

I even tested this by opening the .csv file in Notepad both with and without doing the formatting/saving… The data shows different…

I guess I was worried over nothing… Go figure… As long as it works right?

Thanks again for everyone’s help!

Reply | Quote

If you want the formatting to save, then you can not save it as a .CSV file. CSV files are basically text files with commas between data values. There is no way to save the formatting. You will have to use SaveAs and change the file type to .xls to save the formatting.

Reply | Quote

Thanks Legare… Using your procedure I didn’t get prompted on save….
However, the formatting still didn’t save…

Reply | Quote

Try explicitely saving the file. The following has several changes.

Sub FormatCSVs(strFile As String)
    On Error GoTo Err_FormatCSVs

Dim xApp As Object
Dim wbkJournal As Workbook

    Set xApp = CreateObject("Excel.Application")
    xApp.Visible = True
    On Error Resume Next
    xApp.UserControl = True
    
    Set wbkJournal = xApp.Workbooks.Open(strFile)
    On Error GoTo Err_FormatCSVs
    If Not wbkJournal Is Nothing Then
        xApp.ActiveSheet.Columns("A:A").NumberFormat = "yyyy-mm-dd"
        xApp.ActiveSheet.Columns("E:E").NumberFormat = "0.00"
        Application.DisplayAlerts = False
        xApp.ActiveWorkbook.Save
        xApp.ActiveWorkbook.Close
        Application.DisplayAlerts = True
        xApp.Quit
    End If
Exit_FormatCSVs:
    Set wbkJournal = Nothing
    Set xApp = Nothing
    Exit Sub
    
Err_FormatCSVs:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_FormatCSVs
End Sub

Reply | Quote

if your file really is a csv, then in essence it is a textfile and not an excel file, meaning you cannot store formatting (and other stuff) in it.
so you will need to explicitly set the format of the csv to an excel workbook when you save it.

Reply | Quote

Thanks Pieter… I’ll give that a shot…
If I can’t figure out how to do it, I’ll be back…

Reply | Quote

Thanks Pieter… I’ll give that a shot…
If I can’t figure out how to do it, I’ll be back…

Reply | Quote

if your file really is a csv, then in essence it is a textfile and not an excel file, meaning you cannot store formatting (and other stuff) in it.
so you will need to explicitly set the format of the csv to an excel workbook when you save it.

Reply | Quote