Need formula help linking closed excel files

[Steven]

Hello. I need to move three identical cells that are in 2000 separate
excel files into one sheet for analysis. A few months ago someone had
told me to copy the filenames into an excel spreadsheet and then to
use a formula to have the program extract the cell data from the
closed files into the opened worksheet.

I have done all of that but I am now getting a formula error that I
cannot figure out.

When I opened a new spreadsheet, I have put the file names in the A1
column and the formula below in the B1 column.

The formula that I am using is:

='c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\[" & a1 & "]Sheet1"'!"$A$1


Here is what I believe each part of the formula is trying to do.

c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\ - This is the file directory path.

[" & a1 & "] - I assume that this tells the program to search in the
directory path AND in the specific file name in column a1, and then
to...

Sheet1"'!"$A$1 - ... look in Sheet1 in all the specific files. The
last part of the formula tells the program which cell to copy into the
B1 column.

This is where my problem lies. I am getting a formula error where
Excel is highlighting this portion of the formula. "$A$1

I am very inexperienced with Excel formulas. Can someone explain to me
where my error is? I am wondering if there is an error within any
other part of the formula.

Thank you very much. If I can get this formula to work, it will save
me hours of work.

Steven

 

[Bernie Deitrick]

Steven,

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

 

[Bernie Deitrick]

Oh, and to get the formula to work, you would need to have used:

='C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ\[" & a1 &
"]Sheet1'!$A$1"

But then you would have needed a macro to convert the result of the formula into a true formula.
With the cells with the above formula selected:

Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Oops, I forgot to include the other macros that are called:

Sub TurnOff()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
End Sub

Sub TurnOn()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

These assume that you use automatic calculation - rare to not use it, especially for newer users.

HTH,
Bernie
MS Excel MVP

Oh, and to get the formula to work, you would need to have used:

='C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ\[" & a1 &
"]Sheet1'!$A$1"

But then you would have needed a macro to convert the result of the formula into a true formula.
With the cells with the above formula selected:

Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub


HTH,
Bernie
MS Excel MVP

Hello. I need to move three identical cells that are in 2000 separate
excel files into one sheet for analysis. A few months ago someone had
told me to copy the filenames into an excel spreadsheet and then to
use a formula to have the program extract the cell data from the
closed files into the opened worksheet.

I have done all of that but I am now getting a formula error that I
cannot figure out.

When I opened a new spreadsheet, I have put the file names in the A1
column and the formula below in the B1 column.

The formula that I am using is:

='c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\[" & a1 & "]Sheet1"'!"$A$1


Here is what I believe each part of the formula is trying to do.

c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\ - This is the file directory path.

[" & a1 & "] - I assume that this tells the program to search in the
directory path AND in the specific file name in column a1, and then
to...

Sheet1"'!"$A$1 - ... look in Sheet1 in all the specific files. The
last part of the formula tells the program which cell to copy into the
B1 column.

This is where my problem lies. I am getting a formula error where
Excel is highlighting this portion of the formula. "$A$1

I am very inexperienced with Excel formulas. Can someone explain to me
where my error is? I am wondering if there is an error within any
other part of the formula.

Thank you very much. If I can get this formula to work, it will save
me hours of work.

Steven

 

[Steven]

Oops, I forgot to include the other macros that are called:

Sub TurnOff()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
End Sub

Sub TurnOn()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

These assume that you use automatic calculation - rare to not use it, especially for newer users.

HTH,
Bernie
MS Excel MVP

Oh, and to get the formula to work, you would need to have used:

='C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ\[" & a1 &
"]Sheet1'!$A$1"

But then you would have needed a macro to convert the result of the formula into a true formula.
With the cells with the above formula selected:

Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub

HTH,
Bernie
MS Excel MVP

Hello. I need to move three identical cells that are in 2000 separate
excel files into one sheet for analysis. A few months ago someone had
told me to copy the filenames into an excel spreadsheet and then to
use a formula to have the program extract the cell data from the
closed files into the opened worksheet.
I have done all of that but I am now getting a formula error that I
cannot figure out.
When I opened a new spreadsheet, I have put the file names in the A1
column and the formula below in the B1 column.
The formula that I am using is:
='c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\[" & a1 & "]Sheet1"'!"$A$1
Here is what I believe each part of the formula is trying to do.
c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\ - This is the file directory path.
[" & a1 & "] - I assume that this tells the program to search in the
directory path AND in the specific file name in column a1, and then
to...
Sheet1"'!"$A$1 - ... look in Sheet1 in all the specific files. The
last part of the formula tells the program which cell to copy into the
B1 column.
This is where my problem lies. I am getting a formula error where
Excel is highlighting this portion of the formula. "$A$1
I am very inexperienced with Excel formulas. Can someone explain to me
where my error is? I am wondering if there is an error within any
other part of the formula.
Thank you very much. If I can get this formula to work, it will save
me hours of work.
Steven

Thank you Bernie. My problem is that I don't know how to add these
other macro's. Looks very complicated.
If I open up 100 files at a time, is there a quick way for me to
transfer some of the cells into a new worksheet?

Thank you again.

Steven

 

[Bernie Deitrick]

Steven,

Please post your response at the top of the message - that's the custom in these groups.

It's pretty simple to use macros. Here's a combined version:

Sub TransformToFormula2()
Dim myCell As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

End Sub

Then visit

http://www.mvps.org/dmcritchie/excel/getstarted.htm

for more information on how to use macros.

Again, you would need to use the formula

='C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ\[" & a1 & "]Sheet1'!$A$1"


HTH,
Bernie
MS Excel MVP

Thank you Bernie. My problem is that I don't know how to add these
other macro's. Looks very complicated.
If I open up 100 files at a time, is there a quick way for me to
transfer some of the cells into a new worksheet?

Thank you again.

Steven

Oops, I forgot to include the other macros that are called:

Sub TurnOff()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
End Sub

Sub TurnOn()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

These assume that you use automatic calculation - rare to not use it, especially for newer users.

HTH,
Bernie
MS Excel MVP

Oh, and to get the formula to work, you would need to have used:

='C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ\[" & a1 &
"]Sheet1'!$A$1"

But then you would have needed a macro to convert the result of the formula into a true
formula.
With the cells with the above formula selected:

Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub

HTH,
Bernie
MS Excel MVP

Hello. I need to move three identical cells that are in 2000 separate
excel files into one sheet for analysis. A few months ago someone had
told me to copy the filenames into an excel spreadsheet and then to
use a formula to have the program extract the cell data from the
closed files into the opened worksheet.

I have done all of that but I am now getting a formula error that I
cannot figure out.

When I opened a new spreadsheet, I have put the file names in the A1
column and the formula below in the B1 column.

The formula that I am using is:

='c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\[" & a1 & "]Sheet1"'!"$A$1

Here is what I believe each part of the formula is trying to do.

c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\ - This is the file directory path.

[" & a1 & "] - I assume that this tells the program to search in the
directory path AND in the specific file name in column a1, and then
to...

Sheet1"'!"$A$1 - ... look in Sheet1 in all the specific files. The
last part of the formula tells the program which cell to copy into the
B1 column.

This is where my problem lies. I am getting a formula error where
Excel is highlighting this portion of the formula. "$A$1

I am very inexperienced with Excel formulas. Can someone explain to me
where my error is? I am wondering if there is an error within any
other part of the formula.

Thank you very much. If I can get this formula to work, it will save
me hours of work.

Steven

 

[Steven]

Oops, I forgot to include the other macros that are called:

Sub TurnOff()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
End Sub

Sub TurnOn()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

These assume that you use automatic calculation - rare to not use it, especially for newer users.

HTH,
Bernie
MS Excel MVP

Oh, and to get the formula to work, you would need to have used:
='C:\Documents and Settings\Owner\My Documents\Subject Profile Summary LBJ\[" & a1 &
"]Sheet1'!$A$1"
But then you would have needed a macro to convert the result of the formula into a true formula.
With the cells with the above formula selected:
Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub
HTH,
Bernie
MS Excel MVP
Hello. I need to move three identical cells that are in 2000 separate
excel files into one sheet for analysis. A few months ago someone had
told me to copy the filenames into an excel spreadsheet and then to
use a formula to have the program extract the cell data from the
closed files into the opened worksheet.
I have done all of that but I am now getting a formula error that I
cannot figure out.
When I opened a new spreadsheet, I have put the file names in the A1
column and the formula below in the B1 column.
The formula that I am using is:
='c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\[" & a1 & "]Sheet1"'!"$A$1
Here is what I believe each part of the formula is trying to do.
c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\ - This is the file directory path.
[" & a1 & "] - I assume that this tells the program to search in the
directory path AND in the specific file name in column a1, and then
to...
Sheet1"'!"$A$1 - ... look in Sheet1 in all the specific files. The
last part of the formula tells the program which cell to copy into the
B1 column.
This is where my problem lies. I am getting a formula error where
Excel is highlighting this portion of the formula. "$A$1
I am very inexperienced with Excel formulas. Can someone explain to me
where my error is? I am wondering if there is an error within any
other part of the formula.
Thank you very much. If I can get this formula to work, it will save
me hours of work.
Steven

Thank you Bernie. My problem is that I don't know how to add these
other macro's. Looks very complicated.
If I open up 100 files at a time, is there a quick way for me to
transfer some of the cells into a new worksheet?

Thank you again.

Steven

Thank you Bernie. Much appreciated.

Steven