Here is a sample VBA macro that creates an Excel PivotTable using a SAS cube, server data set, or local data set. It requires the SAS OLEDB data providers. And of course, you'd need to update the connection strings for your environment.
Sub CreatePivotTable()
Dim pc As PivotCache
Dim pt As PivotTable
Dim connectionString As String
Dim commandType As XlCmdType
Dim commandText As String
Dim version As XlPivotTableVersionList
'Change these values to customize your PivotTable
Const dataType As Integer = 1 'Use OLAP cube (0), server data set (1), or local data set (2)
version = xlPivotTableVersion10 'Version of the PivotTable to create
'Add a PivotCache (for Excel2007 PivotTables, use the new Create method instead of Add
If version = xlPivotTableVersion12 Then
Set pc = ActiveWorkbook.PivotCaches.Create(xlExternal)
Else
Set pc = ActiveWorkbook.PivotCaches.Add(xlExternal)
End If
If dataType = 0 Then 'OLAP
connectionString = "OLEDB;Provider=SAS OLAP Data Provider 9.2;User ID=olaptst1;Password=*****;Data Source=bciba09;Location=bciba09;SAS Machine DNS Name=bciba09;SAS Port=5451;SAS Protocol=2;SAS Server Type=2;"
commandType = xlCmdCube
commandText = "PRDMDDB" 'The name of the cube to open
ElseIf dataType = 1 Then 'server relational
connectionString = "OLEDB;Provider=SAS.IOMProvider; User ID=sasdemo; Password=****; SAS Machine DNS Name=d14217.na.sas.com; SAS Protocol=2; SAS Port=8591;"
commandType = xlCmdTable
commandText = "SASHELP.CLASS" 'The name of the IOM dataset to open
Else
connectionString = "OLEDB;Provider=sas.LocalProvider.9.2;Data Source=c:\program files\sas\enterpriseguide\4.3\sample\data;Mode=Read|Share Deny None"
commandType = xlCmdTable
commandText = "class" 'The name of the local dataset to open
End If
pc.Connection = connectionString 'OLEDB connection string to the server
pc.commandType = commandType
pc.commandText = commandText
Set pt = pc.CreatePivotTable(TableDestination:=ActiveCell, DefaultVersion:=version)
End Sub