Excel Automation

[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)

 

[Stuart McCall]

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)

I think you need to change this line:

Dim xlApp As New Excel.application

to:

Dim xlApp As Excel.application

because when this line executes:

Set xlApp = New Excel.application

it creates another instance (AFAIK)

 

[DZ]

Thanks for trying to help me

I made the change you suggested but I am still getting the error message,
but only when I excute the commented out lines. The instance is created
successfully

Here is the updated code

Dim xlApp As Excel.application
Dim xlwbBook As 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)

'MsgBox xlwbBook.Names(1).Name

End Sub

 

[Stuart McCall]

Thanks for trying to help me

I made the change you suggested but I am still getting the error message,
but only when I excute the commented out lines. The instance is created
successfully

Here is the updated code

Dim xlApp As Excel.application
Dim xlwbBook As 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)

'MsgBox xlwbBook.Names(1).Name

End Sub

Well that opens a file in Excel here, though a different file, obviously.
All I can suggest (not being very experienced at automating Excel) is to
check whether the file actually exists first:

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

If Dir$(stFile) = "" Then
MsgBox stFile & " doesn't exist"
Exit Sub
End If

 

[DZ]

Stuart, thank for letting me know that the code worked for you.

Its not working for me. The only part that works is the instantiation (set
xlApp = etc)
I know the instantiation works because I have Windows task manager open as I
trigger the code and I see Excel.Exe appear in the task manager. And when run
code that closes the xlApp, it disappears from the task manager. But when I
try to run the code with any other additional statements it crashes, It even
crash when I merely try to make the xlApp visible.

 

[Stuart McCall]

Stuart, thank for letting me know that the code worked for you.

Its not working for me. The only part that works is the instantiation (set
xlApp = etc)
I know the instantiation works because I have Windows task manager open as
I
trigger the code and I see Excel.Exe appear in the task manager. And when
run
code that closes the xlApp, it disappears from the task manager. But when
I
try to run the code with any other additional statements it crashes, It
even
crash when I merely try to make the xlApp visible.

It begins to sound like your Excel install is corrupt. The code is simple
(and common) enough. I certainly can't see anything that would crash Excel.
If noone else jumps in with an idea, you may have to re-install..

 

[DZ]

I got it.

It works using late binding

Thanks for the help

DZ