I want to programmatically create a pivottable on the basis of a text file.
One option would be to read the text file into sheet and then create the
pivottable from the sheet. However, the text file contains to many records to
be included a sheet. So I would instead want to directly link the pivottable
to the text file through ADO.

I have the made a Sub in VBA to do so (see my comments after the code):

Sub CreatePivotTableFromText()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim DBFile As String
Dim ConString As String
Dim QueryString As String

' Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)

ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\t;Jet
OLEDB:Engine Type=60;Extended Properties=Text;"
QueryString = "SELECT * FROM test.csv;"

With PTCache
.Connection = ConString
.CommandText = QueryString
End With

' Add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"

' Create pivot table
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1") , _
TableName:="TestPivot")

End Sub

Unfortunately, this does not work. I get an error 1004 on the statement:
With PTCache
.Connection = ConString

ConString ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\t;Jet
OLEDB:Engine Type=60;Extended Properties=Text;") connects to the folder c:\t.

The QueryString "SELECT * FROM test.csv;" selects all the fields in the file
test.csv in the c:\t folder.

I have tried using the connect and query string in an ADO connection and
recordset objects, passing the contents directly to a sheet. This works fine
up to the max no of rows allowed in a sheet, so there is no problem with the
connection string or query string as such. Rather, there seems to be a
limitation in what kind of connection string is allowed for the
PivotCache.Connection property.

I have considered linking the text file to Access and then accessing it
through Access. However, I cannot be sure that Access will be available on
the Pc's that will execute the Sub. Also, it seems kind of a clumsy way to go
about it. There must be a more simple, direct solution.

I hope that someone will save my day and let me know how to get this text
file set as the basis of the pivotcache and -table.


With kind regards,

Frank M.