I assumed you'd want to select the files rather than automatically import
from all .xls files in the directory. Of course, use the <Shift key to
facilitate selection of multiple files.

If speed is an problem, the macro can be rewritten to populate an array and
then batch dump into the summary sheet. Minimal testing.

Sub ImportData()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range, c As Range
Dim P As String
Dim wbarr As Variant
Dim i As Integer, ii As Integer

With Application
.ScreenUpdating = False
wbarr = .GetOpenFilename("Excel files (*.xls), *.xls", _
MultiSelect:=True)
End With
If VarType(wbarr) = vbBoolean Then Exit Sub
P = wbarr(1)
i = InStrRev(P, "\")
P = Left(wbarr(1), i - 1)
Set ws = Sheets("Summary") 'Assumed wks name in "Summary.xls"
Set r1 = ws.Range("A6:E6")
Set r2 = ws.Range("A2:A3, B5:B7") 'Used to get address only

For i = LBound(wbarr) To UBound(wbarr)
ii = 0
For Each c In r2
ii = ii + 1
r1(i, ii).Formula = "='" & P & _
"\[" & Dir(wbarr(i)) & "]Sheet1'!" & c.Address
Next c
r1(i, 6).Hyperlinks.Add r1(i, 6), wbarr(i)
Next i
Set r1 = r1.Resize(ii, 5)
r1.Value = r1.Value
Application.ScreenUpdating = True
End Sub

Regards,
Greg

"Krista F" wrote:

Hi Greg,

This is great - is there a way to get the array to recognize all files in
the directory rather than have to list out each one?

Krista

"Greg Wilson" wrote:

Oops...

Remove:
wbarr = Array("Combinations.xls", "Combinations.xls", "Combinations.xls",
"Combinations.xls")

I used that for testing purposes. I told you I wrote it in a hurry.

Regards,
Greg

"Greg Wilson" wrote:

Assumed is that the worksheet in Summary.xls is named "Summary" and that
Summary.xls is in the same folder as the others. Change to suit. Note that
this macro doesn't have to open any workbooks or copy and paste and so should
be slicker. Hyperlinks appear to work. I never use them so have no real
experience.

Written in a hurry with minimal testing:

Sub ImportData()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range, c As Range
Dim P As String
Dim wbarr As Variant
Dim i As Integer, ii As Integer

wbarr = Array("Car.xls", "Boat.xls", "Rv.xls", "Loco.xls")
wbarr = Array("Combinations.xls", "Combinations.xls", "Combinations.xls",
"Combinations.xls")
P = ThisWorkbook.Path 'If not correct change to path of above files
Set ws = Sheets("Summary") 'Assumed wks name in "Summary.xls"
Set r1 = ws.Range("A6:E6")
Set r2 = ws.Range("A2:A3, B5:B7") 'Used to get address only

For i = LBound(wbarr) To UBound(wbarr)
ii = 0
For Each c In r2
ii = ii + 1
r1(i + 1, ii).Formula = "= '" & P & _
"\[" & wbarr(i) & "]Sheet1'!" & c.Address
Next c
r1(i + 1, 6).Hyperlinks.Add r1(i + 1, 6), wbarr(i)
Next i

Set r1 = r1.Resize(ii, 5)
r1.Value = r1.Value
End Sub

Regards,
Greg


"Judy" wrote:

I have data in sheets Car.xls; Boat.xls; Rv.xls; Loco.xls
On the first sheet of each of these workbooks, I need to bring in the data
cells a2, a3, b5, b6, b7

And paste it into a summary.xls file.
Starting at Row 6; (a6,b6,c6,d6,e6)
then moving down 1 row for each xls above.

In addition to that, I would like in Column F of each row a hyperlink
created to link back to the source data (Car, Boat, Rv, Loc)

Any and all help is appreciated.