Use vbs to open an excel workbook, run a macro and save the workbook

35,177

Updated code below, I have also tweaked the logic of the clean-up

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open("W:\Excel Macro\EIM File Maneger\EIM_file_check.xlsm", 0, True)


xlApp.Run "GetFiles"

xlbook.Save
xlBook.Close False
set xlBook = Nothing

xlApp.Quit
Set xlApp = Nothing

WScript.Echo "Finished."
WScript.Quit
Share:
35,177
ALEXM
Author by

ALEXM

Updated on March 30, 2020

Comments

  • ALEXM
    ALEXM over 3 years
    • I have a macro that I need to be run every 5 min. I have a vbs file that schedules the macro.
    • The macro is checking for new files in some folder, writes their info into a table, and moving the files into archive.
    • The table is in the same excel file as the macro!

    It is running the macro fine but in the end, its asking me if I wont to save the file.

    I need it to save the changes that the macro did to the file automatically!

    this is my current vbs code:

    Option Explicit
    
    Dim xlApp, xlBook
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False
    Set xlBook = xlApp.Workbooks.Open("W:\Excel Macro\EIM File Maneger\EIM_file_check.xlsm", 0, True)
    
    xlApp.Run "GetFiles"
    
    xlBook.Close true
    xlApp.Quit
    
    Set xlBook = Nothing
    Set xlApp = Nothing
    
    WScript.Echo "Finished."
    WScript.Quit
    
  • ALEXM
    ALEXM over 8 years
    I need it to save the file with the changes
  • ALEXM
    ALEXM over 8 years
    what did you changed?
  • David Zemens
    David Zemens over 6 years
    Seems like this shouldn't work, as you're specifying the file will be opened in ReadOnly mode.
  • David Cram
    David Cram over 4 years
    @DavidZemens i just used this code for a similar task and I wish I had read your comment first. Since DisplayAlerts is false and the file is read only, it silently fails to save. Updating the read only parameter to false did fix the problem