Variable File Name

  • Thread starter Thread starter Trevor
  • Start date Start date
T

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?
 
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
 
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.
 
Back
Top