I want to pull data from about 100 different Excel files. They are located in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something like
S:\Folder\January\CaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula. Any suggestions???

Ralph,

You can make your formulas like this (to get the value from cell A3 from the
filename in
A6 and sheet in B6) Make sure A6 includes the path and the .xls, if the
file is closed.

="='[" & A6 & "]" & B6 & "'!" & "A3"

If you've done it properly, the cell contents will look like a valid linking
formula. (A good way to check is to make a valid link formula using an
actual workbook.) Then select all those formulas and run the macro below.

HTH,
Bernie

Sub TransformToTrueFormulas()
Dim myCell As Range
For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = myCell.Text
Next myCell
End Sub

"Ralph" wrote in message
...
I want to pull data from about 100 different Excel files. They are located
in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something
like
S:\Folder\January\CaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula. Any suggestions???

Ralph,

Sorry, I slightly misread your post.

Try something like the macro below, that will create a summary from all the
user-selected files. Change the sheet name and range to suit.

HTH,
Bernie
MS Excel MVP

Sub MakeLinkToMultipleUserSelectedFiles()
Dim filearray As Variant
Dim i As Integer
filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2).Formula = _
"='[" & ActiveWorkbook.Name & "]" & "Sheet1" & "'!" & "A3"
ActiveWorkbook.Close False
Next i
End If
End Sub

"Ralph" wrote in message
...
I want to pull data from about 100 different Excel files. They are located
in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something
like
S:\Folder\January\CaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula. Any suggestions???

Added apologies for all spelling errors! It's 1am here and I'm still working!!

"Ed (from UK and useless with computers!)" wrote:

Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

"Bernie Deitrick" wrote:

Ralph,

Sorry, I slightly misread your post.

Try something like the macro below, that will create a summary from all the
user-selected files. Change the sheet name and range to suit.

HTH,
Bernie
MS Excel MVP

Sub MakeLinkToMultipleUserSelectedFiles()
Dim filearray As Variant
Dim i As Integer
filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2).Formula = _
"='[" & ActiveWorkbook.Name & "]" & "Sheet1" & "'!" & "A3"
ActiveWorkbook.Close False
Next i
End If
End Sub

"Ralph" wrote in message
...
I want to pull data from about 100 different Excel files. They are located
in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something
like
S:\Folder\January\CaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula. Any suggestions???

Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

....='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

.....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

......='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

......but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

"Bernie Deitrick" wrote:

Ralph,

Sorry, I slightly misread your post.

Try something like the macro below, that will create a summary from all the
user-selected files. Change the sheet name and range to suit.

HTH,
Bernie
MS Excel MVP

Sub MakeLinkToMultipleUserSelectedFiles()
Dim filearray As Variant
Dim i As Integer
filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2).Formula = _
"='[" & ActiveWorkbook.Name & "]" & "Sheet1" & "'!" & "A3"
ActiveWorkbook.Close False
Next i
End If
End Sub

"Ralph" wrote in message
...
I want to pull data from about 100 different Excel files. They are located
in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something
like
S:\Folder\January\CaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula. Any suggestions???

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

Thanks Bernie, that worked fantastically!!

Now, one final question, is there anyway I can grab all the filenames from a
folder and put them into a column in excel? (i.e if I had files named X, Y
and Z in my folder could I get excel to automatically generate them in cells
A1, A2 and A3 respectivley?)

Thanks again for your help, it really is appreciated!

Ed

"Bernie Deitrick" wrote:

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

Ed,

Try the macro below. It should list all the .xls files in the folder "Excel test" and create a
linking formula to each one's sheet1 cell D16.

HTH,
Bernie
MS Excel MVP

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

With Application.FileSearch
..NewSearch
..LookIn = "'C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation
myFName = Application.Substitute(.FoundFiles(i), .LookIn & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub


"Ed (from UK and useless with comuters!)" oft.com
wrote in message ...
Thanks Bernie, that worked fantastically!!

Now, one final question, is there anyway I can grab all the filenames from a
folder and put them into a column in excel? (i.e if I had files named X, Y
and Z in my folder could I get excel to automatically generate them in cells
A1, A2 and A3 respectivley?)

Thanks again for your help, it really is appreciated!

Ed

"Bernie Deitrick" wrote:

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

Hi again Bernie,

I can't seem to get this one to run! I think it's probably just because I
am completely useless but any help would gratefuly recieved (again!)

When I go to run the macro, it just comes up with Syntax error and the first
line highlighted! What have I done wrong?!

Thanks yet again, and I promise to go to some lessons on using excel soon!

Ed

"Bernie Deitrick" wrote:

Ed,

Try the macro below. It should list all the .xls files in the folder "Excel test" and create a
linking formula to each one's sheet1 cell D16.

HTH,
Bernie
MS Excel MVP

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

With Application.FileSearch
..NewSearch
..LookIn = "'C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation
myFName = Application.Substitute(.FoundFiles(i), .LookIn & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub


"Ed (from UK and useless with comuters!)" oft.com
wrote in message ...
Thanks Bernie, that worked fantastically!!

Now, one final question, is there anyway I can grab all the filenames from a
folder and put them into a column in excel? (i.e if I had files named X, Y
and Z in my folder could I get excel to automatically generate them in cells
A1, A2 and A3 respectivley?)

Thanks again for your help, it really is appreciated!

Ed

"Bernie Deitrick" wrote:

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

Ed,

Sorry, mybad: an extra single quote found its way into the code - and there is a problem if you
don't get the capitalization exact, so try the version below:

HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFilesFixed()
Dim MyFormula As String
Dim myFName As String
Dim myCount As Integer
Dim i As Integer
myCount = 1

With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation

myFName = Application.Substitute(LCase(.FoundFiles(i)), LCase(.LookIn) & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub



"Ed (from UK and useless with comuters!)" oft.com
wrote in message ...
Hi again Bernie,

I can't seem to get this one to run! I think it's probably just because I
am completely useless but any help would gratefuly recieved (again!)

When I go to run the macro, it just comes up with Syntax error and the first
line highlighted! What have I done wrong?!

Thanks yet again, and I promise to go to some lessons on using excel soon!

Ed

"Bernie Deitrick" wrote:

Ed,

Try the macro below. It should list all the .xls files in the folder "Excel test" and create a
linking formula to each one's sheet1 cell D16.

HTH,
Bernie
MS Excel MVP

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

With Application.FileSearch
..NewSearch
..LookIn = "'C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation
myFName = Application.Substitute(.FoundFiles(i), .LookIn & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub


"Ed (from UK and useless with comuters!)"
oft.com
wrote in message ...
Thanks Bernie, that worked fantastically!!

Now, one final question, is there anyway I can grab all the filenames from a
folder and put them into a column in excel? (i.e if I had files named X, Y
and Z in my folder could I get excel to automatically generate them in cells
A1, A2 and A3 respectivley?)

Thanks again for your help, it really is appreciated!

Ed

"Bernie Deitrick" wrote:

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

hi again Bernie,

Well guess what, I still can't get it to work! It's saying....

Compile error:

Expected: identifier or bracketed expression

So sorry to keep bothering you, the first macro you gave me is working great
and has already saved me a whole lot of time!!!

cheers,
Ed

"Bernie Deitrick" wrote:

Ed,

Sorry, mybad: an extra single quote found its way into the code - and there is a problem if you
don't get the capitalization exact, so try the version below:

HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFilesFixed()
Dim MyFormula As String
Dim myFName As String
Dim myCount As Integer
Dim i As Integer
myCount = 1

With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation

myFName = Application.Substitute(LCase(.FoundFiles(i)), LCase(.LookIn) & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub



"Ed (from UK and useless with comuters!)" oft.com
wrote in message ...
Hi again Bernie,

I can't seem to get this one to run! I think it's probably just because I
am completely useless but any help would gratefuly recieved (again!)

When I go to run the macro, it just comes up with Syntax error and the first
line highlighted! What have I done wrong?!

Thanks yet again, and I promise to go to some lessons on using excel soon!

Ed

"Bernie Deitrick" wrote:

Ed,

Try the macro below. It should list all the .xls files in the folder "Excel test" and create a
linking formula to each one's sheet1 cell D16.

HTH,
Bernie
MS Excel MVP

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

With Application.FileSearch
..NewSearch
..LookIn = "'C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation
myFName = Application.Substitute(.FoundFiles(i), .LookIn & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub


"Ed (from UK and useless with comuters!)"
oft.com
wrote in message ...
Thanks Bernie, that worked fantastically!!

Now, one final question, is there anyway I can grab all the filenames from a
folder and put them into a column in excel? (i.e if I had files named X, Y
and Z in my folder could I get excel to automatically generate them in cells
A1, A2 and A3 respectivley?)

Thanks again for your help, it really is appreciated!

Ed

"Bernie Deitrick" wrote:

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

Ed,

One thing that often happens - since you are using the web interface - is that extra characters get
added to the code by whatever software is used to convert it to HTML for the web interface. So you
can either:

Post the code that you have, and I will tell you which characters to remove
Contact me privately - take the spaces out and change the dot to . and I will send you a clean
version in an email - or send you a working .xls file.
OR - post your (with extra spaces and other anti-spam stuff) email address and I will contact you
from that.

HTH,
Bernie
MS Excel MVP


"Ed (from UK and useless with comuters!)" oft.com
wrote in message ...
hi again Bernie,

Well guess what, I still can't get it to work! It's saying....

Compile error:

Expected: identifier or bracketed expression

So sorry to keep bothering you, the first macro you gave me is working great
and has already saved me a whole lot of time!!!

cheers,
Ed

"Bernie Deitrick" wrote:

Ed,

Sorry, mybad: an extra single quote found its way into the code - and there is a problem if you
don't get the capitalization exact, so try the version below:

HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFilesFixed()
Dim MyFormula As String
Dim myFName As String
Dim myCount As Integer
Dim i As Integer
myCount = 1

With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation

myFName = Application.Substitute(LCase(.FoundFiles(i)), LCase(.LookIn) & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub



"Ed (from UK and useless with comuters!)"
oft.com
wrote in message ...
Hi again Bernie,

I can't seem to get this one to run! I think it's probably just because I
am completely useless but any help would gratefuly recieved (again!)

When I go to run the macro, it just comes up with Syntax error and the first
line highlighted! What have I done wrong?!

Thanks yet again, and I promise to go to some lessons on using excel soon!

Ed

"Bernie Deitrick" wrote:

Ed,

Try the macro below. It should list all the .xls files in the folder "Excel test" and create a
linking formula to each one's sheet1 cell D16.

HTH,
Bernie
MS Excel MVP

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

With Application.FileSearch
..NewSearch
..LookIn = "'C:\Documents and Settings\excel test"
..FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate formula through string manipulation
myFName = Application.Substitute(.FoundFiles(i), .LookIn & "\", "")
MyFormula = "='" & .LookIn & "\[" & myFName _
& "]Sheet1'!D16"
'Set file name and link formula in columns A and B
Cells(myCount, 1).Value = myFName
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub


"Ed (from UK and useless with comuters!)"
oft.com
wrote in message ...
Thanks Bernie, that worked fantastically!!

Now, one final question, is there anyway I can grab all the filenames from a
folder and put them into a column in excel? (i.e if I had files named X, Y
and Z in my folder could I get excel to automatically generate them in cells
A1, A2 and A3 respectivley?)

Thanks again for your help, it really is appreciated!

Ed

"Bernie Deitrick" wrote:

Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed