CODEWITHSUNDEEP
excelvba
Rhyfelwr
Rhyfelwr

Reputation: 329

Trying to rename a file in a folder (based on a string within the filename)

Hi there I am trying to create a Sub where I find a file in a folder with the name containing a specific phrase like "test_file" and rename it with a different name/extension. I have the following code that doesn't seem to work: (Also I don't know how to search for a specific string within a filename and execute the renaming according to that)

Sub ReName()

Dim myFile As String
Dim myPath As String
Dim mVal As String

mVal = "_12345678_" 'test string'

myPath = "C:\Users\bf91955\Desktop\testfolder\" 'folder path'
myFile = Dir(pathname & "*test_file*") 'real file name is 2222_test_test_file.xlsx'
myFile = myPath & myFile

NewName = "new_test_file & mVal & .xlsx" 'save it as .xlsx and with the new filename including myVal'

Name myFile As myPath & NewName 'rename

End Sub

Any help would be appreciated!

Upvotes: 1

Views: 2294

Answers (1)

Tom
Tom

Reputation: 9898

Have a look at the comments you had a couple of errors

Sub ReName()
    Dim myFile As String
    Dim myPath As String
    Dim mVal As String

    mVal = "_12345678_" 'test string'

    myPath = "C:\Users\bf91955\Desktop\testfolder\" 'folder path'
    '' You weren't referencing MyPath here - guessing copy and paste error
    myFile = Dir(myPath & "*test_file*") 'real file name is 2222_test_test_file.xlsx'
    myFile = myPath & myFile

    '' This was passing the variable as part of the string
    newname = "new_test_file" & mVal & ".xlsx" 'save it as .xlsx and with the new filename including myVal'

    Name myFile As myPath & newname 'rename

End Sub

Upvotes: 1

Related Questions