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