Executing excel SaveAs from Access

[Guest]

I am trying to open and save an imported .csv file as an excel workbook, but
the "saveas" method returns the error message "saveas method of workbook
class failed." The code is as follows:

Private Sub btnTest_Click()

Dim oApp As Object
Dim oSource As Object
Dim strSource As String
Dim strDest As String

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.UserControl = True

strSource = "C:\Documents and Settings\ccrcsg1dxm\My
Documents\Excelwrk\Impts\CCW_VNDR_PYMNT_STATUS_ACCTG_DT.csv"
strDest = "C:\Documents and Settings\ccrcsg1dxm\My
Documents\Excelwrk\Impts\apimpt"
Set oSource = oApp.workbooks.Open(strSource)
oSource.SaveAs Filename:=strDest, _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
oSource.Close
oApp.Close

End Sub

Does "saveas" have to be run inside Excel? Can this procedure be executed
in the background, without making excel visible.

Thanks,

DM

 

[Ken Snell \(MVP\)]

The "xlExcel9795" intrinsic constant has a value of 43 in EXCEL. Because
you're using late binding in this code (which is good), ACCESS does not know
what the "EXCEL library" value of "xlExcel9795" variable is, so it gets a
value of 0 from ACCESS.

Replace xlExcel9795 with the number 43 and try it again.