Variable File Name

[Trevor]

We are using -

FileOpenName = Application.GetOpenFilename("Text Files
(*.txt),*.txt", 1, "Open IFCAP Text File", , False)

to prompt the user for the file to be opened. Later in our
macro we want to switch from another workbook to this
workbook, but can't figure out how to get the workbook
name as a variable from the FileOpenName variable.

The FileOpenName could be "C:\FOLDER1\FOLDER2\FNAME.xls."
It could have multiple folders and the filename can vary.
We can not see how to get the FNAME.xls extracted. We
looked at Instr but couldn't see how to use it to get what
we want.

Any ideas?

 

[Ron de Bruin]

You can use the Split97 function for this if it must work in Excel 97-2003
See the example I use in this Zip sub to get the file name
http://www.rondebruin.nl/zip.htm

See the VBA help for Split if you use 2000 or higher

 

[Guest]

What about:
newString = Mid(yourString, InStrRev(yourString, "\", , vbTextCompare) + 1)

This will get everything after the last "\" or everything in the string, if no backslash is present.

-Dan

 

[Tom Ogilvy]

Sure you can (in excel 2000 and later).

Another way is

Public Function GetName(sFname As String)
Dim i As Long, sFname1 As String, s As String
Dim j as Long
If InStr(sFname, "\") Then
sFname1 = ""
i = Len(sFname)
j = 0
Do
s = Mid(sFname, i, 1)
If s <> "\" Then
j = j + 1
End If
i = i - 1
Loop Until s = "\"
sFname1 = Right(sFname, j)
Else
sFname1 = sFname
End If
GetName = sFname1
End Function

This is slower, but should work in all versions.