How do I open a spreadsheet from Access and export CSV file?

[hollifd]

Using VBA, how do I open a spreadsheet from Access and export the spreadsheet as a CSV file? All help will be appreciated.

Thanks,

David

 

[hollifd]

Re: How do I open a spreadsheet from Access and export CSV f

Here is how it can be done...

Private Sub Command0_Click()

'You must set a reference to Excel for this automation to work

Dim objXL As Object, x
Dim MyFileNameIn As String
Dim MyFileNameOut As String

Let MyFileNameIn$ = "C:\Temp\Junk.xls"
Let MyFileNameOut$ = "C:\Temp\Junk.csv"

On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.workbooks.Open MyFileNameIn$
.activeworkbook.saveas FileName:=MyFileNameOut$, FileFormat:=xlcsv, createBackup:=False
.activeworkbook.Close savechanges:=False
.Application.Quit
End With
Set objXL = Nothing
MsgBox "Done exporting as csv."

End Sub