InStrRev

[mAP3d]

Hi guys,

I am working on excel 2010 and have a file path from which I need to separate the file name. Since the files could always be in different places, I wanted to search for the last "\" with InStrRev. Then I wanted to subtract this value from the length (Len) of the string and use this value with "Right()" to cut of only the file name. My problem is, that InStrRev just doesn't show a value.

Right(strListe, Len(strListe) - InStrRev(strListe, "\"))

The file path is stored in "strListe", its only InStrRev which doesn't do his job.

Someone got an idea?

btw it worked with InStr but I can not use this value.

 

[JackDanIce]

Found these two solutions when I searched the internet for "vba file name from file path"

- This one uses InStrRev: vba - Extract filename from path - Stack Overflow
- This one uses Right: vba - How to extract file name from path? - Stack Overflow

 

[tiredofit]

Instrev does work but takes a lot to figure out exactly what it's doing.

Take a look here:

http://www.mrexcel.com/forum/excel-questions/770071-instrrev.html

 

[mAP3d]

thx for the quick replys guys. did u notice, that I tried exactly what "- This one uses InStrRev: vba - Extract filename from path - Stack Overflow" they suggest? the only difference is, that I get the file name through an open box, which I execute through a button and then I store the path in "strListe".
The thing is: if I put a "stop point" (don't know how its called in English, sry) and check the values (mouse over) stored in the different variables (eg Right(), Len(), InStrRev()), it shows me everything except the one from "InStrRev".

but I will go on and check your links, just wanted to give another insight on my situation

 

[Peter_SSs]

Does this work for you?

fName = Mid(strListe, InStrRev(strListe, "\") + 1)

Edit: If not, please give us the code you are using to get strListe

 

[mAP3d]

InStrRev still has no value stored in it :/ guess I am making a stupid mistake. I am using "msoFileDialogFilePicker" to open an explorer window, where the user is able to select the file he needs, since there are no conventions for the file names.

Code:
*Dim strListe As String
Dim strDatei As String
Dim strPart As String

'iNR = UsedRange.Columns.Count

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strListe = strListe & vbCr & _
vrtSelectedItem
Next vrtSelectedItem
End If
End With

If strListe = "" Then
MsgBox "Es wurde kein Eintrag ausgewählt!"
Else
MsgBox "Gefunden!" & vbCr & strListe*

 

[Peter_SSs]

InStrRev still has no value stored in it ,,

1. InStrRev is not a variable, it is a function so it does not 'store' a value, it 'returns' one when appropriate arguments are fed into it.

2. You haven't used InStrRev in your code??


This works for me, but would only return one file name even if you chose multiples in the file dialog. Would need to know more about just what you are trying to achieve.
Please indent any posted code - it makes it much easier to read, understand & debug.

Dim strListe As String
Dim strDatei As String
Dim strPart As String
Dim strFilename As String

'iNR = UsedRange.Columns.Count

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
  If .Show = -1 Then
    For Each vrtSelectedItem In .SelectedItems
      strListe = strListe & vbCr & _
      vrtSelectedItem
    Next vrtSelectedItem
  End If
End With

If strListe = "" Then
  MsgBox "Es wurde kein Eintrag ausgewählt!"
Else
  MsgBox "Gefunden!" & vbCr & Mid(strListe, InStrRev(strListe, "\") + 1)
End If
End Sub

 

[mAP3d]

first of all thanks for your time and advice peter!

you are right, I wanted to say that InStrRev doesn't return a value, while every other operator(?) like right and len DOES return one.

in my code, I had the InStrRev as well in the "Else" part of the 2nd If tree.

my goal is to fill a common excel table with values from the files, I select with the "msoFileDialogFilePicker", and store the name of that file in the last column.

the problem is that the files are not in a specified place with a certain name, therefor I need the explorer window so that the user is able to select the file he needs at that moment.

hope this gives u a better view

 

[mAP3d]

ok actually with your last post I was able to fill the last field with only the file name.

now if I want to copy certain cells from the "selected" file, can I refer to it in the same step and fill my cells?

edit: ...fill my cells, like this, for example:

Sheets("2015-16").Cells(ActiveCell.Row, 1).Value = Workbooks(strListe).Worksheets("BANF").Range("D6").Value

?

 

[Peter_SSs]

OK, so what I think you are trying to do is choose one or more files and for each file put the file name in one column of '2015-16' and the value from cell D6 of sheet 'BANF' in another column (A).

I don't know why you are using ActiveCell in your code but it is rare to need to actually select cells to work with them in code and selecting slows your code.
So you might like to test the following code in a copy of your workbook.
It should look in '2015-16' in whatever column your filenames are in (set in the 'Const' line near the top of the code) for the last row used and then in subsequent row(s), place the file name in that column and extracted D6 values in column A (also set with a 'Const' line in the code).

There is no check in the code to test if the selected file(s) actually have a worksheet called 'BANF'.

Anyway, see if it helps lead you in the right direction.

Sub Test()
  Dim pos As Long
  Dim nr As Long
  Dim f As Variant
  Dim fd As Variant
  Dim fName As String

  Const DataCol As String = "A"       '<- Column to put the extracted BANF D6 value
  Const FileNameCol As String = "D"   '<- Adjust to suit or have code work it out

  Set fd = Application.FileDialog(msoFileDialogFilePicker)

  With fd
    If .Show = -1 Then
      For Each f In .SelectedItems
        pos = InStrRev(f, "\")
        fName = Mid(f, pos + 1)
        f = "='" & Left(f, pos) & "[" & fName & "]BANF'!D6"
        With Sheets("2015-16")
          nr = .Cells(.Rows.Count, FileNameCol).End(xlUp).Row + 1
          With .Cells(nr, DataCol)
            .Formula = f
            .Value = .Value
          End With
          .Cells(nr, FileNameCol).Value = fName
        End With
        i = i + 1
      Next f
    End If
  End With
End Sub