PDA

View Full Version : Excel VBA with Pivot Table



bryant_16
01-01-2014, 11:09 PM
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
01-02-2014, 01:20 AM
Do it manually in Excel withy the macro recorder on, that will give you a good start.

bryant_16
01-02-2014, 01:44 AM
But in the future, those CSV files that are being generated, how to handle that? Need to record the macro again?

Jayfunk360
01-02-2014, 01:53 AM
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
01-02-2014, 01:57 AM
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
01-02-2014, 02:02 AM
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
01-02-2014, 02:03 AM
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
01-02-2014, 02:09 AM
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
01-02-2014, 02:22 AM
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
01-02-2014, 02:41 AM
You could open it in a different workbook and c+p it or Import the data

Bob Phillips
01-02-2014, 02:41 AM
Add it to your Personal.xlsm workbook, or create a utility add-in.

bryant_16
01-02-2014, 08:23 PM
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
01-02-2014, 08:26 PM
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
01-03-2014, 05:28 AM
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 (http://www.rondebruin.nl/win/personal.htm).

And here is one about addins (http://www.fontstuff.com/vba/vbatut03.htm).

bryant_16
01-12-2014, 11:42 PM
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
01-13-2014, 01:39 AM
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?