Getting a filename into VBA and putting it into cells

[Babymech]

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

 

[Gary''s Student]

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

 

[Dave Peterson]

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

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