Rename a file in excel using variable values

anshikam

Board Regular
Joined
Aug 20, 2016
Messages
87
Hello,

I need to rename a file.
However both filenames are stored in variables.
Name of the original file will change and can be anything. However I need to rename it to a specific file name to run my code.
There will only be 1 file in the folder at a time.
I also need to make a copy of the file and save in Archive folder before renaming it.
Current Code listed below not working. Any suggestions?

Sub DeleteLeft()

Dim OldName As String
Dim NewName As String
Dim GivenLocation As String
OldName = Dir("F:\Corporate\Anshika\Due Dates\left\*.xls")
MsgBox OldName
NewName = "Left.xls"
MsgBox NewName
Name OldName As NewName

End Sub
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,731
Hi anshikam,

See how this goes:

VBA Code:
'https://www.mrexcel.com/board/threads/rename-a-file-in-excel-using-variable-values.1133444/
Option Explicit
Sub Macro1()

    Dim strSourcePath As String, strArchivePath As String
    Dim strFile As String, strNewName As String
    
    Application.ScreenUpdating = False
    
    strSourcePath = "F:\Corporate\Anshika\Due Dates\left\" 'Source directory for the file. Change to suit if necessary.
    strArchivePath = "F:\Corporate\Anshika\" 'Archive directory for the file. Change to suit if necessary.
    strNewName = "Left.xls"
    
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    If Right(strArchivePath, 1) <> "\" Then strArchivePath = strArchivePath & "\"
    
    strFile = Dir(strSourcePath & "*.xls")
    'The following loops through all files in the 'strSourcePath' directory.
    'As there's only one file said to be in the folder there's no need to validate various file names.
    Do Until Len(strFile) = 0
        FileCopy strSourcePath & strFile, strArchivePath & strFile
        Name strSourcePath & strFile As strSourcePath & strNewName
        strFile = Dir
    Loop
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 

anshikam

Board Regular
Joined
Aug 20, 2016
Messages
87
Hi anshikam,

See how this goes:

VBA Code:
'https://www.mrexcel.com/board/threads/rename-a-file-in-excel-using-variable-values.1133444/
Option Explicit
Sub Macro1()

    Dim strSourcePath As String, strArchivePath As String
    Dim strFile As String, strNewName As String
   
    Application.ScreenUpdating = False
   
    strSourcePath = "F:\Corporate\Anshika\Due Dates\left\" 'Source directory for the file. Change to suit if necessary.
    strArchivePath = "F:\Corporate\Anshika\" 'Archive directory for the file. Change to suit if necessary.
    strNewName = "Left.xls"
   
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    If Right(strArchivePath, 1) <> "\" Then strArchivePath = strArchivePath & "\"
   
    strFile = Dir(strSourcePath & "*.xls")
    'The following loops through all files in the 'strSourcePath' directory.
    'As there's only one file said to be in the folder there's no need to validate various file names.
    Do Until Len(strFile) = 0
        FileCopy strSourcePath & strFile, strArchivePath & strFile
        Name strSourcePath & strFile As strSourcePath & strNewName
        strFile = Dir
    Loop
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
This helped. I modified it a bit though but thanks for sharing.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,972
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top