Skkra
asked on
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.Applic
With oXL
'Open the passed-in XLS document
.application.Workbooks.Ope
'Autofit all rows and columns for easier viewing by customer
With .ActiveSheet.Cells
.Select
.EntireRow.AutoFit
.EntireColumn.AutoFit
End With
.ActiveSheet.Range("A1").S
'Close document and automatically save the changes
oXL.application.Workbooks(
.Quit
End With
Exit Function
Error_Handler:
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.Ope
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!!!
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.Applic
With oXL
'Open the passed-in XLS document
.application.Workbooks.Ope
'Autofit all rows and columns for easier viewing by customer
With .ActiveSheet.Cells
.Select
.EntireRow.AutoFit
.EntireColumn.AutoFit
End With
.ActiveSheet.Range("A1").S
'Close document and automatically save the changes
oXL.application.Workbooks(
.Quit
End With
Exit Function
Error_Handler:
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.Ope
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!!!
ASKER
Tried it... no go. Same issue.
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?
Have u looked into why it keeps getting corrupt?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(strFi
Set xlWs = xlWb.Worksheets(strSheet)
Set xlWb = xlApp.Workbooks.Open(strFi
Set xlWs = xlWb.Worksheets(strSheet)
Have a look at CorruptLoad
u see there are thee values that it can be
xlNormalLoad
xlRepairFile
xlExtractData
read the help on that, that may be the one (and not Jet Li)
u see there are thee values that it can be
xlNormalLoad
xlRepairFile
xlExtractData
read the help on that, that may be the one (and not Jet Li)
ASKER
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...
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 http://www.ee-stuff.com (minus your sensitive stuff)
so I can have a look to see if I can reproduce it
I was wondering if u could upload it in something like http://www.ee-stuff.com (minus your sensitive stuff)
so I can have a look to see if I can reproduce it
ASKER
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...
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
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
ASKER
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!
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
thanks for rewarding me though, appreciated
.Workbooks.Open (strTempFileName)
instead