Macro to rename files

[gmagana2]

Hi Everyone,

Here is one for ya...I would like to create a macro that renames a series of ".out" files in a batch and replace the old files with the renamed files. I am new to programming in general and know very little about VB, can someone PLEASE guide me.

Thanks

 

[Alluvian]

vba actually has an easy function for this, called 'name'. The code below shows how to use it:

oldfilename = "c:\mytemp\oldtext.txt"
newfilename = "c:\mytemp\newtext.txt"
Name oldfilename As newfilename

I found this by typing "vba rename file" into google.

 

[Alluvian]

If your old filenames are in an excel spreadsheet in column A, and your new filenames are in column B (include the directories), then the following code should rename them all:

The code will end when any cell in column A or column B is blank. It will give an error if either value is not a valid file path.

Sub RenameFiles()
r = 0
Do
    r = r + 1
    oldfilename = Cells(r, 1)
    newfilename = Cells(r, 2)
    If oldfilename = "" Or newfilename = "" Then Exit Do
    Name oldfilename As newfilename
Loop
End Sub

 

[gmagana2]

Hi Alluvian,

Thanks for the tip,however one thing I would like to do is bring up a prompt to ask the user to select the file that he/she would like to rename. So if the person has saved a file called "Fluid" I would like to have the macro rename the file "Fluid" as "Fluid_Out", so have a predesignated name for the file that is being renamed.. I kinda have an idea, I have attached the code to this page. I think this code should go embedded or integrated with the "Rename File FUnction". Let me know what you guys think please. Thanks a million

Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

'Set up list of file filters
Filt = "All Files (*.*),*.*"

'Display *.* by default
FilterIndex = 1

'Set the dialog box caption
Title = "Select a file to rename and import"
'Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

'Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

'Display full path and name of the file
MsgBox "You selected" & FileName
End Sub

 

[gmagana2]

I got the last script from a book titled Excel 2003, I did not create that script..

 

[ravishankar]

Hi
Add the following codes to your current codes

Dim newname as string
Newname = Left(FileName, InStrRev(FileName, ".") - 1) & "_out.xls"
Name FileName As Newname
MsgBox "You selected" & FileName & Chr(10) & "it is renamed as " & Newname

ravi

 

[gmagana2]

HI Ravishankar,

Thank for the help it work fine.

 

[suricato]

Hello everyone,

I have a similar problem, i have tried the code mentioned above from gmagana2, with the changes suggested from ravishankar and it works.

I need your help to modify the code in order to:

- choose a folder instead of a file
- find all the files in the chosen folder with a name that starts with "sz" and end with "_d"
- rename the files by replacing the first 5 numbers that follow sz with 5 different numbers that the user can choose e.g. through a user form.

So i can imagine the user to open the excel file, click on a form thats connected to a macro. The macro will call a userform and ask the user to choose the folder and type in a textbox the 5 new numbers.
I need the code that will be pasted behind the "OK" button in the userform.

Is this possible with vba?
Thanks a lot for any kind of help!

 

[camle]

I have something similar: if you don't mind looking at this?

How do you in Excel <acronym title="visual basic for applications">VBA</acronym> Rename a file from looking into a sheet and
finding a match in a cell.

Ex.
My filename is:
Guy's Grocery Games -2014-07-04- Grocery Grillin'.ts

I have a sheet named "Guy's Grocery Games" in it is:
cell A1= 2x03
cell B1= Grocery Grillin'

Take this info and come up with a new file name and rename the file:
Guy's Grocery Games 2x03 Grocery Grillin'.ts

File names will vary, depending on the show that is being recorded.
Ex.
Yard Crashers -2014-07-04- Backyard Secret Garden
Diners, Drive-Ins and Dives -2014-07-04- Open 24_7
Yard Crashers -2014-07-04- Country French Backyard

and so on. Each show has it's own sheet.

 

[promax]

I tried to use the code originally given by Alluvian. I have mentioned the old and the new file names in column A and B of the excel sheet containing this macro, however I am getting runtime error 53:file not found message.

the debugger says that error is in the line: Name oldfilename As newfilename

the excel sheet header row has been named as oldfilename and newfilename respectively.

will be grateful for guidance on whats happening here.