Finding the last in a string (97)

Topic

New Reply

Sorry if this is a repeated common question, lack of Search etc.

I have a file path set as a string for example:

c:Documents and settingsdesktopjezzareport.csv

I am just trying to get the file name report.csv. What is the code to hunt through the original string to find the last so that I can just get the value report.csv. I seem to remeber it uses the right, left and len functions and have seen it in the forum before.

Reply | Quote

Replies

If the string is in A1 this will give the file name:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1))

Steve

Reply | Quote

Hi Steve

Thanks for your speedy reply. I have this running in VBA

……

FileToOpen = Application.GetOpenFilename(“CSV Files (*.csv), *.csv”, , ” Source File”) ‘ This finds the file to be opened

Workbooks.Open FileToOpen

………

Obviously FileToOpen is the file path

The VBA doesn’t like the substitute function… am I doing something wrong?

Jerry

Reply | Quote

Dim FileToOpen As String
Dim FileName As String
Dim i As Integer

FileToOpen = Application.GetOpenFileName(…)
For i = Len(FileToOpen) To 1 Step -1
If Mid(FileToOpen, i, 1) = “” Then
Exit For
End If
Next i

If i = 0 Then
MsgBox “No found in path!”
Else
MsgBox Mid(FileToOpen, i+1)
End If

Reply | Quote

Hi Hans

I had one of the 11.30pm inspirations last night and went tapping at the laptop. I got a solution not as elegant as yours but I thought I would share it with the forum, just to show I am trying:

……..

While Left(FileName,1) “”

x=x+1

FileName=Right(FileToOpen,x)

Wend

FileName = Right (FileToOpen, x-1)
FileLength = Len (FileName)
NewFileName = Left (FileName, FileLength-4)

…… etc

I think I was nearly there, it just seems you have used Step/Next

Reply | Quote

Hi Jerry,

There is no fundamental reason to prefer a For / Next loop over a While loop (or vice versa). Both will work fine.

Reply | Quote

InStrRev() finds a string within another one starting from the end, so you could use:

iPos = InStrRev(FileToOpen, "")
strPathName =  Left(FileToOpen, iPos - 1)
strFileName = Right(FileToOpen, Len(FileToOpen) - iPos)

Have fun!
Ian.

Reply | Quote

I can’t seem to find this function in the VB I have. InStr and InStrB but no InStrRev

Steve

Reply | Quote

Sorry – I use Excel 2000 now, but I thought I remembered InStrRev being in earlier versions.

Memory going with advancing age, I’m afraid!

Ian.

Reply | Quote

I believe that InStrRev was introduced with Office 2K

Peter

Reply | Quote

Darn it, Hans beat me to it, but I’ll offer one useless comment: in my pathetic code, I usually keep the path name and file name as separate strings; it makes them easier to handle, and you can easily concatenate them.

Reply | Quote

You got your original question answered. VB is more clear than the megaformula.

Once you OPEN the file, you can get the path and name directly using:
sPath = activeworkbook.path
sFilename = activeworkbook.name

FYI,
To use substitute (and other “non-VB” functions preface them with:
“Application.worksheetfunction.” eg:

Application.worksheetfunction.SUBSTITUTE(text,old_text,new_text,instance_num)

Steve

Reply | Quote

Slightly easier and more efficient than Hans’ code:

Dim vPath As Variant
Dim strFName As String
    vPath = Application.GetOpenFilename
    If Not vPath = False Then
        strFName = Dir(vPath)
        MsgBox "File name is " & strFName
    End If

Reply | Quote