Solved

Method 'SaveAs' of object '_Workbook' failed

Posted on 2011-09-09
13
933 Views
Last Modified: 2012-05-12
In Access I use VBA to open a single worksheet Excel file and save it as a text file. I never had a problem with it but it gives the above error on a user's PC in a different location.

    Set myXL = New Excel.Application
    myXL.Visible = False
    Set wb = myXL.Workbooks.Open(strXLfile)
    myXL.DisplayAlerts = False 'don't want dialog box
    With wb
        .SaveAs Filename:=strNewName, FileFormat:=xlTxtFileType
        .Close SaveChanges:=False
    End With
    myXL.DisplayAlerts = True


0
Comment
Question by:TimHudspith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36515952
You might get an error if the saveas file is already opened, or already exists

Dave
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 36515971
Tim

Are you sure you are using the correct file format?

I've never seen, and can't find a reference to, a format called xlTxtFileType.

Why not try just xlText?
0
 

Author Comment

by:TimHudspith
ID: 36515993
imnorie - I should have pasted in the whole procedure. 'xlTxtFileType' is actually an argument of type Excel.xlFileFormat in this subprocedure. The calling procedure can choose whether to save the the file as CSV (in which case it passes in the constant xlCSVWindows) or TXT (in which case it passes in xlTextWindows).
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TimHudspith
ID: 36515998
Additional info - the user is located in Taiwan and I think is using a Chinese-language Windows, if that makes any difference.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36516004
Do you mean it's passed as an argument to the function?

What happens when you don't and just use either of the file format constants you want to use?

Another thing that could be a problem is strFileName not being a valid filename.

How/where are you defining that?
0
 

Author Comment

by:TimHudspith
ID: 36516084
Yes, it is passed in as an argument defined in the caller subprocedure, and strFileName is defined by the location of the original Excel file, which is held in an Access table. This has worked fine on my PC for a long time with no problems. There is no question of the filepath being incorrect - I have eliminated that possibility.

I will try a dedicated procedure without the xlFileFormat argument being passed in and see how that fares.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36516089
Tim

You aren't using the filepath in the code.

What's the full error message anyway?

Is it basically what you posted, or is there something that could possibly point to where the problem is?
0
 

Author Comment

by:TimHudspith
ID: 36516100
The error message is exactly as per this question title: error number 1004, "Method 'SaveAs' of object '_Workbook' failed.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36516116
Tim

Where is the path in the code?

All you appear to be using is the filename, without the path the file will get save in what VBA/Windows regards as the current directory.
0
 

Author Comment

by:TimHudspith
ID: 36516132
The path is held in a table that contains a list of files to import. The import procedure loops down this table's recordset importing the files, most of which are text or csv - no problems there. When it encouters this particular Excel file there is a Yes/No field value in that table that indicates that it must call the subprocedure to convert the Excel workbook to a text file. The file path is read from the field into a string variable which is then passed to the subprocedure.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36516258
Tim

Could you please check the value of strNewName?

Does it include the full path and a valid filename?

That's for the user having the problem.
0
 

Author Comment

by:TimHudspith
ID: 36516281
I'll pick this question up again on Monday when the user is back in the office. Thanks for your help so far.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36516501
No problem.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question