0

I know there are other posts similar to this but I cannot seem to get this to work. I just want to create a .vbs file to use with the scheduler to kick off a macro in Excel on monthly basis. Here is what I have been trying:

Option Explicit


Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open("C:\Test\TEST.xlsm", 0, True)
xlApp.Run "Macro1"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing


WScript.Quit

I just get a warning message every time saying that TEST.xlsm already exists and do I want to overwrite it. When I click Yes, the changes never take effect.

I don't want to get any message at all, and I don't know why the changes from the macro are not occurring.

I've tried to use 

xlApp.SaveAs

and I cannot get that to work either. Plus the workbook appears to not be closing correctly.

Thanks in advance, I am new to this so maybe I am just missing something stupid.

Matt Puck
  • 3
  • 2
  • 3
  • `Application.Run` requires the workbook name in addition to the macro name. – Tim Williams Jul 23 '13 at 19:41
  • This still isn't working. It tells me there is already a file there, do I want to overwrite it? I click Yes and there are still no changes being made to the file?? – Matt Puck Jul 24 '13 at 16:36
  • File where? Hard to tell what's going on without knowing what your macro does. – Tim Williams Jul 24 '13 at 17:34
  • I am just trying to update an existing workbook. I am trying to learn how to do this so I just created a dummy .xlsm file where the macro literally only copies one sheet, goes to the next sheet and pastes. Ultimately the workbook that I want to use this for refreshes a bunch of data being pulled from an accdb. When I run the script I get a message saying the file already exists, do I want to overwrite it. I click yes, but no changes are made to the original file...it runs, I don't get any errors, but the data in the file does not change, it is exactly the same. Thanks for your help. – Matt Puck Jul 24 '13 at 19:44
  • Try adding `xlBook.Save` before closing the workbook. – Tim Williams Jul 24 '13 at 19:53
  • I tried that and it didn't change anything. I found this thread, which I know you commented on as well. http://stackoverflow.com/questions/10232150/run-excel-macro-from-outside-excel-using-vbscript-from-command-line Is one way better than the other? They seem totally different. – Matt Puck Jul 24 '13 at 20:25
  • I missed this before, but you're passing `True` as the "readonly" argument in the `Workbooks.Open` call. If you want to save changes to TEST.xlsm then you need to pass `False` instead. – Tim Williams Jul 24 '13 at 21:50
  • Ahhh....That worked great. You are really good at this! So, if you don't mind, what is the difference with say, the above and the other way in the thread I referenced in my other comment? Is one way better than the other? It starts with: Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("V:/TEST.xlsm") – Matt Puck Jul 24 '13 at 22:10
  • Seems like pretty much the same thing to me: just depends on what parameters you pass to `Open()`. Passing just the path by itself would work fine in your case, since by default `ReadOnly` is False, and you want to save changes to the file. – Tim Williams Jul 24 '13 at 23:04

0 Answers0