SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

SAS 9.2 Unix -> Excel 2002 -> VBA -> Recodset -> Pivot Table

Reply
Occasional Contributor
Posts: 9

SAS 9.2 Unix -> Excel 2002 -> VBA -> Recodset -> Pivot Table

Hello, have a problem with pivot table creation. I have a compiled macro running on a SAS server. I start the macro using the IOM Bridge for COM . It sorts a summury table that I put to a Recordset. I can filter the Recordset and print data to a Sheet (so there is no problem with previous steps).  When i'm trying to create a pivot table on the Recordset getting an Error saying that there is a problem with Microsoft Connectors what could it be (no problem with pivot table creation from a Sheet data )?

 

There is a part of a code where the Error ocures:

 

Dim obConnection As ADODB.Connection

Set obConnection = New ADODB.Connection
obConnection.Open "Provider=sas.iomprovider.9; SAS Workspace ID=" & MYidentificator

 

Dim obRecordset As New ADODB.Recordset

obRecordset.Open "WORK.TABLE", obConnection, adOpenDynamic, adLockReadOnly, adCmdTableDirect

 

...


Dim PvtTblCache As PivotCache

 

Set PvtTblCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

Set PvtTblCache.Recordset = obRecordset

 

Error here -->  PvtTblCache.CreatePivotTable TableDestination:=Range("A10"), TableName:="Tab", DefaultVersion:=xlPivotTableVersion10

Can give Error direct text if needed. Thanks.

 

 

 

 

Post a Question
Discussion Stats
  • 0 replies
  • 434 views
  • 0 likes
  • 1 in conversation