Extracting data from hyperlinked spreadsheets

[akfrumtarn]

I have a master spreadsheet with hyperlinks to around 2,000 other
spreadsheets. All of the hyperlinks are held in the same column. All of the
other spreadsheets are of a standard format. There are 8 cells on each of
these spreadsheets that contain data that I want to collate in the master
spreadsheet. How do I do this without resorting to manually linking to all
2,000 sheets?

 

[ryguy7272]

This should help you out:
http://www.rondebruin.nl/tips.htm

Pay attention to the section titled "Copy/Paste/Merge examples".
Your explanation sounds a little confusing to me, but I know you know what
you want; you should find it in that web link above.


Regards,
Ryan---

 

[akfrumtarn]

Thanks for the reply, the GetValue function appears to be on the right line
of attack. However, I need to define the path and file by reading each of the
hyperlinks already stored in each of the 2,000 cells of the excel column
(i.e. from cells B2 to B2000). To do this I need an excel function that I can
place in, say D2 and copy down to D2000 e.g.
=GETVALUE(B2,$J$16)
the outcome is that the function goes to the hyperlink held in B2 and goes
to the linked spreadsheet, gets the value from cell $J$16 (always the same
cell in each spreadsheet) and places the result in the master spreadsheet
cell D2. As I copy the formula down the master spreadsheet to D3, D4 etc I
get the same process repeated, but each time going to a different hyperlink
stored in B3, B4 etc.

I hope my problem is a little clearer for you to understand. Thanks again
for your help.

 

[ryguy7272]

Hummm, I'm thinking this macro may work for you:

Sub ListData10()
Dim ws As Worksheet
Dim rCopy As Range
Dim rDest As Range

Set rDest = ActiveWorkbook.Worksheets("Summary").Range("B3") ' < --
This is where the macro starts
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Summary" Then
rDest.Offset(0, -1).Value = ws.Name
With ws.Range("B39:T39") '< -- Change this to match your need
rDest.Resize(1, .Columns.Count).Value = .Value
End With
Set rDest = rDest.Offset(1, 0)
End If
Next ws
End Sub

It will take a little engineering on your part (i.e. customize for your
specific needs) but I think that will do what you need.


Regards,
Ryan--