SUPPORT / SAMPLES & SAS NOTES
Problem Note 49229: Hidden SAS® content in PivotTables does not refresh in the SAS® Add-In 5.1 for Microsoft Office
 |  |  |  |  |
In Microsoft Excel, the SAS Add-In for Microsoft Office does not refresh SAS content in PivotTables that are on hidden worksheets. The following error occurs:
"SAS-server-name:SAS-Library-Name.SAS-Data-Set-Name" encountered errors while opening the results. We can postpone processing the results by placing them in the Status window. Select method of Range class failed
As a workaround, you can use the following code in the Microsoft Visual Basic Editor in order to unhide, refresh, and then hide the worksheets.
'Unhides all sheets in the workbook
Sub SheetsUnhide()
Dim sheet As Worksheet
For Each sheet In Worksheets
sheet.Visible = True
Next
End Sub
'Refreshes all SAS Content
Sub RefreshContents()
Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
sas.Refresh ThisWorkbook
End Sub
'Pause 5 seconds for Refresh - change time interval as necessary
Sub Wait()
Application.Wait Time + TimeSerial(0, 0, 5)
End Sub
'Hides an individual sheet named below
Sub SheetsHide()
Dim sheet As Worksheet
For Each sheet In ThisWorkbook.Worksheets
If sheet.Name = "Specific-Sheet-Name-Here" Then sheet.Visible = False
Next sheet
End Sub
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Add-in for Microsoft Office | Microsoft® Windows® for x64 | 5.1 | 6.1 | 9.2 TS2M0 | |
| Microsoft Windows Server 2003 Datacenter Edition | 5.1 | 6.1 | 9.2 TS2M0 | |||
| Microsoft Windows Server 2003 Enterprise Edition | 5.1 | 6.1 | 9.2 TS2M0 | |||
| Microsoft Windows Server 2003 Standard Edition | 5.1 | 6.1 | 9.2 TS2M0 | |||
| Microsoft Windows Server 2003 for x64 | 5.1 | 6.1 | 9.2 TS2M0 | |||
| Microsoft Windows Server 2008 | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Microsoft Windows Server 2008 for x64 | 5.1 | 6.1 | 9.2 TS2M0 | |||
| Microsoft Windows XP Professional | 5.1 | 6.1 | 9.2 TS2M0 | |||
| Windows 7 Enterprise 32 bit | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Enterprise x64 | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Home Premium 32 bit | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Home Premium x64 | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Professional 32 bit | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Professional x64 | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Ultimate 32 bit | 5.1 | 6.1 | 9.2 TS2M3 | |||
| Windows 7 Ultimate x64 | 5.1 | 6.1 | 9.2 TS2M3 | |||