Object variable or With block variable not set

[Guest]

I need to export a table as a text file but using the outputto or transfer
text options do not produce the file format I want. If I export the file as
Excel and then save it as text it's OK so I copied the following code to
automate the procedure but get an error 'Object variable or With block
variable not set.

Can anyone help?

Function Export()

Dim objExcel As Excel.Application

DoCmd.OutputTo acOutputTable, "MyTable", acFormatXLS, _
"c:\data\MyExcelFile.xls", False

Set objExcel = Excel.Application

objExcel.ActiveWorkbook.SaveAs Filename:= _
"C:\data\MyTextFile.txt", FileFormat:=xlText, _
CreateBackup:=False

objExcel.Quit
Set objExcel = Nothing
End Function

 

[Dirk Goldgar]

I need to export a table as a text file but using the outputto or
transfer text options do not produce the file format I want. If I
export the file as Excel and then save it as text it's OK so I copied
the following code to automate the procedure but get an error 'Object
variable or With block variable not set.

Can anyone help?

Function Export()

Dim objExcel As Excel.Application

DoCmd.OutputTo acOutputTable, "MyTable", acFormatXLS, _
"c:\data\MyExcelFile.xls", False

Set objExcel = Excel.Application

objExcel.ActiveWorkbook.SaveAs Filename:= _
"C:\data\MyTextFile.txt", FileFormat:=xlText, _
CreateBackup:=False

objExcel.Quit
Set objExcel = Nothing
End Function

First, about not getting the file format you want: have you tried
setting up an import/export specification? Maybe you could get the
output you want without having to go through Excel. You could also,
potentially, open a recordset on your table and write the text file
yourself using the built-in VB text I/O methods.

As for the problem in your code, I see nothing there that actually
creates an instance of the Excel application, nor anything that opens
the workbook you created in the Excel application after you would
normally have created it. I haven't spent all that much time automating
Excel, so the following code may not be exactly correct, but try this:

'----- start of (untested) revised code -----
Function Export()

Dim objExcel As Excel.Application

DoCmd.OutputTo acOutputTable, "MyTable", acFormatXLS, _
"c:\data\MyExcelFile.xls", False

Set objExcel = New Excel.Application

objExcel.Workbooks.Open "c:\data\MyExcelFile.xls"

objExcel.ActiveWorkbook.SaveAs Filename:= _
"C:\data\MyTextFile.txt", FileFormat:=xlText, _
CreateBackup:=False

objExcel.Quit
Set objExcel = Nothing

End Function
'----- end of revised code -----