SAS addin 7.1 Excel VBA Slow Refresh on multiple SAS tables

[sundx045]

Hi all,

I'm currently writing a Macro in VBA to refresh all SAS tables in an activeworkbook. I've tried two different methods, seen below, and while they work to refresh the tables, the run time is very slow in workbooks with multiple SAS tables (10 mins in some workbooks that would take 1 min if done manually). I believe this is occurring between table refreshes as it takes about 10-20 seconds to address the new table and update it.

1.

Sub test()

Dim sas As SASExcelAddIn

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

For Each sheet In ActiveWorkbook.Sheets
If sheet.Name Like "*SAS*Table*" Then
sheet.Activate
sas.Refresh ActiveWorkbook.ActiveSheet
End If

Next sheet

end sub

2.

Sub test()

Dim sas As SASExcelAddIn

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

sas.Refresh 

end sub

Does anyone know if there is a way to speed up the time between refreshes, when a workbook has multiple tables embedded? It's to have it automated, but right now isn't worth it with how long some of the automatic refreshes are taking compared to a manually.

Thanks in advance for the help!

[fifthand57th]

@sundx045 

Is your goal to have the SAS content refresh on workbook open? If so, enable the "Refresh on file open" option on the Execution tab of the properties of the SAS content. See screen shot below. 

If refresh on file open is not your goal, try using only the sample VBA code in the SAS Add-In help without other VBA present:

Sub RefreshContents()

    Dim sas As SASExcelAddIn
    Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
    
    sas.Refresh ThisWorkbook
 
End Sub
 

[sundx045]

@fifthand57th 

Thanks for getting back.

My goal is to create a tool to loop through multiple files and refresh the tables, as I have to refresh around 150 files. I can get it to loop through and refresh tables, but a refresh on a table that would take a couple seconds manually takes a few minutes when I try to automate it. This makes the automated refresh unjustified as it ends up taking longer.

So I'm curious if there is a way to speed up that refresh or if that's just the nature of the automation. Appreciate the help!

[fifthand57th]

@sundx045 

Each time you open an Excel workbook, the SAS Add-in for MS Office must load in Excel. Before you Refresh SAS Content, the SAS Add-in for MS Office must establish a connection to the metadata server then instantiate a workspace server where execution will occur. If I understand what you are doing correctly, you are looping through closed Excel files and refreshing SAS Content. 

When you refresh SAS content from VBA in this manner, the VBA must first open the Excel file, then the SAS Add-in must load in Excel, then the metadata connection occurs and workspace server is instantiated, and, finally, the refresh occurs. You likely count all of this as your "refresh time". 

Without using VBA, you first open the Excel file and the SAS Add-in loads, then it is likely that the metadata and workspace server connections are made before the user clicks the "Refresh" button. You are likely not counting the time it takes to open Excel, load the Add-in, make the connections, then refresh the data as your "refresh time".