Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel error when opening file with VB script


(!)

FinTechie's Avatar
FinTechie FinTechie is offline
Computer Specs
Member with 12 posts.
THREAD STARTER
 
Join Date: Feb 2012
Experience: Beginner
23-Feb-2012, 11:48 AM #1
Solved: Excel error when opening file with VB script
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's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,269 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
25-Feb-2012, 06:32 PM #2
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's Avatar
FinTechie FinTechie is offline
Computer Specs
Member with 12 posts.
THREAD STARTER
 
Join Date: Feb 2012
Experience: Beginner
27-Feb-2012, 10:57 PM #3
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's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,269 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
28-Feb-2012, 02:46 AM #4
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's Avatar
Member with 4,693 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
28-Feb-2012, 04:52 PM #5
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's Avatar
FinTechie FinTechie is offline
Computer Specs
Member with 12 posts.
THREAD STARTER
 
Join Date: Feb 2012
Experience: Beginner
29-Feb-2012, 10:01 PM #6
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.
Attached Thumbnails
Solved: Excel error when opening file with VB script-excel-error.jpg  
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,269 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
01-Mar-2012, 03:39 AM #7
Post script and excel file to test.
(Back on Saturday)
Rollin_Again's Avatar
Member with 4,693 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
01-Mar-2012, 08:07 AM #8
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

Last edited by Rollin_Again; 01-Mar-2012 at 08:18 AM..
FinTechie's Avatar
FinTechie FinTechie is offline
Computer Specs
Member with 12 posts.
THREAD STARTER
 
Join Date: Feb 2012
Experience: Beginner
04-Mar-2012, 07:02 PM #9
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's Avatar
FinTechie FinTechie is offline
Computer Specs
Member with 12 posts.
THREAD STARTER
 
Join Date: Feb 2012
Experience: Beginner
24-Mar-2012, 04:27 PM #10
Quote:
Originally Posted by Rollin_Again View Post
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑