Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    <img src=/S/please.gif border=0 alt=please width=31 height=23>
    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!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    <pre>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
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks Hans... but it didn't work...
    I still got prompted to save again... and the formatting didn't save... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks Legare... Using your procedure I didn't get prompted on save....
    However, the formatting still didn't save... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks Pieter... I'll give that a shot...
    If I can't figure out how to do it, I'll be back... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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...
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I guess I was worried over nothing... Go figure... As long as it works right? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks again for everyone's help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •