[HOW TO] Integrate the SAS function "refresh multiple data" in a macro VBA

[deleted_user]

Hi everybody !

I want to integrate the function "refresh multiple data" from the SAS Add-In for Microsoft (see images attached here under).

If I want to refresh a simple pivotTable in Excel, the VBA code is the following :

Sheets("Pivot Table").Select
Range("C13").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Day").Select


I try to register a macro when using the function "refresh multiple data" but it seems that MS do not recognise this SAS function when registering the VBA macro.

Is there any documentation or code explaining how to integrate this SAS function in a VBA macro in Excel ?

It is quite boring to do it manually when there is surely a way to automate it 😉

Thank you in advance !

Image 1 :

Image 2 :

[CaseySmith]

There is some documentation in SAS->Help->SAS Add-In for Microsoft Office Help. See the "Working with Visual Basic Code" section under Contents.

Here is an example macro:

'For AMO 2.1 or 4.2
'Add reference to "SAS_OfficeAddin"
Sub RefreshWorkbook()

Dim ComAddin As ComAddin
Dim SasAddinObj As SASAddin

Set ComAddin = Application.COMAddIns("SAS.OfficeAddin.Loader.ConnectProxy")
Set SasAddinObj = ComAddin.Object
Set ComAddin = Nothing

' refreshes all the AMO content (tasks, reports, data views, etc. in the workbook)
SasAddinObj.Refresh (Workbook)

End Sub

Additional features (opening data views, pivot tables, reports, running stored processes, etc.) have been added to the scripting inteface in AMO 4.3, due out later this year.

[deleted_user]

Thanks !!!

[deleted_user]

Hello,

I 'm back again 😕

I tried the macro you posted just here upper but I got an error message (see image 1)

Image 1 : http://free0.hiboox.com/images/1410/diapo357ed44476a7cb38744597a08063d423.jpg?39

So, I followed your advice and went to the Help in the SAS AddIn Section in XL.

Then I tried to follow the instructions indicated in the Help section. I followed the instructions (instructions detailled under) and copied the code mentionned here under and got the message

"compile error - only valid in object module"


Managing SAS Content
Writing event procedures in Excel
To write an event procedure in Excel, follow these steps:
Select Tools Macro Visual Basic Editor. The Microsoft Visual Basic Editor opens.
In the Visual Basic Editor, select Tools References. The References dialog box opens.
From the list, select SAS.OfficeAddin and click OK. In the Project pane, double-click the class module for the existing workbook.
In the code module for the workbook, insert the following code:


----
Private WithEvents sas As SASAddIn

Private Sub Workbook_Open()
Set sas = Application.COMAddIns.Item("SAS.OfficeAddin.Loader.ConnectProxy").object
End Sub

Private Sub sas_ItemUpdated(ByVal refreshableObject As Variant)
MsgBox "SAS item has been updated: " + refreshableObject
End Sub
----

The object name of the job is returned by the refreshableObject parameter of the ItemUpdated event. You can use this information to identify what job was refreshed. In Excel, the object name is the name of the range that contains the entire results from the job. To view the object name for a job, see the Object name field on the General tab of the Properties dialog box.
Select File Close and Return to Microsoft Excel.

To execute this code after you have inserted it, save the file and close Excel. The next time that you open the workbook this code is automatically executed.

Image 2 : http://free0.hiboox.com/images/1410/diapo64860ac0417425820887174fe67803a0.jpg?26

Image 3 : http://free0.hiboox.com/images/1410/diapoba29ac334acebac47761bca030eec246.jpg?34


:'(

[CaseySmith]

To use the macro I posted, you have to add a reference to "SAS_OfficeAddin" in the VBA editor (Tools->References, check "SAS_OfficeAddin", OK, then run the macro).

[deleted_user]

Hi again,

Thank you Casey, but ...

The problem is that I don't have this option in the VBA editor as you can see in the images here under.

How do I have to have this option available in the VBA editor ?

Image 2 : http://free0.hiboox.com/images/1410/diapo64860ac0417425820887174fe67803a0.jpg?26

Image 3 : http://free0.hiboox.com/images/1410/diapoba29ac334acebac47761bca030eec246.jpg?34

[CaseySmith]

It is odd if "SAS_OfficeAddin" isn't in the list. You may want to look at the top of the list as well since I noticed not all the items are alphabetized.

If you can't find it, you can run that macro without adding a reference to "SAS_OfficeAddin" by removing or commenting out the "As SASAddin" by placing a single quote in front of it like so:
Dim SasAddinObj 'As SASAddin

This may require you turning off options explicit.

[deleted_user]

Thanks Casey !

I 'm out of the office for a week but as soon as I get back to work, I will try this.

I let you now about this asap.

Thanks again Message was edited by: Fab.from.be

[deleted_user]

Hi Casey,

I try with the following as I don't find the Add-Ins in the list

---Option Explicit

Sub Macro1()
'
'For AMO 2.1 or 4.2
'Add reference to "SAS_OfficeAddin"

Dim ComAddin As ComAddin
Dim SasAddinObj 'As SASAddin

Set ComAddin = Application.COMAddIns("SAS.OfficeAddin.Loader.ConnectProxy")
Set SasAddinObj = ComAddin.Object
Set ComAddin = Nothing

' refreshes all the AMO content (tasks, reports, data views, etc. in the workbook)
SasAddinObj.Refresh ("DETAIL_BE")

End Sub

---

It seems it's working but ... I don't get the same table I 'm used to see when I launch "refresh data multiple".

In fact I don't see anything working.

How can I be sure that the refresh has been done ?


Normally I get a table with things to checkboxes

* Select ALL
* Refresh items in order (last run time for select items : 3 seconds)
* modify items before refreshing (this one is not chosen)


Your feedback will be greatly appreciate 😉

Thank you !

Message was edited by: Fab.from.be Message was edited by: Fab.from.be

[CaseySmith]

That VBA macro is not interactive -- it doesn't show any UI. It simply refreshes one or more pieces of content (data views, PivotTables, reports, stored processes). To verify that it actually refreshed, you can either change values in the source data or delete some of the values in the results in the worksheet, then run the macro and ensure the latest values are retrieved (Excel doesn't allow you to delete values in a PivotTable, so you'd have to change the source data to verify it).