Automation using Excel says, "ERROR 1004: Open method of Workbooks class failed"
Posted on 2006-07-06
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")
'Open the passed-in XLS document
'Autofit all rows and columns for easier viewing by customer
'Close document and automatically save the changes
AddToLogFile "ERROR " & Err.number & ": " & Err.Description & " (" & Err.Source & ")", LOG_ERROR
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:
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!!!