Linking multiple workbooks

[Newbie]

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.

[Newbie]

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

"bgeier" wrote:

>
> 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.
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=542052
>
>

[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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Newbie" <[email protected]> wrote in message news:[email protected]...
>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?