we can split the string up, and put brackets aroung the last part, and then
put it back together. Here is an example:

Sub brackIt()
Dim s As String
s = "fds\f4f\bbgd\nhfnh\zxc.xls"
v = Split(s, "\")
v(UBound(v)) = "[" & v(UBound(v)) & "]"
s = Join(v, "\")
MsgBox s
End Sub

--
Gary''s Student - gsnu201001


"Babymech" wrote:

Basically what I have is a button that creates a new column that should be
full of linked values. When the user hits the button, he is asked to select a
file from his hard-drive; the code then takes the name of that file and
creates a number of links in the new column. So for example:

1) User hits button and selects the closed excel file
C:\Tempfiles\Testsheet.xls
2) Excel creates the new column and populates it with formulas that link to
the file; in A1 it enters
='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters
='C:\Tempfiles\[Testsheet.xls]Budget'!$A$4
And so forth...

I've come so far as to let the user select the file, by using the code:

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xlsx; *.xls), *.xls", _
MultiSelect:=False, Title:="File to open")

This creates a string in VBA with the path of the file I want to open, for
example C:\Tempfiles\Testsheet.xls. The problem is that if I want to create a
new link, I need to add brackets around the filename, as far as I can tell,
to separate it from the path, ie I need to change
C:\Tempfiles\Testsheet.xls into
='C:\Tempfiles\[Testsheet.xls]
Any idea how I could do this?

Thanks,
Babymech