Usage Note 38691: Capturing a PivotTable connection string from Microsoft Excel
The connection string for a PivotTable is the information that is needed to
make a connection to SAS®. This information is often useful when debugging a
connection problem. The method for capturing the string depends on which version of the SAS® Add-In for Microsoft Office you are running.
In version 4.2 of the SAS Add-In, the connection string
can be viewed by simply doing the following:
- Select a cell in the PivotTable.
- Using Microsoft Office 2007, select the Data tab on the menu.
- Select the Properties icon.
- Select the Definition tab.
In version 2.1 of the SAS Add-In, the connection string is not this easily
available. One quick way to get the connection string is to run a Visual Basic
macro. Below are the steps to capture the connection string:
- Click the PivotTable that you want to see the connection string for (the active
cell must be in the PivotTable.)
- Start the Visual Basic Editor by pressing Alt+F11 in either Microsoft Excel 2003 or 2007. You also have the following options:
- In Excel 2003, select Tools ► Macro ► Visual Basic Editor.
- In Excel 2007, select the Developer ribbon, and then select Visual Basic.
- In the Visual Basic Editor, double-click ThisWorkbook under VBAProject(<name
of your workbook>) ► Microsoft Excel Objects.
- Paste the following macro:
Sub ShowConnectionString()
MsgBox ActiveCell.PivotTable.PivotCache.Connection
End Sub
|
- Click on the MsgBox line so that focus is in the macro, then press F5 to run
it. A message box should pop-up with the connection string.
Operating System and Release Information
| SAS System | SAS Add-in for Microsoft Office | Microsoft Windows 2000 Advanced Server | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Datacenter Server | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Server | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Professional | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows NT Workstation | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Datacenter Edition | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Enterprise Edition | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Standard Edition | 2.1 | | 9.1 TS1M3 SP4 | |
| Microsoft Windows XP Professional | 2.1 | | 9.1 TS1M3 SP4 | |
| Windows Vista | 2.1 | | 9.1 TS1M3 SP4 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Date Modified: | 2012-01-25 12:38:14 |
| Date Created: | 2010-02-10 14:12:18 |
