WorkBooks.Open Fails When A Reference to Excel Is Made

[Pradeep]

Hello,

I have come across the following scenario and not able to make out the exact
reason for the behaviour.

I have a textbox in MS Access into which the name of workbook which needs to
be opened is keyed in. The following code is written in the click event of
the command button which opens the workbook. The workbook has links.

Dim objExcel As Excel.Application,objWrkBook as Excel.WorkBook

set objExcel= New Excel.Application
set objWrkBook= objExcel.WorkBooks.Open(txtWrkBkName.Value,0,True) - Fails
here

The above code fails at the user's end with an error Automation Error -
Server Threw An Exception. It however works fine on my system and colleagues
in my office.

I have changed the code to the following and it started working well at the
user's end too:

Dim objExcel As Object,objWrkBook as Object

set objExcel= CreateObject("Excel.Application")
set objWrkBook= objExcel.WorkBooks.Open(txtWrkBkName.Value,0,True)

The above code works fine at all the locations.

Ms Access Version - 2002
Ms Excel Version - 10

Both the user and developer ends are configured in such a way that they are
identical.

I would appreciate if someone could explain the reason for this behaviour.

Cheers,

Pradeep

 

[Andy Hull]

Hi

This is my understanding...

When you use "set myApp = Excel.Application" Access has to "know" about this
thing Excel.Application which it looks for in its references. If I go into
the VBA editor and click on Tools and References I have a tick by "Microsoft
Excel 9.0 Object Library" - it is this library that alows me to use the
Excel.Application in the above way.

If I have Excel 10 installed on my system, Access will still set myApp to be
an Excel 9.0 application because of the above reference.

If you use CreateObject("Excel.Application") the object name is in quotes so
is just a string to Access and Access doesn't have to "know" about the
object. Instead, the registry is searched for that object name and the
relevant library is loaded according to the registry settings - so with Excel
10 installed - the registry would point to the Excel 10 library.

In your case, maybe the user has Excel 10 installed but Access only has the
version 9 Object Library?

Hope this helps

Andy Hull

 

[Pradeep]

Hello Andy,

I appreciate your quick response in this regard.

Exactly because of the same thought in mind, i checked references dialog box
with that of the user's and both of them point to Microsoft Excel 10.0 Object
Library with executables present in the locations specified in the references
box.

That is the reason, i specified in the post that the configurations at the
developer and the user end are same.

Is there any way to determine as to what object library is being loaded in
the first case?

Cheers,

Pradeep

 

[Andy Hull]

Hi

Not sure on that one I'm afraid. Only other thing I can think of is if both
Excel V9 & V10 references are present (and ticked) with the V9 higher up the
list than the V10.

Access will use the first reference containing the Excel.Application object.

Regards

Andy Hull

 

[Pradeep]

Andy,

I did check with the user by doing a net meeting and found that there is
only one reference and that points to Excel 10.0.

But even I beleive there is some other version that is getting loaded into
memory when using the Excel. Application which is causing the problem.

Thanks for all the help.

Cheers,

Pradeep