Run Excel Macro from Access and save changes to .CSV format

[vman92]

I am trying to run an Excel Macro from MS Access. The code will open a
..CSV file
along with my personal.xls. The desired result is to make some type of
modification to
the file (Remove extraneous commas, etc.) and then save it back into a
..CSV format.
The code seems to work but I believe it is erring when Excel fires a
dialog trying to
confirm if I really want to save into .CSV (loose any type of
formatting). In otherwords the
Macro functionality is not being retained in the .CSV file that results
from the process.

Private Sub CleanCsv_Click()

Dim xls As Excel.Application
Dim wk As Excel.Workbook
Dim wk1 As Excel.Workbook
Dim strPath As String
Dim strFile As String

Set xls = New Excel.Application
xls.Visible = True

strPath = "<My File Structure>\" 'directory where your spread
sheets are
strFile = Dir(strPath & "*.csv")

Do While strFile <> ""
Set wk1 = xls.Workbooks.Open("C:\Documents and
Settings\jvojtko1\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
Set wk = xls.Workbooks.Open(strPath & strFile)
xls.Run "PERSONAL.XLS!Macro2"
wk.Close True 'Save changes made by macro
wk1.Close True 'Save changes made by macro
xls.Quit

Set wk = Nothing
Set wk1 = Nothing
Set xls = Nothing
strFile = Dir
Loop

End Sub

 

[Larry Daugherty]

You're trying to debug Excel code from within Access. Can you run a
similar loop error free if it's entirely within Excel?

HTH

 

[Ron2006]

Using automation and xls format (not csv) I use the following to save
changes

es.ActiveWorkbook.Save
es.ActiveWorkbook.Close (False)

Try separating the save operation from the close operation. Although
you may still have a probelm with Excel wanting to save it in xls
fromat. But still better to separate the two operations.

If it really gets bad you could make the application visible just
before the save with instructions to reply. A poor way to do it though.

Have you thought opening it as a txt file and rewriting it with the
changes. That would all be in vba and not having to go through another
application.

Ron

 

[Douglas J. Steele]

If the file hasn't already been saved, you should be able to use

es.ActiveWorkbook.SaveAs _
FileName:=<full path to file>, _
FileFormat = xlCSV

If you're using Late Binding, replace xlCSV with 6.

 

[Douglas J. Steele]

Actually, that should work even if the file has already been saved.