[FinTechie]

I am fairly new to VB. I am using a vb script to open an excel file. It works on two of our computers. However 3 other co-workers get an error when they run the same script. Excel opens a new workbook and pops up an error windows that says "Excel has encountered and error and needs to close. We are all on windows XP, Excel 2007.

Below is the script. There is a larger script, but i narrowed the error down to this set of lines. When i copied these into a .vbs all by themselves I get the error on the three computers. So I know this is culprit. I know the file and path are correct (since it works on 2 out of the 5 computers) and all have the same access to the same network drive. Do I need to declare teh objExcel, or am I missing something else? Your help/advice will be great appreciated.

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "Fully Qualified Path\FileName.xlsx"
objExcel.Visible = True

[Keebellah]

Hi welcome to the forum.

Can these people open the Execl file without a problem without the VB script? Just double clicking the file?

[FinTechie]

Thanks. Yes, all users can open the file manually through excel or by double clicking on it. However they cannot through the script. I have also tried:

1. Excel Diagnostics (detect and repair)
2. Updating anti-virus
3. Ensuring the script folder and excel file folder are trusted locations.
4. Ensured that there is not another instance of excel running.
5. Rebooting PC

[Keebellah]

If I run your script in vbs if of course get the error for the path, but what does it say
instead of "Fully qualified path\Filename.xlsx" ?

Do you also run a macro when Excel opens?

[Rollin_Again]

What versions of Excel are you running on each PC? Is it possible you are trying to open the new filetype (.xlsx) with an older version of Excel that only recognizes .xls extensions ?

Rollin

[FinTechie]

All computers are on Excel 2007. In terms of a macro running when it opens... the file we are trying to open doesn't have macros or vba. It is a dummy file with text in cells A1:A3.

When I first encounteed the error i cut everything down to simplest form to narrow the scope (i.e. eliminate all other variables)... but still get the error even with the script as shown and the file very basic.

It may be possible that some are using a personal.xls, vs personal.xlsb, vs personal.xlam. However, we get the error regardless of whether excel is already open or not. So i'm not sure that is the issue. However, i will test that next just to see.
Below is the error that pops up when we run the script.

[Keebellah]

Post script and excel file to test.
(Back on Saturday)

[Rollin_Again]

Instead of declaring Excel as type "Object" have you tried using early binding and setting reference to the Excel Object library beforehand ? Also try disabling any add-ins in Excel as they may be interfering with the automation. Are there any formulas or data in the workbook that links to other workbooks or applications?

Rollin

[FinTechie]

I found the solution:

Set objExcel = CreateObject("Excel.Application")
set wbX = objExcel.Workbooks.Open("Fully Qualified path name\Filename.xlsx")
objExcel.Visible = True

This seems to work on all the computers with no errors.

[FinTechie]

Originally Posted by Rollin_Again

Instead of declaring Excel as type "Object" have you tried using early binding and setting reference to the Excel Object library beforehand ? Also try disabling any add-ins in Excel as they may be interfering with the automation. Are there any formulas or data in the workbook that links to other workbooks or applications?

Rollin

Thanks Rollin. Good call on the addins.

The problem was that I couldn't get my original script to work on all computers. Some it worked and some not. But there was no meaningful error message ie when trying to open an excel file using the original script, all it would tell me was that "Excel has encountered and error and had to close". So I was trying to hard code a file path / name as opposed to using the prompt.
Resolution:
Fortunately, yesterday I figured out that it the Excel Addins / Coms were different between the computers. This was causing some computers to successfully execute the code and others not. Once I disabled un-needed addins/ coms the script now works on all the computers. So the original script is working as designed and all users are happy and i can keep my original script as opposed to hardcoding the path/file name.
Thanks for all the responses.