Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2005

Tip: Looking for answers? Try searching our database.

Excel SaveAs from Access Error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JeepNC - 31 Jan 2005 18:18 GMT
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
anonymous@discussions.microsoft.com - 31 Jan 2005 19:27 GMT
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

>-----Original Message-----
>I have the following code attempting to save an open xl workbook as a tab
[quoted text clipped - 20 lines]
>    Set xlx = Nothing
>.
anonymous@discussions.microsoft.com - 31 Jan 2005 19:37 GMT
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.

>-----Original Message-----
>hi,
[quoted text clipped - 32 lines]
>>
>.
JeepNC - 31 Jan 2005 19:45 GMT
Thanks for the suggestion.  I tried the sheet save and now get an error
SaveAs method of worksheet class failed.

> hi again,
> sorry, click the button to soon
[quoted text clipped - 40 lines]
> >>
> >.
anonymous@discussions.microsoft.com - 31 Jan 2005 20:50 GMT
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.

>-----Original Message-----
>Thanks for the suggestion.  I tried the sheet save and now get an error
[quoted text clipped - 46 lines]
>>
>.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.