3
  • 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
shA.t
  • 16,580
  • 5
  • 54
  • 111
ALEXM
  • 87
  • 1
  • 2
  • 11

1 Answers1

6

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
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • I need it to save the file with the changes – ALEXM Mar 29 '15 at 05:45
  • what did you changed? – ALEXM Mar 29 '15 at 06:00
  • 3
    Seems like this shouldn't work, as you're specifying the file will be opened in `ReadOnly` mode. – David Zemens Apr 20 '17 at 17:54
  • 2
    @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 – David Cram Jun 28 '19 at 16:31