1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel error when opening file with VB script

Discussion in 'Software Development' started by FinTechie, Feb 23, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. FinTechie

    FinTechie Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    12
    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
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    Hi welcome to the forum.

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

    FinTechie Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    12
    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
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    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?
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  6. FinTechie

    FinTechie Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    12
    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 Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    Post script and excel file to test.
    (Back on Saturday)
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  9. FinTechie

    FinTechie Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    12
    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.
     
  10. FinTechie

    FinTechie Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    12
    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.
     
  11. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1042337