Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"acessor flags" creating pivot table from recordset
I'm using ADO to create a recordset which I'm trying to use to create a
pivot table. The code is below. I get error "One or more accessor flags were invalid" on the CreatePivotTable line. I know the recordset has data. TIA, Wolfie Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String Dim objPivotCache As PivotCache Dim objPivotTable As PivotTable ' Create connection string szConnect = "Provider=SQLOLEDB; Data Source=MyServer;;User ID=myuser; " & _ "password=mypassword" ' Create the SQL statement szSQL = Worksheets("SQL_ActPlan").Range("A100").Value ' Create the Recordset object and run the query. Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Make sure we got records back If Not rsData.EOF Then ' Use the record set for the pivot table Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = rsData2 With objPivotCache .CreatePivotTable TableDestination:="R7C1", _ TableName:="PivotTable1", ReadData:=True End With rsData2.Close End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"acessor flags" creating pivot table from recordset
The issue here was in the following line
If Not rsData.EOF Then Apparently this code does something to the recordset so that the pivotcache is unable to read it. Freaky right!? I commented it out and it now works. Apparently testing BOF and RecordCount causes the same problem. The credit for this find goes to the Wrox Excel VBA books (which are excellent) and their brilliant authors. Wolfie "Wolfie" wrote in message ... I'm using ADO to create a recordset which I'm trying to use to create a pivot table. The code is below. I get error "One or more accessor flags were invalid" on the CreatePivotTable line. I know the recordset has data. TIA, Wolfie Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String Dim objPivotCache As PivotCache Dim objPivotTable As PivotTable ' Create connection string szConnect = "Provider=SQLOLEDB; Data Source=MyServer;;User ID=myuser; " & _ "password=mypassword" ' Create the SQL statement szSQL = Worksheets("SQL_ActPlan").Range("A100").Value ' Create the Recordset object and run the query. Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Make sure we got records back If Not rsData.EOF Then ' Use the record set for the pivot table Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = rsData2 With objPivotCache .CreatePivotTable TableDestination:="R7C1", _ TableName:="PivotTable1", ReadData:=True End With rsData2.Close End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Pivot Table Report Filter - "OR" instead of "AND" Multiple Filters | Excel Discussion (Misc queries) | |||
Pivot table "Group and Show Details" vs. "SubTotals" | Excel Programming | |||
Copy recordset from an Access "make table" query | Excel Programming | |||
Creating small "recordset" or sorting a numeric 1-dim array | Excel Programming |