Add cells across multiple workbooks that increase...

G

Guest

I will try to explain this the best that I can...

I have around 100 excel workbooks that contain the same exact sheets in
each. In each of these worksheets there are cells that contain a number
value that I would like to total on one 'master' sheet. These workbooks are
in there own folders but the workbooks all have the same name. As time goes
on there will be more folders and workbooks created that I want to total
without having to change the master.

Is there a way to count the value of a cell from these workbooks and not
have to be concerned if there are additional workbooks added? I was thinking
of some type of VBA or script that could run and go out and search these
folders for that file name then count the cells...

Is there a better way to do this? Thanks in advance.
 
R

Roger Govier

Hi Steve

Are there multiple sheets in each book with values, or just a single
sheet?
If there is a single sheet, is there any reason not to have them all in
the same workbook, as that would make the task much easier?
 
G

Guest

There are several sheets in the workbook but only the values of the one sheet
(which also have the same name) are needed. The reason that they are
seperate is that these workbooks are being used as estimates for the company
I work for. Each estimate must be in a unique folder. What I am trying to
accomplish is gathering how many devices of each type I need from these
estimates.
 
R

Rick Rothstein \(MVP - VB\)

There are several sheets in the workbook but only the values of the one
sheet
(which also have the same name) are needed. The reason that they are
seperate is that these workbooks are being used as estimates for the
company
I work for. Each estimate must be in a unique folder. What I am trying
to
accomplish is gathering how many devices of each type I need from these
estimates.

A couple of more facts about the layout might be useful. Are all the folders
(directories to old timers like me<g>) containing estimates under the same
master folder? That is, if we go to the master folder, can we restrict are
search to just folders located within it? If so, are there any other folders
inside the master folder that do not contain estimates? If so, are the
folders with estimates named in some similar way so that they can easily be
distinguished from the non-estimate folders? What I am trying to get at is a
method to identify where the workbooks are without having to search the
entire hard disk(s) looking for them. In the end, I am not sure I will be
able to answer your question, but those with the knowledge to do so should
find this information useful.

Rick
 
G

Guest

The rdbmerge addin that Ron has is getting closer, however it does not scan
all of the subfolders (directories), even when selected to do so...

The file structure is as such (with different names);

Home
- Building 1
- 102 Charly
- Documents
- 104 Dave
- Documents
- 111 Fred
- Documents
- Building 2
- 221 Mary
- Documents
- 234 Sue
- Documents
- 258 Lisa
- Documents

The xls file is in the documents folder. The workbook has the same name and
the worksheet is also the same name. It is on a network mapped drive. I am
going to play around with some of the other utilities available on that site
but still have not a foolproof way of getting this to work, yet.
 
R

Rick Rothstein \(MVP - VB\)

The file structure is as such (with different names);
Home
- Building 1
- 102 Charly
- Documents
- 104 Dave
- Documents
- 111 Fred
- Documents
- Building 2
- 221 Mary
- Documents
- 234 Sue
- Documents
- 258 Lisa
- Documents

The xls file is in the documents folder. The workbook has the same name
and
the worksheet is also the same name. It is on a network mapped drive.

A follow up question... are you looking to combine worksheets in all the
Building directories taken together, or in each Building directory
individually?

Rick
 
G

Guest

I am looking to total the amount of a certain cell taken from the first
worksheet from each workbook in all directories and subdirectories. The file
and worksheet name is the same in each directory and subdirectory. This
total will then be displayed in a new xls file which will keep an updated
quanity. I have been playing with more scripting, only I can not find one
that will successfully make it down through all of the subdirectories...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top