#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    140
    Rep Power
    13

    Launch Excel VBA Macro From Script File (such as a batch file)


    Is it possible to launch an Excel spreadsheet VBA macro from a script file (such as a batch file or a Perl script)? I have a spreadsheet application that I would like to have run and update itself in the middle of the night. Thanks
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    I don't think you can directly refer to anything inside Excel (like a macro) from outside code unless you create an instance of excel via automation and then manipulate the opened excel object with your code.

    And I don't know if wsh scripting supports office automation. You could do what you want with VB6, for example, by instantiating Excel.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    I agree. Doing it in something like vb6 and making an executable would be the way to go.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Aug 2004
    Posts
    1,795
    Rep Power
    85
    you can just schedule an .xls to open, or a .bat to open an .xls. Then inside the .xls you can make a macro that executes as soon as it loads. So when the file opens it just starts running. First thing it should do is see if it already has data, if not then it goes fill itself with data and save itself to some timestamp based filename and close itself. You can then open it later and the data does not get overwritten.
  8. #5
  9. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Then inside the .xls you can make a macro that executes as soon as it loads. So when the file opens it just starts running.
    You must set macro security to Low, not advised, for Auto_Run to Auto_Run without prompting the user (which there is no user). Building an executable is far better for scheduled tasks.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Aug 2004
    Posts
    1,795
    Rep Power
    85
    I agree, but this is not always possible. Not everyone has VB.
    Anyway, i found this .vbs someplace, it should work; no clue what office's macro security will do to it.

    Code:
    Set xlObj = CreateObject("Excel.application")
    xlObj.Workbooks.Open "C:\somefile.xls"
    xlObj.Run "MyFavoriteMacro"
    xlObj.ActiveWorkbook.Saved = True
    xlObj.ActiveWindow.Close
    xlObj.Quit
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    140
    Rep Power
    13
    Do I need anything special to run this vbs script?


    Originally Posted by replax
    I agree, but this is not always possible. Not everyone has VB.
    Anyway, i found this .vbs someplace, it should work; no clue what office's macro security will do to it.

    Code:
    Set xlObj = CreateObject("Excel.application")
    xlObj.Workbooks.Open "C:\somefile.xls"
    xlObj.Run "MyFavoriteMacro"
    xlObj.ActiveWorkbook.Saved = True
    xlObj.ActiveWindow.Close
    xlObj.Quit

IMN logo majestic logo threadwatch logo seochat tools logo