Macro help - Moving 2 cells from 100 separate files into new folder

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

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
 
Another one similar
http://www.rondebruin.nl/summary2.htm
With Application.FileSearch
Click to expand...
Is gone in 2007



--

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


Bernie Deitrick said:
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 said:
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
Click to expand...
Click to expand...
 
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


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


I am opening 100 excel files at a time and I need to do the following:
Click to expand...
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.
Click to expand...
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.
Click to expand...
Thank you.
Click to expand...
Steven
Click to expand...
Click to expand...
 
Back
Top