asked on

Excel PivotTable: Convert Macro to VBScript

Hi all, I have recorded a macro in which I am concerned with two columns; Acct Num and ABB.
Example:

Acct Num ABB
001 12.85
001 05.31
002 11.78
003 03.22
003 22.99
...

I'm trying to merge like Acct Nums and summing their ABBs:
Acct Num ABB
001 18.16
002 11.78
003 26.21

Here is the recorded macro:

Sub Macro1()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "test!R1C1:R5462C5").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Acct Num")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ABB"), "Sum of ABB", xlSum
End Sub

Open in new window

How can I convert this to VBScript to run on multiple files in which the amount of columns will vary and save the result as a new .csv file?

Thanks in advance!

SiddharthRout

Have never tried it with vb script but am willing to give it a try. Few questions

1) Do all the workbooks have a sheet called "Test"?
2) Does the data start from Cell A1 in all the "Test" Sheets?
3) What is the name of the name of the sheet where you want to create the Pivot?
4) And will all test sheets will have data with those headers Acct Num and ABB?

Sid

ITDeptAtPCS

ASKER

1) Yes - I've already started a script that removes unnecessary rows, columns, and an image - saving the file as a .csv. I'll post my current code below.
2) Row 1 contains column headers (Acct Num = A1 and ABB = E1).
3) Anything is fine as long as the final .csv contains only the pivottable result. If there is a better way that a pivottable to combine amounts for like acct numbers, I'm open to that too.
4) Yes

Const xlCSV = 6
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = true
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open("c:\Working\Accounting\Test\test.xls")
Set objWorksheet = objWorkbook.Worksheets("ABB")
i = 1

Do Until objExcel.Cells(i, 1).Value = "Client Id"
    If objExcel.Cells(i, 1).Value <> "Client Id" Then
        Set objRange = objExcel.Cells(i, 1).EntireRow
        objRange.Delete
        i = i - 1
    End If
    i = i + 1
Loop

i = 1
Do Until objExcel.Cells(1,i).Value = ""
	If 	objExcel.Cells(1,i).Value = "Client Id" OR objExcel.Cells(1,i).Value = "Client Name" OR	objExcel.Cells(1,i).Value = "G Number" OR objExcel.Cells(1,i).Value = "Acct Name" Then
		Set objRange = objExcel.Cells(1,i).EntireColumn
			objRange.Delete
			i = i - 1
	End If
	i = i + 1
Loop



objWorksheet.SaveAs "c:\Working\Accounting\Test\test.csv", xlCSV
objExcel.Quit

Open in new window

SiddharthRout

I see you have made quite a progress here ;-)

Sid

ITDeptAtPCS

ASKER

After the columns I don't need are removed using the code above, I'm left with columns A through E.
A - Acct Num (9 digit number)
B - Date End Date (MM/DD/YYY)
C - BPS (digit/s)
D - Billable Avg Dly Bal ($ amount)
E - ABB ($ amount)

SiddharthRout

Quick question.

Do you want to include the cleaning process before creating the pivot in all the files? Will all the files have a picture and same format? Or do you want me to write a script only for the 5 columns that you created above?

Sid

ITDeptAtPCS

ASKER

A little bit - I've don't have much of a background in coding and the above is my first attempt at piecing together and modifying what I've found on the web. I started to play around with the pivottable, but couldn't hash it out.

SiddharthRout

The reason, I ask that of the format of the files is different then the cleaning process can be little it complicated. And hence the question. :)

Do you want me to write a script only for the 5 columns that you created above if the formats are different?

Sid

ITDeptAtPCS

ASKER

Well if you want to go all out - let me add one thing. This script will be ran quarterly and in reality there are three xls files to start with, one for each month. So lets say:

Test1.xls
Test2.xls
Test3.xls

They are all in the same format and have the same picture. So if we could clean them and then merge them into a single test4.csv that would be great! Then once we have this master file, we can perform the pivot process.

SiddharthRout

Ok :)

Can you upload a sample file to work with so that I can get my references correct?

Sid

ITDeptAtPCS

ASKER

Sure thing - here is a dummy file with 89 rows of dummy data. Each of the real files has about 5,000. EE-Test.xls

SiddharthRout

Is every sheet named ABB in all three files?

Sid

ITDeptAtPCS

ASKER

Yes, each file only has one sheet named "ABB"

SiddharthRout

Ok I am good to go :)

Sid

ITDeptAtPCS

ASKER

Great! I really appreciate you taking a stab at it.

SiddharthRout

Ok is this the output you are looking at?

Sid
Sample.csv

ITDeptAtPCS

ASKER

Yes sir that's great!

SiddharthRout

Ok. Let me do few more tests.

Sid

SiddharthRout

Ok Done. :)

Just Commenting the code.

Sid

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITDeptAtPCS

ASKER

Thanks a ton! It works without issue, but ends up creating three separate csv files. Would it be possible to merge them into one and combine the amounts then?

If not, within the final csv files, could we strip out the rows that contain "Sum of ABB" and "Grand Total?" And then could we fill column C with the date that was originally found in Column B (Date End Date)? Either option is fine and I can create a separate EE question for the same points for each if you'd like.

Again thanks!

SiddharthRout

Merging them should be a different question :)

Sid

ITDeptAtPCS

ASKER

As should the addition of that date, I'd imagine. One thing at a time though...naturally.

https://www.experts-exchange.com/questions/26970728/Excel-VBScript-Adapting-Script-to-Merge-CSVs.html