Save files in a folder with new file names using vba

[agohir]

Hello I have a an excel file with column "A" with old file name and Column "B" with new file names. Column "A" gives me full filename plus the extension e.g. "Sales.xlsm" but Column "B" only give me filename i.e. "Sales"

I used the following code to replace old file names with new. It worked but the files are not saved in Excel format i.e. they are saved as just files.

Sub RenameFiles()
For R = 1 To Range("A2").End(xlDown).Row
OldFileName = Cells(R, 1).Value
NewFileName = Cells(R, 2).Value
On Error Resume Next
If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
On Error GoTo 0
Next
End Sub

How do I save the new file names in Excel format?

Thanks,
Arslan

 

[Joe4]

If column A has file extensions, but column B doesn't, then just amend your variable, i.e.

[COLOR=#333333]NewFileName = Cells(R, 2) & ".xlsm"[/COLOR]

 

[agohir]

If column A has file extensions, but column B doesn't, then just amend your variable, i.e.

[COLOR=#333333]NewFileName = Cells(R, 2) & ".xlsm"[/COLOR]

Thanks Joe.


Cheers

 

[agohir]

If column A has file extensions, but column B doesn't, then just amend your variable, i.e.

[COLOR=#333333]NewFileName = Cells(R, 2) & ".xlsm"[/COLOR]

Actually, I have to do it every month. Is there a code I can include where it automatically includes month name and year. For example new file name "Sales_July_2016.xlsm". I was thinking of adding a dialog box that may ask us month and year to include.

Thanks for the help!

 

[Joe4]

If you wanted it to always use the current month and year, you could do:

NewFileName = Cells(R, 2) & Format(Date,"mmmm_yyyy") & ".xlsm"

Otherwise, you could use an InputBox to ask the user to enter it in, and then use that value in the formula.

 

[Joe4]

The Input Box method would look something like this:

    myNamePart = InputBox("Enter the date prefix you wish to use in your file name")

    NewFileName = Cells(R, 2) & myNamePart & ".xlsm"

 

[agohir]

If you wanted it to always use the current month and year, you could do:

NewFileName = Cells(R, 2) & Format(Date,"mmmm_yyyy") & ".xlsm"

Otherwise, you could use an InputBox to ask the user to enter it in, and then use that value in the formula.

Hi Joe,

Thanks again for the help, I want to use the Inputbox function but it seems the Inputbox appears for every file in the folder that I have to change. Can I loop so I just enter month and year in Inputbox once only?

I wrote the following code:
Sub RenameFiles()
Sheets("Filenames").Select
For R = 1 To Range("A2").End(xlDown).Row
OldFileName = Cells(R, 1).Value
NewFileName = Cells(R, 2) & InputBox("Enter Month & Year", "Month & Year") & ".xlsm"
On Error Resume Next
If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
On Error GoTo 0
Next
End Sub

Thanks again!

 

[Joe4]

If you use a variable to store your input like I did in my previous reply, and put that line BEFORE your loop, you will only be prompted once.
Within your loop, build your NewFileName like I showed in the previous post.

 

[agohir]

If you use a variable to store your input like I did in my previous reply, and put that line BEFORE your loop, you will only be prompted once.
Within your loop, build your NewFileName like I showed in the previous post.

Hello Joe,

Can you please help me with the loop here. I tried but excel goes into "not responding".

I have this code as described in your previous post. How do I build the loop so the input box appears only once.

Thanks!!

Sub RenameFiles()
Dim myNamePart As String
Sheets("Filenames").Select
For R = 1 To Range("A2").End(xlDown).Row
OldFileName = Cells(R, 1).Value
myNamePart = InputBox("Enter the date prefix you wish to use in your file name")

Do Until myNamePart = ""

NewFileName = Cells(R, 2) & strt & ".xlsm"

Loop

On Error Resume Next
If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
On Error GoTo 0
Next
End Sub

 

[Joe4]

OK. It is important to understand what loops are and how they work.
You have a FOR...NEXT loop. Anything that you put in between the FOR and the NEXT are going to happen once for every single cell you loop through.
If you only want something to appear once, you can put it BEFORE the FOR line. You only need to ask the input question once, so you can move that before the FOR statement.

So your code may look something like this:

Sub RenameFiles()

    Dim OldFileName As String
    Dim NewFileName As String
    Dim R as Long
    Dim myNamePart As String

    myNamePart = InputBox("Enter Month & Year", "Month & Year")
    
    Sheets("Filenames").Select
    For R = 1 To Range("A2").End(xlDown).Row
        OldFileName = Cells(R, 1).Value
        NewFileName = Cells(R, 2) & ImyNamePart & ".xlsm"
        On Error Resume Next
        If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
        On Error GoTo 0
    Next
    
End Sub