ERROR 1004: Open method of Workbooks class failed

[Guest]

Greetings,
I've searched everywhere I could for a solution but have come up empty. My
issue is with the code listed below. We have an Access 97 db, attempting to
modify an Excel file. This code works with no problem when the version of
Excel is 97.
However, we need it to also work on Excel 2002 but receive the following
error:
"ERROR 1004: Open method of Workbooks class failed"
Is this a reference issue? Is doesn't seem to want to communicate with
Excel properly.
The references we have checked are:
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Microsoft Office 8.0 Object Library

I've tried using these as well (with no luck):
Microsoft Excel 10.0 Object Library
Microsoft Office 10.0 Object Library

Any help would be appreciated!
Here's the code:
**************
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
With .ActiveSheet.Cells
.Select
.EntireRow.AutoFit
.EntireColumn.AutoFit
End With
.ActiveSheet.Range("A1").Select
'Close document and automatcally save the changes
oXL.application.Workbooks(1).Save
.Quit
End With
Exit Function
Error_Handler:
AddToLogFile "ERROR " & Err.number & ": " & Err.Description & " ("
& Err.Source & ")", LOG_ERROR
Resume Next
End Function
*******************
Thanks in advance,
Rob M.

 

[Van T. Dinh]

The ".application" before Workbooks in unnecessary since you already create
an _Application_ object oXL.

However, this may not be the cause of the error.

Try the code on a different computer also.

 

[Guest]

Thank you for the response.
I removed ".application" and tried it on my computer and another computer
with the same setup and still received the same errors.

Also, when I look at the debeub window at the values for oXL, I see the
following:
"Application-defined or Object-defined error"
I have not been successful finding info on that message either.
So it appears it's not recognizing Excel.

Any further help would be appreciated.
Thanks,
Rob M.

 

[Van T. Dinh]

Just making sure: Have you got Excel installed on the PCs you tested?

 

[Guest]

Yes,
On both machines Excel 2002 is installed and the code is run from Access 97.
Could this be a problem with the Excel 2002 installation?

Rob M.

 

[Van T. Dinh]

I am thinking of the same but it is rare that you have 2 faulty
installations out of 2 (unless you PCs were setup using the same (faulty)
image.

Try:

.Workbooks.Open strTempFileName

Make sure strTempFileName is a valid path.

 

[Guest]

Van,
It looks like the issue is with the creation of the Excel file which happens
in another module.
The problem is with a Docmd.OutputTo command going from Access97 to Excel2002.
Once the file is created, when you open it, you received the "Renamed
invalid sheet name." error.
Any idea on how to elliminate that programatically?
I'll start a new post since it is a separate topic.
Thanks for you assistance!
Rob M.

 

[Van T. Dinh]

Sorry, I have never seen this error.

All I can suggest is to create a really simple test Table, says with 3
Fields and 5 Records and try Automation and OutputTo and see if errors
appear and hopefully, you can identify the cause.