Automating SAS Add-In Thru VBA - Error

[shl007]

Hi,

I am using VBA to automate the SAS add-in for Excel by refreshing my pivot tables. When I invoke the “.RefreshAll” method in VBA to refresh all pivot tables, I get the error attached. Any ideas how to get around? When I manually click “Close” to the error below, the VB script is allowed to continue, but I am not sure how to click “Close” on the dialog box below through VBA. It seems anything I’ve tried, e.g., .SendKeys method, etc., does not work because putting it after the .RefreshAll is too late ☹. Hope you can share any tips.

I did look at the following paper and have googled to no avail. I don't think I need to reinstall/repair the add-in quite yet as the pivot tables ARE refreshing, just with this blip of an error (attached) that I need to click thru. Any advice appreciated. Thank you!

https://support.sas.com/resources/papers/proceedings11/012-2011.pdf

[ballardw]

What does the error box show when you click on the Details?

[shl007]

Object reference not set to an instance of an object.

-------------------------- Technical Information Follows --------------------------

Exception Details:
----------------------------------------
Exception type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: SAS.OfficeAddin
Target Site: FormatDataField

Stack Trace:
at SAS.OfficeAddin.PivotTable.FormatDataField()
at SAS.OfficeAddin.ExcelAddin.PerformCalculatedItemOperation(ContentInfo contentInfo, CalculatedItemOperations operation)
at SAS.OfficeAddin.ExcelAddin.OnSheetPivotTableUpdate(Worksheet worksheet, PivotTable pivotTable)
at SAS.ExcelAddIn.VstoExcelAddIn.OnSheetPivotTableUpdate(Object Sh, PivotTable Target)

[sustagens]

SAS Add-In for Microsoft Office (AMO)

RefreshAll only applies to SAS objects, not Excel objects. As such, when you invoke AMO's RefreshAll, it will only refresh SAS objects currently in your workbook.

If you didn't create the pivot table using AMO, odds are it is an Excel object and not a SAS object. 

All your SAS objects are listed in SAS > Manage Content. These items are the "all" referred to in "RefreshAll". In your case, if this list is empty, it means you have no SAS objects in your workbook. This is why your error says object reference is null. This also tells us that your pivot table is an Excel object. 

Your option is to add VBA code using Excel methods/properties to refresh the pivot table - not (AMO's RefreshAll). 

If you want to stick to using RefreshAll, you need to bring in the SAS dataset you want to use as a "Pivot Table" (default selection is Worksheet)

After this you would be able to modify the pivot table normally as you would an Excel-object-pivot table

Confirm that it is a SAS object by going to manage content again.

[shl007]

Thanks for the feedback. In answer to your question, out of the six or so tabs/sheets in my workbook, under SAS > Manage Content in Excel, I see just one of the tabs/sheets listed there. That is probably the original one I created in the workbook. After that, I copied the tab and created multiple tabs. So those multiple copied tabs are not being recognized under SAS > Manage Content. That could be the issue/culprit of my error? To help address this, I did the below:

I tinkered with the config file for the add in by adding a 'false' setting to that formatdatafield that was causing an error, and now I get the error below. Is there a way around this one? When I run the VB manually thru Task Scheduler, I don't get the error. It is only when it is scheduled and I have my PC locked, that the error seems to occur? I'm invoking "refresh" in the VBA below. Do I put a qualifer/argument on the sas.refresh instead of "ThisWorkbook" to be the particular tab that's under SAS > Manage Content? It's hard to test this since I cannot seem to replicate manually; again only seems to happen when the Windows Task Scheduler runs and I am actively on my desktop. The job still runs and updates; just this extra error further down occurs.

Dim sas As Object
Const SASExcelAddIn As Long = 0
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

sas.refresh ThisWorkbook '  <-- change this to say,  sas.refresh ThisWorkbook.Sheets("sheetname")? Just tried that and seemed to generate a weird different error 😞

------------------------------------------

Error below from sas add-in; not sure if the code above is causing it, or my .RefreshAll:

Value cannot be null.

Parameter name: context

-------------------------- Technical Information Follows --------------------------

Exception Details:

----------------------------------------

Exception type:  System.ArgumentNullException

Message:         Value cannot be null.

Parameter name: context

Source:          SAS.OfficeAddin

Target Site:     AutoRefresh

Stack Trace:

   at SAS.OfficeAddin.OfficeAddinBase.AutoRefresh(OfficeDocumentContext context)

   at SAS.OfficeAddin.ExcelAddin.Load()

   at SAS.OfficeAddin.Connect.CreateAddIn()

   at SAS.ExcelAddIn.VstoExcelAddIn.CreateAddIn()

[shl007]

Update: I'll change the sas.refresh line of code to say something like this per Tim Beese's paper:

sas.Refresh Sheet1.Range("A1")

And see how it works tomorrow when the scheduler runs at 6 am. Thanks and fingers crossed!!