Create Excel Pivot using Access Table

[redman2009]

I'm writing VBA in MS Access, to run a module which will create the pivot (in Excel) using a local (linked) Access table.

I can easily create the data in Excel and then pivot it, but I don't want to store all the data in another sheet because of high volumes. Ideally I just want the pivot to sit in the Excel workbook (actually there will be a number of these) with it linked to the table.

Manually, there's no problem. Creating it in VBA with the links has proved a problem. I have tried recording the steps in Excel, but transferring it to Access VBA has not had the desired effect.

The best attempt has been as per below:

Dim Excelconn As ADODB.Connection
Set Excelconn = New ADODB.Connection
With Excelconn .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outcome Reporting\Databases\18Wk RTT Reporting Data.mdb;Persist Security Info=False, Array([T003 Clock Running Referrals])"
End With

followed by:

With AppExcel.ActiveWorkbook.PivotCache
With .Create(SourceType:=xlExternal, SourceData:=Excelconn, Version:=3) Set PTable = .CreatePivotTable(TableDestination:=T003Sheet.Range("A" & T003HeadRow), TableName:="T003Pivot", DefaultVersion:=3) End With
End With

The above produces a run-time error '1004' (Application/object-defined error) on the "With .Create(SourceType:=xlExternal......" line.

Any ideas/suggestions would be greatfuly received.

 

[gemma-the-husky]

to be honest i would consider not doing this via a live connection, although it is possible

if you want to play with a set of data, save it into a csv via a query, then import into excel and play around with it there.

 

[redman08]

Thanks Dave. The table I'm reading and wanting to pivot has had all the work done on it, so this is purely to produce a report which will allow a drill-down for those who require it. If there is a way to achieve this, I would appreciate your help.