BookmarkSubscribeRSS Feed
sundx045
Calcite | Level 5

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!

3 REPLIES 3
fifthand57th
SAS Employee

@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
 

2019-08-05_13-54-49.jpg

sundx045
Calcite | Level 5

@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
SAS Employee

@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". 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Discussion stats
  • 3 replies
  • 2198 views
  • 0 likes
  • 2 in conversation