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
Author by
ALEXM
Updated on March 30, 2020Comments
-
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
- I have a macro that I need to be run every 5 min. I have a
-
ALEXM over 8 yearsI need it to save the file with the changes
-
ALEXM over 8 yearswhat did you changed?
-
David Zemens over 6 yearsSeems like this shouldn't work, as you're specifying the file will be opened in
ReadOnly
mode. -
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