VBA file renamer. Please help!

[timemoves]

I've been running into a wall. I am trying to make a file renamer and have fallen flat on my face. I would like to remove the first 12 characters of a file (always the same) then delete everything after the part I want to keep" -" (always different after. It would be amazing if it utilized application.getopenfilename so I can use it in different folders and multiple files at the same time. Example of what I'm attempting below. Please keep in mind keep will always be different as will be everything after - .

Original:
123456789 - keep - 5654eg -5444_3453333 - vffgvxdrf6.csv

What I'm trying to rename to
Keep.csv

 

[JoeMo]

Assuming the word you want to keep is always between the first and second hyphens in the file path and name and the file extension is always at the end, maybe this will get you started:

Sub FileRenamer()
Dim fName As String, newName As String
fName = Application.GetOpenFilename
newName = Trim(Mid(WorksheetFunction.Substitute(fName, "-", WorksheetFunction.Rept(" ", 100)), 100, 100)) & Mid(fName, InStrRev(fName, "."))
MsgBox newName
'your code to open file named  fName then saveas newName using a folder of your choice
End Sub

 

[Joe4]

Here is a little User Defined Function that should do what you want:

Function GetFileName(str As String) As String
    Dim temp As String
    temp = Mid(str, 13)
    GetFileName = Trim(Left(temp, InStr(temp, "-") - 1)) & ".csv"
End Function

Then you can call it from the worksheet or in VBA, like any other Excel function, like this:

Sub Test()
    MsgBox GetFileName("123456789 - keep - 5654eg -5444_3453333 - vffgvxdrf6.csv")
End Sub

 

[timemoves]

Assuming the word you want to keep is always between the first and second hyphens in the file path and name and the file extension is always at the end, maybe this will get you started:

Sub FileRenamer()
Dim fName As String, newName As String
fName = Application.GetOpenFilename
newName = Trim(Mid(WorksheetFunction.Substitute(fName, "-", WorksheetFunction.Rept(" ", 100)), 100, 100)) & Mid(fName, InStrRev(fName, "."))
MsgBox newName
'your code to open file named  fName then saveas newName using a folder of your choice
End Sub

It is always between the 2nd and 3rd hyphens. Your code works perfect otherwise. Any idea on how to do that? I really like your solution.

 

[JoeMo]

It is always between the 2nd and 3rd hyphens. Your code works perfect otherwise. Any idea on how to do that? I really like your solution.

That's not consistent with the singular example you provided in your OP and your expected result is: keep.csv

In any case, this will return whatever is between the 2nd and 3rd hyphens:

Sub FileRenamer()
Dim fName As String, newName As String
fName = Application.GetOpenFilename
newName = Trim(Mid(WorksheetFunction.Substitute(fName, "-", WorksheetFunction.Rept(" ", 100)), 200, 100)) & Mid(fName, InStrRev(fName, "."))
MsgBox newName
'your code to open file named  fName then saveas newName using a folder of your choice
End Sub

 

[timemoves]

That's not consistent with the singular example you provided in your OP and your expected result is: keep.csv

In any case, this will return whatever is between the 2nd and 3rd hyphens:

Sub FileRenamer()
Dim fName As String, newName As String
fName = Application.GetOpenFilename
newName = Trim(Mid(WorksheetFunction.Substitute(fName, "-", WorksheetFunction.Rept(" ", 100)), 200, 100)) & Mid(fName, InStrRev(fName, "."))
MsgBox newName
'your code to open file named  fName then saveas newName using a folder of your choice
End Sub

PERFECT!
I know, for whatever reason I had it in my head that removing the first x digits then everything after the - was the best solution. I should have given a better example. I had to change it to 280 for whatever reason. Other than that it renames the files saving me a bunch of time. Now I'm going to play with it and try to get it to rename multiple files instead of one at a time.

Again, thanks!

 

[JoeMo]

PERFECT!
I know, for whatever reason I had it in my head that removing the first x digits then everything after the - was the best solution. I should have given a better example. I had to change it to 280 for whatever reason. Other than that it renames the files saving me a bunch of time. Now I'm going to play with it and try to get it to rename multiple files instead of one at a time.

Again, thanks!

You are welcome - thanks for the reply.