CODEWITHSUNDEEP
excelvbasasscheduleradd-in
Feng
Feng

Reputation: 11

SAS Stored process in excel is not refreshing when using a task scheduler?

I'm trying to use the windows task scheduler to auto open an excel workbook, and upon opening, it should refresh a SAS stored process.

The VBA in excel to refresh the stored process is:

Sub RefreshSAS()

Dim sas As Object

Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

sas.Refresh ThisWorkbook

End Sub

And I put this code inside ThisWorkbook in VBA:

Sub Workbook_Open()

RefreshSAS

End Sub

I believe I have all necessary add-ins loaded. When I manually open the file, it runs absolutely fine, and you can see the "SAS" tab in excel on the top menu bar (next to Home, Insert, Page Layout, Developer, etc). But when I use Windows Task Scheduler to open it, it fails to run with this VBA error: enter image description here

When this error shows up, the "SAS" tab is not there on the menu bar. If I click End or Debug on the VBA error, after a few seconds, the "SAS" tab appears. Therefore it seems to me that the SAS Add-in is not loaded when Task Scheduler opens the excel workbook and tries to refresh, and it starts to load after I click End or Debug. Once again, when I open the file manually, it works fine as expected. This issue only happens when I use Task Scheduler. Anyone has any ideas or suggestions? I greatly appreciate any help. Thank you!!!

Upvotes: 1

Views: 322

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12701

Alternatively - avoid plugins / addins entirely by calling your STP as a Web Query using Excel. With the added advantage that it will run on any version of Excel. This should work fine in a Task Scheduler context. Your STP will need to be set for streaming output.

enter image description here

Your VBA will look something like this:

enter image description here

You can find further information in my blog post here

Upvotes: 1

Related Questions