Automation using Excel says, "ERROR 1004: Open method of Workbooks class failed"

I have an Access 97 database that runs code overnight via an automated batch process.  I create various XLS files for my clients throughout the office.  While the code within Access always runs on Access 97, some clients have Excel 97 installed while others have Excel 2003.  The following process works just fine with Excel 97, but on a computer with 2003, it errors.  I'm looking for a solution that can work independent of the Excel version a user has installed.

I have a report (again, in Access 97) that is output to an XLS file during this nightly process via the following line of code:
DoCmd.OutputTo acOutputReport, strDocName, acFormatXLS, strTempFileName

I then want to simply open the XLS document, format the cells for my clients, then save the XLS document again.
It currently looks like this:

Public Function XLCellFix(strTempFileName As String)

On Error GoTo Error_Handler
    'Bind so that it will run on any machine
    Dim oXL As Object
    Set oXL = CreateObject("Excel.Application")
    With oXL
        'Open the passed-in XLS document
        .application.Workbooks.Open (strTempFileName)

        'Autofit all rows and columns for easier viewing by customer
        With .ActiveSheet.Cells
        End With

        'Close document and automatically save the changes
    End With
    Exit Function

         AddToLogFile "ERROR " & Err.number & ": " & Err.Description & " (" & Err.Source & ")", LOG_ERROR
         Resume Next

End Function

On the computers with Excel 97, it correctly opens Sheet1, formats, and closes.
On the computers with Excel 2003, it bombs on this line of code:
      .application.Workbooks.Open (strTempFileName)

and I see the following error in my logfile:
      "ERROR 1004: Open method of Workbooks class failed (Microsoft Office Excel)"

When I open the file manually through Excel 2003, I see the problem - a dialogue box pops up that says:
"Errors were detected in 'filename.xls,' but Microsoft Excel was able to open the file by making the repairs below.
    Renamed invalid sheet name."

The sheet has been renamed to Recovered_Sheet1 in this case.  Saving it manually will fix the problem and let the code run on the XLS file, but that obviously defeats the purpose.  I cannot find a way to automatically close the error message box; Access 97's automation just throws that error and wont open the XLS file.

Any help would be greatly appreciated!!!
Who is Participating?
rockiroadsConnect With a Mentor Commented:
Would specifying Converter help in this case?
Without myself reproducing this, I can only give suggestions

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad, OpenConflictDocument)

Password  Optional Variant.  A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.

WriteResPassword  Optional Variant.  A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.

IgnoreReadOnlyRecommended  Optional Variant.  True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Origin  Optional Variant.  If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Delimiter  Optional Variant.  If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons, or use a custom character. Only the first character of the string is used.

Editable  Optional Variant.  If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn't apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.

Notify  Optional Variant.  If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter  Optional Variant.  The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

AddToMru  Optional Variant.  True to add this workbook to the list of recently used files. The default value is False.

Local  Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad  Optional Variant. Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The Default behavior if no value is specified is usually normal but may be safe load or data recovery, if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file the second attempt is safe load. If Excel again stops operating the next attempt is data recovery.

OpenConflictDocument  Optional Variant. True to open the local conflict document. Default is False.

just try

.Workbooks.Open (strTempFileName)

SkkraAuthor Commented:
Tried it... no go.  Same issue.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

ok, hang on, I just read the last part, didnt scroll down enough
Now Im thinking the fact you got a corrupt spreadsheet, should you really be working on it?
Have u looked into why it keeps getting corrupt?
SkkraAuthor Commented:
Its not actually corrupt, really.  The data is all fine no matter how I open it, and there is nothing wrong with opening the spreadsheet in Excel 97.  I just that odd error when using Excel 2003, and even then its only concerning the sheet name for some reason.
SkkraAuthor Commented:
I definitely understand you can only give suggestions without having this yourself.  Sadly, I actually tried specifying converter before with no luck.
Try this...when creating the object, also specify the sheet

    Set xlWb = xlApp.Workbooks.Open(strFile, True)
    Set xlWs = xlWb.Worksheets(strSheet)
Have a look at   CorruptLoad  

u see there are thee values that it can be


read the help on that, that may be the one (and not Jet Li)
SkkraAuthor Commented:
Sorry; it still fails.
I think I may just have to code some convoluted method to work around it using C#.NET externally or something...
Does that excel sheet fail fail generally?
I was wondering if u could upload it in something like (minus your sensitive stuff)
so I can have a look to see if I can reproduce it
SkkraAuthor Commented:
No, it does not fail generally.  Sadly I cant upload a file where you could see it yourself.  The data the report runs from is confidential production data, and if I open the file to edit those values to dummy ones, the error will stop happening.  Sorry.

I ended up having to recode it in the macro of ANOTHER excel sheet, which I the open via Access and run the macro.  Seems to work once excel 2003 is already open with my "valid" XLS file, but sadly nothing mentioned above would seem to do it from Access, which frustrates the heck out of me - my way is such a convoluted workaround...
Pity, I thought the parameters may help, especially  CorruptLoad  as that sounds like the solution to your issues

When u tried it, did u try all three options (I guess xlNormalLoad is the default)

also after u open the workbook, set visible=true so u can see what is going on

Perhaps that may be a workaround? setting visible to True so that the user can see what is going on and act accordingly
SkkraAuthor Commented:
I have actually had Visible set to true this entire time for my debugging purposes, but this is run overnight in a batch process, so no users will be interacting with it.

Yes, I tried all three recovery options.  Sadly none of the CorruptLoad parameters could solve the problem UNTIL I ran them inside of that XLS file I created, in a macro.  However, thank you very much for all of your help, I appreciate it.  I will award you the points for your effort, and since you halfway found the solution; it just couldnt be done inside Access for whatever freak reason!
Real shame, sorry I couldnt help further
thanks for rewarding me though, appreciated
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.