Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Using Excel Vba To Rename Files In Directory

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

is it possible to have a spreadsheet with two columns, Col A showing a list of current file names in a particular directory, and Col B the names I want these files to be renamed to. Is there some code that I can use to do this, or do I have to rename these files one by one until I get old?

Thanks,

Samantha

View Answers     

Similar Excel Tutorials

Rename a Module for an Excel Macro
This Excel tip shows you how to rename a module in Excel. This is a very important thing to do when you have a larg ...
Change the Name of a Chart in Excel
How to change the name of a chart in Excel. This allows you to use a more intuitive chart reference when organizin ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Prevent Saving a Workbook under a Different File Name
- This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only
Sort Worksheet Tabs - Ascending or Descending Order
- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This
Print Preview Screen Display for The Entire Workbook in Excel
- This free Excel macro allows you to quickly and easily display the print preview window or mode for the entire Excel wor

Similar Topics







I have about 50 files in a directory that are all in Excel and need to be renamed. The file names have nothing in common and I need to rename them, in no particular order, Project1, Project2, Project3, etc. for as many files as are in the directory (will vary). I have no idea how to do this. Please help!

Thank you,

Christina


Hey Guys,

Though this is my First post in the Forum, I have been costantly following up many threads for a while...........And need ur help now

I have a directory where users name 2 files (one with .jpg ext and other with .png extension)with same names...................

I need to identify files (.jpg and .png) with same names and rename all as sequential names.............

Assume there are files Test1.jpg, Test1.png and Test 2.jpg and Tet2.png..................After running the Macro..........test1 should be replaced as 1..............that is 1.jpg and 1.png...............And Test2 as 2................That is 2.jpg and 2.png...............Only the names should be renamed seq and there should be no change in their file extensions................

Can someone please help me on the same.....................


Suppose I have a directory with file names 1.jpg, 2.jpg, 3.jpg etc. And I would like to rename it to a.jpg, b.jpg, c.jpg and store the files in a different directory, is it possible by using excel macros wherin I have Column A having 1.jpg, 2.jpg etc... and column B having a.jpg, b.jpg etc... Inshort the file should be renamed with the corresponding filename on Column B and stored in a seperate directory. Is it possible by using macros?


Hi all,

I have navigated to a folder in DOS in order to copy and paste file names into Excel in order to rename appropriatley before archiving.

When I paste into Excel I can only retrieve the last 296 files. Is there a limit on how much data I can copy using this method?

I'm using instructions I found on the internet (See below) but if anyone can suggest a better way of doing this I would be extremly grateful.

Quote:

Step 1: Type cmd and switch (using cd command) to the directory that contains the files you want to rename in bulk.

Step 2: Type dir /b to see a skeleton list of all files in the current directory. Copy that file list to the clipboard by selecting Mark from the contextual menu.

Step 3: The fun starts now. Fire your copy of Microsoft Excel or Google Docs Spreadsheets and paste those file names into the first spreadsheet column.

Step 4: In the blank adjacent column, add a corresponding Excel function for instance, use SUBSTITUTE to change specific text in the filenames, use CONCATENATE() with DATE() if you want to add date to the filename, etc.

*For more complex criteria, you may want to put the file extensions in a separate column by splitting the file name using period (.) as the delimiter.

Step 5: Your source filenames are now in column A while the Destination files are in column B we will now convert this into a DOS rename command.
In Column C, type the following Excel formula and your command is ready to be executed.
=CONCATENATE(ren ,A1, , B1)

Copy paste the same formula across all cells in column C for which you have corresponding values in Column A or B.
We are almost done. Copy all the values from column C to the clipboard and paste them inside the DOS window or better still, put them inside a new text file and give it a name like rename.bat.
Execute and all files that match the criteria are renamed instantly




Hi All,

I want to come up a macro which will rename any of the files .i.e. file with different extensions present in a directory on the basis of the list present in a excel sheet. For e.g. I have old filename in Column A of sheet1 and in Column B I have new file names. The macro should rename the files with the names present in Column B.

Here is the code which I have so far.

Code to list the files present in a directory. This
Code:

Sub ListDirectoryFiles ()
Dim myextn As String
myextn = InputBox("Enter the file Extension", "Enter Extension")
If myextn = "" Then Exit Sub
Const ListDir = "C:\Datafiles\"
If Not Dir(ListDir & "*." & myextn) = "" Then
Flist = Dir(ListDir & "*." & myextn)
R = 1
Do Until Flist = ""
Cells(R, 1).Value = Flist
R = R + 1
Flist = Dir
Loop
End If
End Sub


Code to rename files :
Code:

Sub RenameFiles()
Dim OldFileName As String
Dim NewFileName As String

For i = 1 To Range("A1").End(xlDown).Row
OldFileName = Cells(R, 1).Value
NewFileName = Cells(R, 2).Value
If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
Next
End Sub


The Code to list directory files works fine but the code to rename files doesn't work.

Thanks a lot for your help in advance.




Hello.

I've run into a small problem with my file renaming worksheet

[Attached is a smaller example version]Items - Test.xlsm

What my worksheet does is list files from into column E from directory shown in C4.

From there I choose what type of file it is to be renamed in column C.

Problem is, if in the list of files found there are two files that are similar (e.g. two driving licence files). My renaming file gives me an error 'File already exists' then ends. I'd like it to stick a (2) or (3)... at the end if there's more than one

Note1: Changing C3 will auto run list filenames macro
Note2: Change C4 to any directory you want (you may ignore sheet2)
Note3: If anyone's got a better way to achieve what I'm trying to do, feel free to suggest

I need to write a macro so that it will pull in a directory of all the files in a folder (.tiff files) to Column A and then be able to rename those files using Column B. If this possible please reply here and I would sincerely appreciate it.


Good Morning,

I have a VBA need to count the number of .csv files in a directory specified in a cell (say A1) as text in a current workbook. Can anybody please help?

I would like to setup an array to capture all of the names of the .csv files in the directory. I have a routine to manipulate the data and save to another location with the same .csv file names above.

I am very new to VBA for Excel. The more descriptive the variable names the more dummy proof it is for me.

Thanks.


I have several thousand .pdf files that I have to rename and wanted to use excel and VBA to do this. I have to current name in column A and the new name in column B. I have been trying to use the code in the following post but it cannot find any files to rename.

http://www.mrexcel.com/board2/viewto...ghlight=rename

can anyone help with this code or figure out why it cannot find any files. This is my code:

Sub Find()

Dim i As Integer

With Application.FileSearch
.LookIn = "C:\Test"
.Filename = "Test*.*"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

Thanks




Hello.

I'm wanting to create a spreadsheet that will rename a few files appropriately.
Attached is an example of the end result I'd like to achieve.
Sheet 1 contains a list of names (this is just the exact name of the folders)
Sheet 2 is my converter sheet which will hopefully rename the files
Sheet 3 is a table of contents, for example is a particular file was not included in the converter, label as NO


What I have is over 200 folders that have the names of employees like so: SURNAME Firstname

In each of these folders are a variety of scans, mostly in pdf format. None of these files are named appropriately, and differ for each folder (e.g. first aid scan might be named first aid.pdf, first aid cert.pdf, fa.pdf).

What I'd like is to be able to do (preferably) is drag and drop a file, passport scan to D8, driving licence to D10 etc. [the red text] and have a macro to rename each of these files (all files source folder and destination are the same).

And then enter onto sheet3 what was entered. For example some may be missing files, so if no file was set there to be rename, enter NO (or FALSE). If the file was there, enter YES (or TRUE)


Is this possible within excel?

I have a directory on my hard drive full of mp3 files. I would like to rename each file to a new name that I specify on an Excel worksheet. Previously, my code was working fine as follows:

Sub RenameFiles()
'Used to quickly rename mp3 files in a folder to a list of song names on an Excel sheet

Dim myCell As Range
Set myCell = Sheets("MySongs").Range("B1") 'specify starting location of cells containing correct song names

Set fs = Application.FileSearch 'use search function to find files
With fs
.NewSearch
.LookIn = "C:\Music\Album1" 'path where mp3 files you want to rename reside
.FileName = "*.mp3" 'look only for mp3 files

If .Execute > 0 Then
'If .Execute > 0 Then
For i = 1 To .FoundFiles.Count 'operate only on the number of mp3 files found
Dim gs, gsFile
Set gs = CreateObject("Scripting.FileSystemObject") 'provides access to computer's file system
Set gsFile = gs.GetFile(.FoundFiles(i))
gsFile.Name = myCell.Value & ".mp3"
Set myCell = myCell.Offset(1, 0)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

For some reason, this code does not work on Excel 2003 SP2, nor Excel 2007. Any ideas?


I've seen this before but can't seem to locate it.

I would like to pull files from a directory into column A and then from column B I will rename those files and then I would like to rename the files from what is in column B.

Here is the code which I use to capture the file names in column A.

Code:

Sub ListFilesNames()
    Dim strTemp As String, MyPath As String, myFile As String
    Dim intCount As Integer, f()

    intCount = 0
    MyPath = ActiveWorkbook.Path & Application.PathSeparator
    myFile = "*.*"
    strTemp = Dir(MyPath & myFile)

    Do While strTemp  Empty
        intCount = intCount + 1
        ReDim Preserve f(1 To intCount)
        f(intCount) = strTemp
        strTemp = Dir
    Loop

    With Sheets("Sheet1")
        .Range("A:A").Clear
        .Range("A1").Resize(UBound(f), 1).Value = WorksheetFunction.Transpose(f)
    End With
End Sub





I am using excel and I wish to rename all the txt files that are created to .doc files. I presume the way to go is:

Set fso = CreateObject("Scripting.FileSystemObject")

and

fso.Name

but I wish to loop through the directory first, lets say C:\test drive and rename all the files with .txt. Is there a better way?. Any opinions would be a help.


I'd like to rename files by prefixing a data to the file name for all files in a directory
e.g.

file_t1 renamed as 20100401_filet_t1 etc
where convention is yyyymmdd
I know its only figuring out the data format in VB,. but I am just so new to VB stuff and damn lazy. So please do your best
Thx
Sam


I have several files in one folder. C:/data/
These files are named as date_xxxx.wk1. The date format is YYYYMMDD.

Everyday I need to rename them as date_cellvalueA1.xls where the date format is DDMMYY. Cellvalue A1 is taken from each of the respective workbook.

As I need to rename them on a daily basis. Can I use 2 field, one to determine the directory path and one to determine what file name to search for.

If the files contain the YYYYMMDD, it will be rename to DDMMYY_cellvalueA1.xls


Hi,

I have a list of 500 file names , each 12 digit without extension ( can be either .doc or .pdf) in a excel sheet. There is a directory that contains around 15000 files also containing these 500 files.

I want to search by these file names in the directory and copy these files to another location if found. (There can be two files by the same name one .doc and another .pdf. ). I would want to copy them both in that case !

Can anybody please help me with this ?


Thanks in advance !!

Aashish


Hi,

I would like rename multiple xls files to a set of new names.

My files are in the folder "C:\TEST\"

Pls find the below link to see the file names i would like to change from and to

http://www.box.net/shared/bbudit224x

Column A : the existing file names
Column B : the new file names


Thank you all


I have several files in one folder. C:/data/
These files are named as date_xxxx.wk1. where xxx are random and the date format is YYYYMMDD.

Everyday I need to rename them as date_cellvalueA1.xls where the date format is DDMMYY. Cellvalue A1 is taken from each of the respective workbook.

As I need to rename them on a daily basis. Can I use 2 field, one to determine the directory path and one to determine what file name to search for.

If the files contain the YYYYMMDD, it will be rename to DDMMYY_cellvalueA1.xls


Hi All,

I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.

Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly.

NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.

I don't know if the above can be done - but I welcome your suggestions and example files if you can.

Cheers


Hello Experts,
I have a requirement to rename around 20000 files.
Original file name is in column A and the new file name to be renamed is stored in column B in an excel spreadsheet.
How do I write a macro to achieve this.
I would also need to track file names which were not renamed because the file mentioned in column A does not exist.

Please advise.
Thanks for your help in advance.


I am trying to use the following Access VB code to rename all the files from *.aqi in a directory to *.csv:

Code:

Sub test()
Name "C:\myfolder\*.aqi" As "C:\myfolder\*.csv"
End Sub


The problem is that VBA does not accept wild cards (at least as given here).

Any suggestions?

Thanks

abe


Hi,

I've about 300 webpages

example:
webpage1,htm
webpage2.htm
webpage3,htm
webpage4.htm

etc all the way up to 300.

I need to rename each one with a new name. I've all the names in a spreadsheet / notepad file.

Can excel rename and save the new files?

Long shot i know but i can't rename 300 files it would kill me and i'll have 100's more over the next few weeks.

Any help would be great

Thank you.


Hi,

I've about 300 webpages

example:
webpage1,htm
webpage2.htm
webpage3,htm
webpage4.htm

etc all the way up to 300.

I need to rename each one with a new name. I've all the names in a spreadsheet / notepad file.

Can excel rename and save the new files?

Long shot i know but i can't rename 300 files it would kill me and i'll have 100's more over the next few weeks.

Any help would be great

Thank you.


Hi,

I am trying to write VBA code to rename files (word doc) in a particular folder.
Please help me with the code to rename files in the folder by referring a column in the excel.

For example: Column A has a list of names aaa, bbb, ccc, ddd in A1, A2, A3, A4 etc.... I need a code to rename the existing files which are saved already in a different name with the new name listed in A1, A2, A3, A4 etc....

I would appreciate if you could give me a better solution ASAP!!
Thanks in advance


Dear All,

This time I have the following challenge:
In a particular directory I place via a programme csv files.
Now I would like VBA to proces all csv files in that directory for concatenation, that means from eg. 5 csv files, I make one csv file.
The files I have processed should be deleted from that directory.
Who can help me on this one pls???
THX in advance.......and Merry X-mas