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

You can start here
http://www.rondebruin.nl/copy3.htm

See example 3
Not open the files yourself (the code will do that)



--

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


"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

How about checking out and adapt

http://www.rondebruin.nl/copy3.htm



--
Regards,

Peo Sjoblom



"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

Thank you Ron.

Steven

On Jul 10, 1:54 pm, "Ron de Bruin" wrote:
You can start herehttp://www.rondebruin.nl/copy3.htm

See example 3
Not open the files yourself (the code will do that)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Steven" wrote in oglegroups.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

Thank you Peo.


On Jul 10, 1:56 pm, "Peo Sjoblom" wrote:
How about checking out and adapt

http://www.rondebruin.nl/copy3.htm

--
Regards,

Peo Sjoblom

"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

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

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

Yes. I couldn't get it to work for some reason. Thank you, but I think
it was a bit complicated for me. I had downloaded an add-in from this
thread that seems to do the job perfectly.

Steven


On Jul 10, 2:20 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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