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:=xlExter nal)
pc.connection = ??????

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