Printable View
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.
Do it manually in Excel withy the macro recorder on, that will give you a good start.
But in the future, those CSV files that are being generated, how to handle that? Need to record the macro again?
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 versionQuote:
'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
No, you would tidy-up the recorded code and turn it into a repeatable macro.Quote:
Originally Posted by bryant_16
Can I check with you how to turn it into a repeatable macro?Quote:
Originally Posted by xld
How do you dump the the info into a data sheet? Copy and paste in?Quote:
Originally Posted by 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
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.Quote:
Originally Posted by Jayfunk360
You could open it in a different workbook and c+p it or Import the data
Add it to your Personal.xlsm workbook, or create a utility add-in.
How do you import the data over through automation? Is there any pointers/examples which I can look at?Quote:
Originally Posted by Jayfunk360
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?Quote:
Originally Posted by xld
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.
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?
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?Code: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