pivot table with external data


D

dstiefe

I am querying my database and saving it to a recordset...see below...know how
do i put that into a pivot? I only know how to write the code for an XL based
data source..

Thank you


Sub GetAccessData()
Dim sh As Worksheet

Set sh = Sheets("Sheet2")

Dim MyConnect As String

Dim MyRecordset As ADODB.Recordset

MyConnect = "Provider=Microsoft.ACE.OlEDB.12.0;" & _
"Data Source = C:\Dans_DB.accdb"

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open "DMA", MyConnect, adOpenStatic, adLockReadOnly
 
Ad

Advertisements

S

SteAXA

For create your pivot table you can do so:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "Provider=Microsoft.ACE.OlEDB.12.0;Data Source =
C:\Dans_DB.accdb"
.CommandType = xlCmdSql
.CommandText = your_query
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:=your_pivot_name, DefaultVersion:=xlPivotTableVersion10
End With

After for refresh your data:
ActiveSheet.PivotTables(sNamePivot).PivotCache.Refresh

Bye, Ste'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top