Search multiple workbooks

[upsidedown_pw]

I have a summary workbook which adds ups cells from a number of different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6 per
cell and have created a number of cells which I then total. The cells doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong so I
am wondering if there is a way I can ask the cell to go into the named folder
and search every workbook within it.

Any help would be greatly appreciated!

 

[Don Guillett]

http://j-walk.com/ss/excel/tips/tip82.htm

 

[upsidedown_pw]

Thanks Don.

I have never used VBA before but am determined to work it out.

Having looked at that link it appears that the code is asking to search a
particular work book for something. I want to look at a particular cell and
sheet in all workbooks in a particular folder.

Is there a way to adapt the code to do this?

http://j-walk.com/ss/excel/tips/tip82.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I have a summary workbook which adds ups cells from a number of different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6
per
cell and have created a number of cells which I then total. The cells
doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong so
I
am wondering if there is a way I can ask the cell to go into the named
folder
and search every workbook within it.

Any help would be greatly appreciated!

 

[Don Guillett]

There are several ways to do this with a macro.
You can open each file and build a sum
You can make a list and get the value withOUT opening the files
You can make one cell get the values and sum as it goes.
Contact me privately.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks Don.

I have never used VBA before but am determined to work it out.

Having looked at that link it appears that the code is asking to search a
particular work book for something. I want to look at a particular cell
and
sheet in all workbooks in a particular folder.

Is there a way to adapt the code to do this?

http://j-walk.com/ss/excel/tips/tip82.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I have a summary workbook which adds ups cells from a number of
different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6
per
cell and have created a number of cells which I then total. The cells
doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong
so
I
am wondering if there is a way I can ask the cell to go into the named
folder
and search every workbook within it.

Any help would be greatly appreciated!