Creating pivot table from text file in VBA

[justforgroups]

Hi,
I have this VBAwhich works fine for creating a pivot table from a sheet:

    Set rngData = Worksheets(gs_sheet).Cells(gl_head_row, 1).Resize(gl_last_row, gl_last_col)

    ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:= _
                                rngData, TableDestination:=Worksheets(lv_pivot).Cells(1, 1), TableName:="PivotTable1"

However I need to change the source to an external file which is a tab-delimited text file.

Any help please? Thanks.

 

[Weaver]

Do a 'record macro' while manually importing the data to excel (use 'get external data') then put this code in front of the code you're using to make the pivot table. I don't think you can make the pivot without importing the data first, but if this could cause issues, you could always hide the sheet the data is in.

HTH

 

[justforgroups]

Hi,
I'm counting on being able to read in the data without it going into sheets
Anyway I now have this coded but the ODBC select data source dialog appears at PivotTableWizard. The lv_src value is valid. Any ideas? Thanks.

ConnectString = "ODBC; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & lv_src & "; Extensions=asc,csv,tab,txt; UID=;;"
    QArray = Array(ConnectString, "SELECT * FROM InvSales.txt")
    ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:= _
                                QArray, TableDestination:=Worksheets(lv_pivot).Cells(1, 1), TableName:="PivotTable1"

 

[Weaver]

I'd imagine you could read the data without it going into a sheet, but if you can produce a pivot table from a query in this way then you know more about it than me. I'll keep tabs on this thread!

 

[justforgroups]

My knowledge of VBA is very limited!
Do you really think it is not possible to create a pivot from an ODBC source without saving it to a sheet first?
All the ODBC examples I've read, take data from a database table as the source of the pivot.