[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel PivotTable:  Convert Macro to VBScript

Posted on 2011-04-21
22
Medium Priority
?
1,268 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:ITDeptAtPCS
  • 12
  • 10
22 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441235
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
0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441387
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

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441405
I see you have made quite a progress here ;-)

Sid
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441422
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)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441465
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
0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441486
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441500
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

0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441514
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441528
Ok :)

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

Sid
0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441628
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441643
Is every sheet named ABB in all three files?

Sid
0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441660
Yes, each file only has one sheet named "ABB"
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35441668
Ok I am good to go :)

Sid
0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35441687
Great!  I really appreciate you taking a stab at it.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35442026
Ok is this the output you are looking at?

Sid
Sample.csv
0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35442099
Yes sir that's great!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35442117
Ok. Let me do few more tests.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35442794
Ok Done. :)

Just Commenting the code.

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35442824
TRIED and TESTED

Ok Try this. Please Replace the path of the folder where the 3 files are. This code will loop through every excel file in that folder and convert them to CSV in the format that you want. Also note that we do not need to delete any shapes because when we save the file as CSV, it gets automatically deleted :)

Hope this helps

Sid

Code

'<~~ Folder where the 3 files are
Const MyPath = "C:\Temp\"
Const xlCSV = 6
Const xlUp = -4162
Const xltoLeft = -4131
Const xlDatabase = 1
Const xlPivotTableVersion10 = 1
Const xlRowField = 1
Const xlSum = -4157

Dim oXL, oFolder, aFile, FSO
Dim wb1, wb2, ws1, ws2
Dim LastRow, pvtDataSource,MyPivotCache

Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = FSO.GetFolder(MyPath)

'~~> Loop through each folder to get the file name
For Each aFile In oFolder.Files
    '~~> Check if it is an excel file
    If Right(LCase(aFile.Name), 4) = ".xls" Then
        Set wb1 = oXL.Workbooks.Open(MyPath & aFile.Name)
        Set ws1 = wb1.Sheets("ABB")
        oXL.Visible = True
        With ws1
            '~~> Clean UP
			.Rows("1:5").Delete 
            .Range("A:C,E:E").Delete 
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            
			'~~> Set the Pivot Table Data Source
            pvtDataSource = "ABB!R1C1:R" & LastRow & "C5"
            
			Set ws2 = wb1.WorkSheets.Add
            ws2.Name = "PvtTable"
			
			'~~> Add Pivot Table
            oXL.activeworkbook.PivotCaches.Add(xlDatabase, pvtDataSource).CreatePivotTable "'PvtTable'!R3C1", _
			"PivotTable1", xlPivotTableVersion10
        End With
        
		'~~> Set the Fields
        With ws2.PivotTables("PivotTable1").PivotFields("Acct Num")
            .Orientation = xlRowField
            .Position = 1
        End With
        
		ws2.PivotTables("PivotTable1").AddDataField oXL.ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ABB"), "Sum of ABB", xlSum
        
        ws2.Copy

        Set wb2 = oXL.Workbooks(oXL.ActiveWorkbook.Name)
        
		'~~> Save As csv
		wb2.SaveAs MyPath & Replace(wb1.Name,".xls","") & ".csv", xlCSV
		
		oXL.DisplayAlerts = False
		wb1.Close False
		wb2.Close False
		oXL.DisplayAlerts = True
    End If
Next

Set wb1 = Nothing
Set ws1 = Nothing
Set wb2 = Nothing
Set ws2 = Nothing
Set oFolder = Nothing
oXL.Quit
Set oXL = Nothing

Open in new window

0
 
LVL 1

Author Comment

by:ITDeptAtPCS
ID: 35442909
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!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35442916
Merging them should be a different question :)

Sid
0
 
LVL 1

Author Comment

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

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_26970728.html

0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Screencast - Getting to Know the Pipeline

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question