CODEWITHSUNDEEP
vbaexcel
Anton Bychek
Anton Bychek

Reputation: 115

Rename file where it was extracted

I've got a VBA macro that renames a file. I have the full path where this file exist but I want to rename this file independent from its location.

Private Sub Workbook_Open()
    Dim sFileName As String, sNewFileName As String

    sFileName = "C:\Users\me\Desktop\text.txt"    
    sNewFileName = "C:\Users\me\Desktop\test1.txt"    
    If Dir(sFileName, 16) = "" Then MsgBox "File not found", vbCritical, "Error": Exit Sub

    Name sFileName As sNewFileName 'rename file

    MsgBox "file has been renamed"
End Sub

I mean if you extract the archive with this Excel file and text.txt file and start it, it will find test.txt and rename it independent from its location.

Upvotes: 2

Views: 174

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You are looking for ThisWorkbook.Path which gives you the path of the workbook you are using. So if your txt file is in the same directory you can use something like this:

sFileName = ThisWorkbook.Path & "\text.txt"  
sNewFileName = ThisWorkbook.Path & "\test1.txt"

Upvotes: 2

user11909
user11909

Reputation: 1265

When the excel file location is the same as the text file location, you don't have to write the direction path.

Just write the filename without its direction path:

sFileName = "test.txt"    
sNewFileName = "test1.txt"

Upvotes: 0

Related Questions