Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Excel (XP)

    I want to create a windows script file (.vbs) that will open an Excel (2003) workbook and run a particular macro within it. I believe I can open the file using the Shell object, but is it possible to then run a specific macro? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel (XP)

    Excel doesn't have a startup switch that lets you specify a macro to run, so you will have to create a macro named Auto_Open in a standard module in the workbook, or a Workbook_Open event procedure in the ThisWorkbook module. Either of these will be run automatically when the workbook is opened.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel (XP)

    Thank you. However, the following script says it can't find the specified file:
    SET myObj = Wscript.CreateObject("Wscript.Shell")

    myObj.Run ("C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE")

    Should it matter where the script is saved? Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel (XP)

    You wrote that you wanted to open an Excel workbook. Try something like this:

    Dim myObj
    Set myObj = GetObject("H:ExcelTest.xls")
    ' do something with the workbook
    ...
    myObj.Close False
    Set myObj = nothing

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Running Excel (XP)

    The problem is the spaces in the path - change the last line to:
    <code>myObj.Run """C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE"""</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel (XP)

    Thank you, that works. Hans is quite right that I want to open a specific file as well. I assumed that if I could run Excel I could then add "CSome foldersome.xls" (after a space) to open a specific file at the same time. But this is causing difficulties as well. How could you extend you example code to then open a workbook? Thanks, Andy.
    (P.S. Does GetObject work in windows scripting?)

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Running Excel (XP)

    You would use:
    <code>myObj.Run """C:Program FilesMicrosoft OfficeOffice11EXCEL.EXE"" ""C:testFolder Aworkbook.xls"""</code>
    <code>GetObject</code> (and <code>CreateObject</code>) will work in VBS though you may have problems with them if you try to run the script via task scheduler.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel (XP)

    Try
    <code>
    myObj.Run """C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE"" ""CSome foldersome.xls"""
    </code>
    And yes, GetObject does work in a .vbs file, I tested the code before I posted it <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel (XP)

    Brill. That works. Thanks, Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •