Another one similar
http://www.rondebruin.nl/summary2.htm

With Application.FileSearch
Is gone in 2007



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ...
Steven,

Did you try my macro from your previous post? No need to open the files (which just slows things
down).

****************************************

Try the sub below, which will create links to cells A1, B1, and C1 of Sheet1 in every file in the
folder

"C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ"


HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate Formulas through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 3).Formula = MyFormula
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!B1"
'Set cell formula
Cells(myCount, 4).Formula = MyFormula
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!C1"
'Set cell formula
Cells(myCount, 5).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub

"Steven" wrote in message
ups.com...
I am opening 100 excel files at a time and I need to do the following:

Create a macro in a new Excel file that will copy two cells from each
of the 100 files and put them next to each other in separate columns.
I will then need to open another 100 files and have the macro perform
the same function, but obviously adding the new new data below the
original data.

The data is in the same location in all the files, and the worksheets
have the same name. Please send a quick macro that I could use for
this purpose.

Thank you.

Steven