• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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:

GRIDCODE AREA SAREA
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.

Thanks,
JE
poly3d-001.xls
0
justearth
Asked:
justearth
  • 5
  • 2
  • 2
  • +2
4 Solutions
 
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.
EE-poly3d-001.xls
0
 
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.

Cheers,
JE
0
 
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
justearthAuthor Commented:
DoDahD:
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?


Cheers,
JE
0
 
RunriggerCommented:
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
etc
.
.
s1_sArea = 0
s2_sArea = 0
etc
.
.


for n = 1 to gbNoOfFiles

workbooks.open(gbPath & "\" & 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

etc
etc

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.

Cheers

Dave
0
 
justearthAuthor Commented:
Runrigger:

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:

GridCode AREA SAREA
1
2
3
4
5

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,
Cheers,
JE
0
 
RunriggerCommented:
can you attach your template please, let me take a look
0
 
justearthAuthor Commented:
Runrigger:

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,
JE

templates-.zip
0
 
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.
0
 
GRayLCommented:
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!
0
 
justearthAuthor Commented:
Thanks.
0
 
Simon BallCommented:
is this a one off, will you need to keep importing lots of files, or will it be one extra file regularly?

0
 
GRayLCommented:
Thanks, glad to help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now