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....

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?

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Bill" wrote in message
...
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....

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

"Andy Wiggins" wrote:

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?

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Bill" wrote in message
...
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....