Linking multiple workbooks

[Guest]

I have to extract a value from the same cells in over 30 workbooks. Is there
a quick way to name all the workbooks in an array and state the array in the
cell formula?

 

[bgeier]

This may suit your needs
It works only if all of the open workbooks are open.

Sub WorkBookLoop()
Dim intWorkBookCounter As Integer
Dim intPlaceRow As Integer

intPlaceRow = 1

For intWorkBookCounter = 1 To Workbooks.Count
Workbooks(intWorkBookCounter).Activate
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRow, 2) =
Workbooks(intWorkBookCounter).Worksheets("Sheet1").Cells(1, 1).Value
intPlaceRow = intPlaceRow + 1
Next intWorkBookCounter
End Sub


If having all of the workbooks open is not feasible try this solution
from John Walkenbach.

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

I have used this several times and have had no trouble with it at all,
and was amazed at the speed it runs (1 application I have used it on
made about 600 semi-random queries populating a userform, and there was
no noticable difference in the time it took to read the same values from
an open workbook)
The website, does an excellent job of explaining how it works and how
to use it.

 

[Guest]

Thanks, but I was wondering if there was a method for the shortening the
worksheet formula?

 

[bgeier]

If you mean

"ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRo w, 2) =
Workbooks(intWorkBookCounter).Worksheets("Sheet1") .Cells(1, 1).Value"

not really, since you are looking at 2 different workbooks, you have to
tell Excel which workbook to use for what.

Check out the link from John Walkenbach, it may be easier in the long
run

 

[Ron de Bruin]

Try
http://www.rondebruin.nl/summary2.htm