CODEWITHSUNDEEP
excelvbscriptexcel.applicationvba
user3535222
user3535222

Reputation: 3

Unknown runtime error when running macro

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.

"Unknown Runtime Error" in line 5

Upvotes: 0

Views: 2313

Answers (2)

user4039065
user4039065

Reputation:

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

Upvotes: 0

John Coleman
John Coleman

Reputation: 52008

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.

Upvotes: 0

Related Questions