Unknown runtime error when running macro

Question

I needed a way to schedule automatically opening an Excel file, refresh the contents then save and close it.

I have done this before but I can no longer open the file as opening it causes the macro that refreshes then saves and closes the file to run.

I considered VBScript after trying several ways of doing it. I found this code on here.

Dim objExcel, objWorkbook 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\...\Finances.xlsm")
objExcel.Visible = True
objExcel.Run "Refresh"
objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing

WScript.Quit

The VBScript code opens the Excel file and triggers the macro Refresh inside. The macro refreshes the data then saves and closes the file.

But I'm getting the following error despite the code seeming to run ok.

Does the Excel Macro "refresh" maybe have a line of code which closes the Excel Workbook? If so, then your VBS gets the runtime error because it was waiting for the `objExcel.Run "Refresh"` to be completed which in turn closed the Excel WorkBook — Jean-Pierre Oosthuizen, Jan 14 '16 at 18:42

score 0 · Answer 1 · answered Jan 14 '16 at 18:25

If you don't want anything to run automatically when you open the workbook, put the line

objExcel.EnableEvents = False

before you open the workbook. You could set it back to True later if you need to. You should still be able to run Refresh. Whether or not that will fix your unknown error, I don't know.

score 0 · Answer 2 · answered Jan 16 '16 at 20:22

0

You are quitting the excel.application object without closing the objWorkbook open workbook. Saved or not, this is going to generate an error crashing out of the open workbook.

answered Jan 16 '16 at 20:22

Unknown runtime error when running macro

2 Answers2