Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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.


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


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.




Hello,

I have a question regarding moving files from one directory to another. I have an excel spreadsheet with a list of file names in Column A. This macro runs through the columns and checks against a directory to see if the file exists, and moves into a destination directory. It runs fine for now, but I was wondering if there was a way to retain the directory structure and create the subfolders within the destination folder.



Please Login or Register  to view this content.


Any help is much appreciated!

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 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


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


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


I'm having a tough time trying to figure out the code and was hoping there are people on this forum who have experience with this.

What I'm trying to do is first remove all files in a directory.
Then I want to copy new source files into the directory.
Then I want to rename those files to a generic name.

One of the main problems I'm having is that I need to use wildcards as my source filenames keep changing and I won't know the exact name each month that I want to run this.

Here's the code I have so far...

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

strPath1 = "C:\User\" 'Source file location
strPath2 = "C:\User\Backup\0907\" 'Backup file location
strPath3 = "C:\User\Production" 'End user file location

'Delete all files in the target directory - GIVES ERROR
Kill (strPath2 & "\*.xls")

'Copy all of the source files - THIS WORKS FINE
fso.CopyFile (strPath1 & "\*.xls), strPath2

'Rename certain files - DON'T KNOW IF THIS IS EVEN CORRECT CODE
'IS THERE A WAY TO OVERWRITE?
Name (strPath2 & "\*file1*.xls") As strPath2 & "File1.xls"


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 regularly receive a bunch of database generated report files in a directory. Every report is named similarly using a text name telling the generic type with a date/time stamp afterwards. For instance one series might be "Production Report 20110713141955". Every report in that series will be "Production Report " so no two are named exactly the same.

Each separate file is a report for a different department. I have not been able to get the database folks to include the department name in the report title so I need to manually rename all of them. They are always in the same sequence, by that I mean the first date-timed report is always Department A17, the second date-time is always Department A18, etc. The date and time stamps are of course always different so I cannot use a fixed renaming macro.

Is there a way using VBA to rename all files in a directory by going in sequence through the files in the directory, and renaming those against a list in excel until all are done?

Thanks!
matt


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.


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 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