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

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

You can parse the string looking for the last backslash and build the string
that way.

But if you allow the user to just select the workbook/filename, how are you
gonna be sure that there is a worksheet with the name your code wants to use:

Option Explicit
Sub testme()

Dim FileToOpen As Variant
Dim LastSepPos As Long
Dim myStr As String
Dim WksName As String
Dim Addr As String

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

If FileToOpen = False Then
Exit Sub
End If

LastSepPos = InStrRev(FileToOpen, "\")

WksName = "sheet1"
Addr = "$a$1"

'='C:\My Documents\Excel\[hi there.xls]Sheet1'!$A$1
myStr = "='" & Left(FileToOpen, LastSepPos) _
& "[" & Mid(FileToOpen, LastSepPos + 1) & "]" _
& WksName & "'!" & Addr

ActiveCell.Formula = myStr

End Sub

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

--

Dave Peterson