Excel SaveAs from Access Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code attempting to save an open xl workbook as a tab
delimited text file from with Access. When it runs I get the following error
and the sheet is not saved. Any suggestions will be greatly appreciated.

Here's the error "SaveAs method of workbook class failed"

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object

Set xlx = CreateObject("excel.application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\book1.xls")
Set xls = xlw.worksheets("sheet1")
xls.Columns("K:K").NumberFormat = "0"
xlw.SaveAs Filename:="C:\Book1.txt", _
FileFormat:=xlText, CreateBackup:=False

Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
 
hi,
if the wb has more than 1 sheet, the line
xlw.SaveAs Filename:="C:\Book1.txt will fail.
try something that save the specific sheet like
 
hi again,
sorry, click the button to soon
try something that save the specific sheet like
xls.SaveAs Filename:="C:\Book1.txt", _
FileFormat:=xlText, CreateBackup:=False
save the xls not the xlw.
 
Thanks for the suggestion. I tried the sheet save and now get an error
SaveAs method of worksheet class failed.
 
hi again,
for what ever reason, it is failing because of the file
format. i took that out of the code and got it to save but
ended up with garbage in the text file. i tried saveing it
as a CSV file but it failed on that too. i think it's a
systax problem.
 
Back
Top