Excel Automation problem

[DZ]

I want to use Automation to obtain properties from an Excel 97 workbook from
another Office App

Here is some code I tried. I need help getting it to work. When I run it, I
get an error message

"Run-time error '-2147417851 (80010105)' Method 'Open' of object
'Workbooks' failed"

The code is in an Access 97 form . OS Win XP
In the current database i have set a references to
Microsoft Excel 8.0 Objct Library
Microsoft Officel 8.0 Objct Library

I am able create a new instance of the Excel Application
But any manipulation of the App generates an error

The following code works, and I am able to create a new instance of Excel
I know this because I can observe Excel.Exe opening in the Windows Task
Manager

....but when attempt to use any of the commented out code to manipulate
Excel, the error mentioned above occurs

'Declaration section
Option Explicit
Dim xlApp As New Excel.application
Dim xlwbBook As New Excel.Workbook

Sub OpenExcelAndManipulate()

Dim stFile As String
stFile = "E:\New Files\Data.xls"

Set xlApp = New Excel.application
'xlApp.Visible = True

'Set xlwbBook = xlApp.Workbooks.Open(stFile)


End Sub

 

[Dale Fye]

DZ,

Is there a reason that you are declaring xlApp and xlWbBook outside the
subroutine? Although this should not make a difference, try it with them
inside the subroutine declaration

Dim xlApp As Excel.application
Dim xlwbBook As Excel.Workbook

Sub OpenExcelAndManipulate()

Dim xlApp As Excel.application
Dim xlwbBook As Excel.Workbook
Dim stFile As String

stFile = "E:\New Files\Data.xls"
if len(dir(stFile)) = 0 then
msgbox "File not found"
Exit sub
endif

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlwbBook = xlApp.Workbooks.Open(stFile)

End Sub

At some point, you should consider using late binding, to allow your code to
work with different versions of Office. To do this, I generally start out
doing what you have done:
1. create the reference to the object model
2. then declare the variables xlApp, xlWbk, ... using the Excel. syntax)
3. write my code, taking advantage of intellisense provided by the
reference to the object model
4. remove the reference to the object model
5. Change all my dim statements for the Excel objects to "as Object"

HTH
Dale