Excel automation through access - SaveAs csv (1 Viewer)

[stephen81]

Hi all

I'm trying to open an Excel file from within Access, then save the file back as a csv.

I'm currently using the following code...

Private Sub RunExcel()

Dim xlApp, xlBook As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add("X:\MyWorkbook.xls")

xlApp.Visible = True


xlBook.SaveAs FileName:="X:\MyWorkbook.csv", FileFormat:=xlCSV

Set xlApp = Nothing
Set xlBook = Nothing

End Sub

When I run the code, my workbook opens but doesn't appear to do anything else. If I remove the FileFormat argument from my SaveAs it saves the workbook successfully (but not as a csv).

Any ideas?

Thanks in advance.

 

[ByteMyzer]

Unless your Access file has a VBA reference to the Microsoft Excel Object Library, you will need to add the following line after the Dim statement:

[COLOR=navy]Const[/COLOR] xlCSV = 6

 

[stephen81]

Fantastic! Strangely I had tried using 6 instead of xlCSV, but it still didn't work...

I've added the reference to the Microsoft Excel Object Library and it's working perfectly now.

Thanks!

 

[stephen81]

Fantastic! Strangely I had tried using 6 instead of xlCSV, but it still didn't work...
QUOTE]

That'll be because I was using FileFormatNum:=6 instead of FileFormat:=6 perhaps?

 

[ByteMyzer]

You can do it without the Microsoft Excel Object Library if you re-write your code thus:

[COLOR=navy]Private Sub[/COLOR] RunExcel()
 
[COLOR=navy]Dim[/COLOR] xlApp [COLOR=navy]As Object[/COLOR], xlBook [COLOR=navy]As Object[/COLOR]
[COLOR=navy]Const[/COLOR] xlCSV = 6
 
[COLOR=navy]Set[/COLOR] xlApp = CreateObject("Excel.Application")
[COLOR=navy]Set[/COLOR] xlBook = xlApp.Workbooks.Open("X:\MyWorkbook.xls")
 
xlBook.SaveAs FileName:="X:\MyWorkbook.csv", FileFormat:=xlCSV
 
xlBook.Close [COLOR=navy]False[/COLOR]
[COLOR=navy]Set[/COLOR] xlBook = [COLOR=navy]Nothing[/COLOR]
 
xlApp.Quit
[COLOR=navy]Set[/COLOR] xlApp = [COLOR=navy]Nothing[/COLOR]
 
[COLOR=navy]End Sub[/COLOR]

 

[SOS]

And I would suggest using it the way ByteMyzer shows withOUT using the reference. It will be able to handle any version someone is on if you do, but if you have the reference set, it can cause problems with other versions that people may have.

I typically will use late binding (as shown by ByteMyzer) for production and sometimes use early binding (using the reference) during development and then I'll change it over to late binding when ready to release. The benefit of that is I can use intellisense with early binding but not with late binding.