Macro to sum values for each excel workbook within a subdirectory

I have a few hundred excel files that look like this:

ID      GRIDCODE      Area      SArea
1      5      1800.00000000000      1804.71003285000
2      3      2700.00000000000      2705.85632977000
3      4      1045.96195221000      1046.38409027000
4      4      576.34941101100      578.02949378200
...     ...     ...                             ...

The gridcode column has 5 values (1-5).  I would like a macro that would sum the area and sarea for each value from the grid code.

The desired result from lc92poly_001 would yield this:

1      408701.2681      415069.1589
2      644001.9069      647772.428
3      129460736.2      132041031.9
4      69890762.14      70097473.11
5      3085928.639      3093677.251

If these calculations could be saved as either a new worksheet within the workbook or a new workbook would be great.  The files are are named like this lc92poly_xxx (where xxx = 001 - 426).

I would appreciate annotated code.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
The function is SUMIF(range, criteria, sum_range)
Translated this means
   range as the column B(Gridcode)
   crtieria (value to add on -i.e. 1 --but could be a cell refernce)
   sum_range (what numbers to add)
So, you would need two functions for one sum.  
However, since your data spans multiple regions, you can use a PivotTable to consolidate and to display the result.  Start the PivotTable wizard from the Data menu and select multiple selections.
The Pivot Table would look like the inserted table in the attached.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
justearthAuthor Commented:
Thanks DoDahD:

I used a pivot table to provide the sums above.  I was hoping for some guidance to incorporate the procedure into a macro. I need to do this several hundred times.

Richard DanekeTrainerCommented:
Sorry, I thought you were consolidating each workbook into one total.
You can make this add pivot table a macro by going to Tools, Macro, RecordMacro.
Store this in your PERSONAL workbook.  This lets the macro run whenever you have your copy of Excel open (it does not matter which workbook is open).  A small toolbar opens up (this is the Macro toolbar - when finished click on the square button (stop) to end the macro recording.
Create the Pivot Table as  before and stop when finished.
Go to Vieiw, Toolbars..., Customize, and add a button to run this macro on your toolbar.
Then, as you open each workbook, click on this button and the Pivot Table sheet will be added.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

justearthAuthor Commented:
Thanks again. I should have been more clear, I didn't restate my intentions from the title into the question proper.  I want this to be done on all workbooks with the subdirectory with one macro.  I want to click one button.  Right, now even with recording the macro I still have to open several hundred sheets and press the button.  I was looking for maximum automation, which is where I need the help.  Can you help with that aspect?

open a new workbook

Save as xla

In your xla file, set up a template in sheet 1

column A
row 1 = GridCode
Row 2 - 6, grid code = 1 - 5

Column B
row 1 = AREA

Column C
row 1 = SAREA

Open the Visual Basic module, right click on the xla workbook and select Insert - Module

In the module, I suggest something like this

Private Const gbPath as string = "<<enter network path of the directory here>>"
Private Const gbFileNameStart as string = "lc92poly_"
Private Const gbNoOfFiles as integer  = 462

'Note, the above constants easily allow you to change the target directory, number of files, filename start yadda yadda!!

Public Sub GetTotalAreas ()
dim n as integer
dim nRow as integer
dim s1_Area as double,s1_sArea as double
dim s2_Area as double,s2_sArea as double
dim s3_Area as double,s3_sArea as double
dim s4_Area as double,s4_sArea as double
dim s5_Area as double,s5_sArea as double

s1_Area = 0
s2_Area = 0
s1_sArea = 0
s2_sArea = 0

for n = 1 to gbNoOfFiles & "\" & right("000"&n,3))

for nRow = 2 to 6 'This assumes that the range in each workbook is always A1:D6
s1_Area = s1_Area + activeworkbook.range("C" & nRow) 'assumes are in column c
s1_sArea = s1_sArea + activeworkbook.range("D" & nRow) 'assumes area in column d
next nRow

workbooks.close(gbPath & "\" & right("000"&n,3))

next n

Workbook("xla filename").Activate

Range("B2") = s1_Area
Range("C2") = sl_sArea


End Sub

Clearly, you will need to adapt this as you see fit, the file does not have to be an xla file, you could simplay save a normal template file.


justearthAuthor Commented:

Thanks for the quick response. I thought I responded yesterday, but I didn't.

I have never used an excel template before.  When I save a workbook as an xla, close it and open it back up the workbook is blank and states "Book1" for the name.  I then saved it as an Excel template, which maintains the data and macro I place into it, only I can't upload them here.

I entered my path to the source directory and pasted your code into the VB editor. I prepared the sheet1 like this:


When I execute the macro I get an error stating that "Public Sub GetTotalAreas ()" is not defined.

I definitely need some help modifying the code to get it to work for me and I am very eager because it seems like it will do the trick.

Thanks again,
can you attach your template please, let me take a look
justearthAuthor Commented:

EE will not only me to upload my templates as-is these were all created in Excel 2007.

Template_3darea.xltm macro enabled template
Template_3darea_notenabled.xltx Excel template
Template_3darea1.xlam Add-in

I changed the extenstion to .xlsx for each of the above for sake of uploading.

Thanks again,
Simon BallCommented:
this is the type of work where access is better than excel...

i'd import all these excel files into access, possibly into one table with a new colum denotiing the source file, then a simple query to provide the stats...

you could use access vba code to make ech excel a link table and then append its contentsinto the master table one at a time, based on a path to a source directory...

either way would take some time to write though...

i could provide some ample code if you are interested in this approach.
If you import or link to the Excel spreadsheet from Access, then run this query:

SELECT GridCode, Sum(Area), Sum(SArea) FROM tblAccessName GROUP BY GridCode;

presto, that simple!
justearthAuthor Commented:
Simon BallCommented:
is this a one off, will you need to keep importing lots of files, or will it be one extra file regularly?

Thanks, glad to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.