Pull string from filename

[diimoo1]

Looking help - a way to pull the string from the filename and perform a task using the string part of the formula manually. Right now I can pull the file from the folder and use the string part of my program

C:\ XXXX_555_GGGGG.xlsx ( extraxt '555")

C:\ XXXX_101_GGGGG.xlsx (extraxt"101")

....cont!

manually we can do this for each of the files:

{ Sub Combined() wb.Sheets("Sheet1").Range("b2").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "555") End Sub

Sub Combined1() wb.Sheets("Sheet1").Range("b3").Value = Application.SumIfs(Sheet1.Columns(2), Sheet1.Columns(1), "101")

End Sub

Looking for a better way to automated this:

Open the folder

pull the string from filename path ( file one) ( i.e '555')

use the string part of my sum formula

next file

Thanks in advance

 

[mohadin]

Try this method

Sub test()
    Dim x
    x = "C:\ XXXX_555_GGGGG.xlsx "
    Debug.Print Split(Split(x, "\")(UBound(Split(x, "\"))), "_")(1)
End Sub

 

[diimoo1]

Thanks Mohadin

 

[mohadin]

You are welcome

 

[Peter_SSs]

I see that you already have a good working solution but FWIW here is an alternative that requires a bit less calculation/manipulation.

Debug.Print Split(Split(x, "\")(UBound(Split(x, "\"))), "_")(1)
Debug.Print Split(Mid(x, InStrRev(x, "\")), "_")(1)