45606 - Using SAS® Add-In for Microsoft Office scripting to delete all SAS content in Microsoft Excel

Sample 45606: Using SAS® Add-In for Microsoft Office scripting to delete all SAS content in Microsoft Excel

Overview

Typically, you use the Manage Content tool on the SAS ribbon to remove SAS content from a workbook. Unfortunately, this method only enables you to delete objects one at time. The VB script in this sample shows you how to delete different types of SAS content from a workbook all at once.

This sample was written using the SAS Add-In for Microsoft Office 4.3 with Microsoft Excel 2010.

Add and run the sample VB script

Open a PivotTable that contains SAS content in Microsoft Excel, and then select the Developer tab from the ribbon.

Note: If the Developer tab is not displayed, complete the first step in SAS Note 42983.

In the Code group, select Visual Basic.
In the left pane, select Sheet1, and then copy and paste the following code into the right pane.

Sub DeleteSasContent()
Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("sas.exceladdin").Object

Dim stpList As SASStoredProcesses
Dim dataList As SASDataViews
Dim pivotList As SASPivotTables
Dim reportList As SASReports

Set stpList = sas.GetStoredProcesses(ThisWorkbook)
Set dataList = sas.GetDataViews(ThisWorkbook)
Set pivotList = sas.GetPivotTables(ThisWorkbook)
Set reportList = sas.GetReports(ThisWorkbook)

For i = 1 To stpList.Count
stpList.Item(i).Delete
Next i

For i = 1 To dataList.Count
dataList.Item(i).Delete
Next i

For i = 1 To pivotList.Count
pivotList.Item(i).Delete
Next i

For i = 1 To reportList.Count
reportList.Item(i).Delete
Next i

End Sub

To execute the code, select Run from the menu, or press F5.

Additional Documentation

For more information about the SAS Add-In for Microsoft Office, see the SAS Add-In for Microsoft Office documentation page.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Date Modified:	2012-02-14 10:20:13
Date Created:	2012-02-08 13:50:18

Product Family	Product	Host	Product Release		SAS Release
Product Family	Product	Host	Starting	Ending	Starting	Ending
SAS System	SAS Add-in for Microsoft Office	Microsoft® Windows® for x64	4.3		9.2 TS2M3
		Microsoft Windows Server 2003 Datacenter Edition	4.3		9.2 TS2M3
		Microsoft Windows Server 2003 Enterprise Edition	4.3		9.2 TS2M3
		Microsoft Windows Server 2003 Standard Edition	4.3		9.2 TS2M3
		Microsoft Windows Server 2003 for x64	4.3		9.2 TS2M3
		Microsoft Windows Server 2008	4.3		9.2 TS2M3
		Microsoft Windows Server 2008 for x64	4.3		9.2 TS2M3
		Microsoft Windows XP Professional	4.3		9.2 TS2M3
		Windows 7 Enterprise 32 bit	4.3		9.2 TS2M3
		Windows 7 Enterprise x64	4.3		9.2 TS2M3
		Windows 7 Home Premium 32 bit	4.3		9.2 TS2M3
		Windows 7 Home Premium x64	4.3		9.2 TS2M3
		Windows 7 Professional 32 bit	4.3		9.2 TS2M3
		Windows 7 Professional x64	4.3		9.2 TS2M3
		Windows 7 Ultimate 32 bit	4.3		9.2 TS2M3
		Windows 7 Ultimate x64	4.3		9.2 TS2M3
		Windows Vista	4.3		9.2 TS2M3
		Windows Vista for x64	4.3		9.2 TS2M3

Support

Sample 45606: Using SAS® Add-In for Microsoft Office scripting to delete all SAS content in Microsoft Excel

Overview

Add and run the sample VB script

Additional Documentation

Operating System and Release Information