Excel VBA with Pivot Table

[bryant_16]

Hi guys,

Need some pointers over here.

I have this program that is able to generate a CSV file every month, with its columns the same.

I need to create a excel spreadsheet, that have a Pivot Table with the values from this CSV file.

Output: This excel spreadsheet is able to open out CSV file and create/refresh the current Pivot Table based on the new values from the CSV file each month.

Can someone guide me or give me some pointers or how do I go about achieving this?


Thanks in advance.

[Bob Phillips]

Do it manually in Excel withy the macro recorder on, that will give you a good start.

[bryant_16]

But in the future, those CSV files that are being generated, how to handle that? Need to record the macro again?

[Jayfunk360]

I would do a template Excel Workbook with the VBA macro in then dump the info into a data sheet then get the VBA to generate the pivot table from that like below

'creating data Range for Pivot Table
Worksheets("Data").Select

Dim datarange As Range
Set datarange = Worksheets("Data").Range("A1", Cells(Range("A65000").End(xlUp).Row, Range("XX1").End(xlToLeft).Column))

'Creat Pivot Table

Sheets("Ptable").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
datarange, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Ptable!R1C1", TableName:="Whatever", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Ptable").Select
With ActiveSheet.PivotTables("Whatever").PivotFields("Fieldname")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Whatever").AddDataField ActiveSheet.PivotTables( _
"Whatever").PivotFields("Value"), "Sum of Value", xlSum

you could of course change the pivot table it is a simple version

[Bob Phillips]

But in the future, those CSV files that are being generated, how to handle that? Need to record the macro again?

No, you would tidy-up the recorded code and turn it into a repeatable macro.

[bryant_16]

No, you would tidy-up the recorded code and turn it into a repeatable macro.

Can I check with you how to turn it into a repeatable macro?

[bryant_16]

I would do a template Excel Workbook with the VBA macro in then dump the info into a data sheet then get the VBA to generate the pivot table from that like below


you could of course change the pivot table it is a simple version

How do you dump the the info into a data sheet? Copy and paste in?

[Jayfunk360]

you could c+p or at least copy it and when the workbook opens run a piece of code to dump it into the data sheet

[bryant_16]

you could c+p or at least copy it and when the workbook opens run a piece of code to dump it into the data sheet

But I cannnot c+p, I would need to open the CSV file within the Excel Spreadsheet first then can run the codes to generate the Pivot Table.

[Jayfunk360]

You could open it in a different workbook and c+p it or Import the data

[Bob Phillips]

Add it to your Personal.xlsm workbook, or create a utility add-in.

[bryant_16]

You could open it in a different workbook and c+p it or Import the data

How do you import the data over through automation? Is there any pointers/examples which I can look at?

[bryant_16]

Add it to your Personal.xlsm workbook, or create a utility add-in.

How do you go about creating an utility add-in? But each time the user opens up a new csv file, the utility add-in will be inside?

[Bob Phillips]

Not if you add it to Personal.xlsm or an addin mate, these will always be loaded into Excel.

I would write the utility to get the user to navigate to a folder, and pick the csv file, open it and process it. You can add a button to the ribbon or the QAT to launch it.

Here is a nice link on Personal.xlsm.

And here is one about addins.

[bryant_16]

Hi there, what you mean by writing the utility to get the user to pick the csv file, open it and process it?

You mean write it inside the Excel?

[bryant_16]

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
 "" & Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1)) & _
"!R1C33:R1048576C106", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12

I have this, but it is always pointing to Sheet1, how do I make it in such a way, it is always a new sheet inside the spreadsheet?