VBA to create Pivot table from text file

[Guest]

Hello
I use vba in Access with an Excel reference to create a workbook containing
the data and generate a pivot table. The query is now generating > 65K
records which exceeds what Excel can hold. I have had no luck in getting the
Excel reference to read data directly from the Acces table so I have decided
to export the data to text and use the text file as the data source for the
pivot table.

Bil xlApp as Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim pc As Excel.PivotCache

set xlapp = creatobject("Excel.Application")
Set xlwb = xlapp.workbooks.add
Set xlws = xlwb.sheets(1)


txtpath = "<path to data in a text file>"

Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
pc.connection = ??????

after that how do I access the data in the text file? as it has field names
but no tablename as such....

 

[Andy Wiggins]

If you are using Access as your source, why do you need to create the Pivot
Table in Excel?
Or, within Excel, have you considered refining your needs into several
smaller pivot tables?

 

[Guest]

Thanks, Andy

I need to use excel because the pivot table is better implemented than in
Access and that is what the users want.

I have no problems using Excel to draw the data from Access but geting
Access to tell Excel to draw the Access data... well that seems to be a step
too far so its text files I think