Limiting filesearch return string 2

[funkmonsteruk]

I'm using the application.filesearch method to fill a combobox with details of the contents of a folder.

However, currently this method returns the full file path of each file, I would like it only to show the name of the file.

eg current detail in combobox would be F:\Folder 1\Folder2\Folder 3\Filename.xls

However I would like it to show Filename.xls

Any ideas

 

[RobBroekhuis]

if variable s contains the full filename, use

mid(s,instrrev(s,"\&quot+1)

to return just the filename without the path.
Rob

 

[LoNeRaVeR]

RobBroekhuis:

That is much easier than looping from the end of the path string looking for the "\" as I have been doing for years. I knew about the InStr function, but I was not aware of the InStrRev function. That is extremely helpful and worthy of the first

that I have awarded!

So would you use the following to obtain the path, or do you have other tricks up your sleeve?

  sFile = Mid(s, InStrRev(s, "\") + 1)
  sPath = Left(s, Len(s) - Len(sFile))

Thanks, LoNeRaVeR

 

[rmikesmith]

Rob,

That's cool! but I can't find InstrRev in either the Object Browser or searching Help. Is this available only in newer versions of Office? I'm using 97 at home.


Regards,
Mike

 

[xlbo]

Certainly can't find it in xl97 Rgds
~Geoff~

 

[RobBroekhuis]

Mike - I'm not sure: I've always programmed in XL2000.

LoneRaver,
You don't really need the intermediate variable:

sPath = Left(s, InstrRev(s,"\&quot-1)

(that takes the final "\" off the path - if you need to keep it on, just leave the "-1" off)
Rob

 

[LoNeRaVeR]

RobBroekhuis:

No wonder I didn't know about it. I'm still used to programming in XL97.

funkmonsteruk:

Here is the code that I used in XL97

For iChar = Len(sPath) To 1 Step -1
    If Mid(sPath, iChar, 1) = "\" Then
        sFile = Mid(sPath, iChar + 1)
        Exit For
    End If
Next iChar

Assign the Path\File to the variable sPath and the above code will assign the filename to the variable sFile.

Hope That Helps, LoNeRaVeR

 

[funkmonsteruk]

Cheers for your help Rob - still a bit stuck - this is the code i'm currently using

Private Sub UserForm_Initialize()
Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "F:\Process Support\Taps Bal"
.SearchSubFolders = False
.FileName = "*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles(i)
ComboBox1.AddItem .FoundFiles(i)
Next i
End If
End With
End Sub

Where would your code fit in - or do you recommend a different approach

 

[RobBroekhuis]

Replace your additem code with:

ComboBox1.AddItem mid(.Foundfiles(i),instrrev(.Foundfiles(i),"\&quot+1)

As we discovered yesterday, you need to be running XL2000 for the InstrRev function to work.
Rob

 

[funkmonsteruk]

What about if i'm using 97?

 

[RobBroekhuis]

You could create your own InstrRev function:

Public Function InstrRev(s1 As String, s2 As String)
Dim p1 As Integer, p2 As Integer
p2 = InStr(s1, s2)
Do
p1 = p2
p2 = InStr(p1 + 1, s1, s2)
Loop Until p2 = 0
InstrRev = p1
End Function

Then just use the same code I proposed above.

Rob

 

[funkmonsteruk]

Fantastic Rob, once again you are a lifesaver