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"
MsgBox Err.Number & " - " & Err.Description
As always... TIA!!