0

I've got a VBScript that I can run manually without an issue. It opens and Excel doc, runs the macro, saves as a different file type and closes out of excel.

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("Excel.Application")
Set ObjWB = ObjExcel.Workbooks.Open("C:\Scripts\ExcelTest.xlsm")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE
ObjWB.Application.Run("RefreshData")
objWB.SaveAs "C:\Scripts\ExcelTest.xlsx", 51
objExcel.Application.Quit
Set objExcel = Nothing

However when putting this in Task Scheduler it opens Excel (in background) but never finishes. The user running has Admin rights and I've tested the VBS with that user as well. I can see that Excel is opening as it remains open in task manager, and task times out after 3 minutes per the task scheduler limit.

In short - its opening the file, but I can't figure out why it wont run the macro and save. Thanks

braX
  • 11,506
  • 5
  • 20
  • 33
Bro Dimaggio
  • 1
  • 1
  • Remove `.Visible = False` and see what happens – Andy G Jan 18 '18 at 14:08
  • you might use some message boxes (or other logging) to check how far your code runs and where it gets stuck. – Pᴇʜ Jan 18 '18 at 14:14
  • ... and remove `.DisplayAlerts = False`. You need to see what's going on during development and testing. – Andy G Jan 18 '18 at 14:15
  • probably macros are not enabled/trusted for the admin (which is generally a good idea). Check the Options › Trust Center in Excel for that admin user if he is allowed to run macros at all and if the specific file is trusted already. – Pᴇʜ Jan 18 '18 at 14:21
  • You might want to try my answer here: https://stackoverflow.com/a/34912755/4338329 – lovechillcool Jan 18 '18 at 14:29
  • 1
    So in testing I took out the 'ObjWB.Application.Run("RefreshData")' portion and it runs/saves without an issue. I'd note that I am running this with the "Run whether user is logged in" option on in Task Scheduler. I followed instructions but the running of the macro is the issue. I've enabled everything in Trust Center without any luck. Other suggestions? – Bro Dimaggio Jan 18 '18 at 15:44
  • Just to clarify by "*I've enabled everything in Tust Center*" (which is no very specific description of what you really did) you mean you did already [add the folder/file to the (admin) user's "trusted locations"](https://stackoverflow.com/a/48323914/3219613)? Which means you logged in as that user and added that folder/file to the trusted locations in the user's Trust Center? If yes can you run that macro manually when logged in with that admin user account? – Pᴇʜ Jan 18 '18 at 16:10

0 Answers0