Pivot Table from external CSV file using VBA

[Anh Bui]

Hi all,

I want to programmatically generate a pivot table using the data stored in a
csv file using VBA. I try using the PivotTableWizard object but don't know
how to specified the value for the parameter SourceData in this case.
Please help me out on this problem.

Thank you very much,
Anh Bui

 

[DomThePom]

Hi
I don't think you can use a csv directly as an external data source. I see 2
options
1. Open the csv in excel, do any parsing required and then use the range as
your xlDatabase sourcedata
2. Cretae an access mdb file and connect to your csv file. Then you can make
the access mdb an external datasource for your pivottable

 

[Anh Bui]

Really? Since I was able to do this manually as following:
1-open PivotTAble and PivotChart Wizard
2-Select External data source and click Next
3-Click on "Get Data". In the Choose Data Source dialog, select <New Data
Source> and click OK
4-In the "Create New Data Source", enter a certain name and select "Driver
da Microsoft para arquivos... (*.txt; *.csv)" at field number 2.
5-Click on the button "Connect" at field number 3 and select the folder
which contains the csv file
6-The select the csv file list in the combobox in field 4

The we will be able to select the column we want and generate the pivot
table directly from it.