I have an excel spreadsheet with a PivotTable. The spreadsheet is
about 20 megs in size (since it has a large set of data associated
with it). It was sent to me by someone who actually has the source
data.

The PivotTable is completely functional, but I'd like to extract the
raw data behind it to place into an Access database. I've run some VB
code on it (see code below) and it's produced output about the
connection that WAS used to access the data. It appears that the query
used was a simple one that only accessed one flat .CSV file.

How can I go about pulling out this data without still having the
underlying connection?

My thoughts included fetching it off the PivotTable.PivotCache object
somehow (querying to get back a recordset, ???).

-------- Code ---------

Sub ListSourceData()
   'Declare our variables.
   Dim newSheet As Worksheet, sdArray As Variant
   Dim oldSheet As Worksheet, pt As PivotTable, r As Integer

   'Set our variables.
   Set oldSheet = ActiveSheet
   Set newSheet = ActiveWorkbook.Worksheets.Add

   newSheet.Range("A1").Value = oldSheet.Name
   r = 3
   
   'Loop through each PivotTable on the active sheet and place its
ODBC information on a new sheet.
   For Each pt In oldSheet.PivotTables
       newSheet.Cells(r, 1).Value = pt.Name
       newSheet.Cells(r + 1, 1).Value = pt.PivotCache.Connection
       newSheet.Cells(r + 2, 1).Value = pt.PivotCache.Sql
       newSheet.Cells(r + 3, 1).Value = pt.SourceData
       r = r + 4
   Next pt
   newSheet.Cells.EntireColumn.ColumnWidth = 100
   newSheet.Cells.EntireRow.AutoFit
End Sub

-------- Output -------- 

PivotTable1
ODBC;DSN=Text Files;DefaultDir=C:\My
Documents;DriverId=27;MaxBufferSize=2048;PageTimeout=5;UID=admin;
"SELECT uksmrte.REG, uksmrte.ROUTE, uksmrte.CABIN, uksmrte.TRKGRP,
uksmrte.CNET, uksmrte.CPSJ, uksmrte.CLOAD, uksmrte.CEXP, uksmrte.CLCH,
uksmrte.TPSJ, uksmrte.TNET, uksmrte.QVOL, uksmrte.QRND, uksmrte.QCMP,
uksmrte.QYLD, uksmrte.QCAB, uksmrte.CIMP, uksmrte.ACCNAME
FROM `c:\windows\desktop\dealing group`\uksmrte.csv uksmrte"
DSN=Text Files;DefaultDir=C:\My
Documents;DriverId=27;MaxBufferSize=2048;PageTimeout=5;UID=admin;