Using Excel Vba To Rename Files In Directory

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


Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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 ...
Combine Worksheets from Multiple Workbooks into One
Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined in ...
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 ...

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 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 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 Excel Team,

I need help from ur End, I need tht How to rename the Filenames in an Folder using macro.. Is it possible, How we can do it..

Example:
In an Folder 200files are there with extenxion .jpg/xls/bmp/txt/doc files, I want to rename tht those files names,

Example1:
Source File: TestImage.jpg
I want rename as
Destination file : image.jpg.


Not only Single file rename..if i wnt to rename Bulk means?...

Is there any coding is there, Can any one share it, its More helpful.........

Waiting For ur rply..

Regards,
Karthick


Hi I need code to rename all word doc files in a folder. I require to remove the first 10characters from the file name for the new filename.
Can anyone help me on this. (I am currently doing it via the command prompt to list all files and then generating the new filename through excel and putting the new list of names back into the command prompt to rename file.) I'm guessing this could be done more neatly through VBA code.Thanks in advance.


Hi,

I am looking for a macro to rename the pdf files in a folder.

Say I have 100 pdf files in a folder 1-100, I need to rename the file to the list I have in excel. For example file name 1 should be renamed to "invoice 1" which I have it in excel spreadsheet. Similarly file name "2" should be renamed to "Invoice 2".

Please help!

Regards,
Vijay


I have a bunch of files that I make every day. Then I copy them to an archive folder and re-name the ones that are still in my current folder.

For instance, tomorrow I will copy "Deposits 070909" and "Open Balance 070909" to my archive folder and, in the current folder, rename them "Deposits 071009" and "Open Balance 071009." There are also a couple of Excel files in my current folder that do not end in dates, and those I use every day without copying or renaming.

Can someone help me to get this to happen automatically?

I figure I could rename each file with something like this:

Code:

newname=substitute(oldname,mid(oldname,find(".",oldname)-6,6),format(date,"mmddyy"))


...but I don't know how to loop through the files or how to change the names while they're closed - or how to determine if they end in a date.

Thanks for helping me

Tai


I need to look at all files in a specific directory and see which of the files have a "DateLastModified" value which is equal to the current date. I then need to set a flag of some sort to let me know which files met the criteria so I know which files to look at. I know how to get the last modified date, but need help building the loop and setting the flag. I basically want to set a Y/N or T/F flag using a variable based on the file name, minus the extension. All of the files in the directory will be Excel files.


I have an excel spread sheet with two columns. The first is a long list of current file names of a large number of image files in a single folder. The second column represents the new name we would like each image file to have. We are Mac OS. I am not trained in Visual Basic, but I am trying to create a Macro to do the following. I need Excel to start with cell A1 and locate that named image file in a specific folder on my hard drive. I then need excel to rename the file with the name with cell B1 and then proceed to cell A2 and repeat.

Any guidance appreciated,

Kent


Hi,
I have a large number of .txt files that get deposited in a directory. I'd like some code in Excel that will cycle through all the .txt files, open them and then close them in .xlsx format. (without any prompts, etc).
I know it's relatively simple but not sure how to cycle through files.

It's not a show-stopper, but would be good to point the code at a root folder which then has folders at the next level with the files in.
eg.
:\root folder\dir1\file1.txt
:\root folder\dir2\file2.txt
etc. where there can be any number or naming convention of folders under :\root folder


Hello,

I have linked 10 excel workbooks to one master workbook so that different individuals can open and edit the 10 workbooks, automatically updating the master workbook. I have saved these 10 workbooks and master workbook as blank templates with no data entered.

I would like to be able to make a copy of these templates for each fiscal year. However, when I copy and rename the templates, the links continue to connect to the original templates instead of automatically connecting to the renamed files.

I could manually change the links to have the files' new names; however, because the master workbook contains so many links in so many different places, it would take many many hours to do so.

I have saved the 10 workbooks and master workbook in a folder and when I copy and rename the entire folder, the links are maintained. But, I cannot rename any of the files within the new folder, which means that I cannot simultaneously open files from different fiscal years (and this also causes confusion as multiple files have the same name and can only be differentiated by the folders they're in).

I am wondering if there is perhaps a macro I could write that would automatically correct my links when I rename the template files for each fiscal year. However, I am not familiar with VBA so I am having trouble writing such a code.

Thank you so much for your time and any advice. Please let me know if you need more information.

Hi All,

I have the below macro which loops through all files in a directory and then unzips the zip files. I have another macro to download some file from different urls actually a userform which has listbox with all links listed in it and the names of the files to be named post download from those links. I want to identify the zip files and post download unzip and rename them as per the name reflecting in the lisbox and save in the same directory. Actually all those files contain the excel files which I want to rename as per the names reflecting in listbox.

Code:

Sub RUNZIPPER()

'Run before you leave and keep excel running in the background
Application.OnTime TimeValue("19:00:00"), "UnZipMe"


End Sub

Sub UnZipMe()

Dim str_FILENAME As String, str_DIRECTORY As String, str_DESTINATION As String

'Your directory where zip file is kept
str_DIRECTORY = "C:\Users\Graeme\Documents\Alex TEST\"

'Loop through all zip files in a given directory
str_FILENAME = Dir(str_DIRECTORY & "*.zip")

Do While Len(str_FILENAME) > 0
    Call Unzip1(str_DIRECTORY & str_FILENAME)
    Debug.Print str_FILENAME
    str_FILENAME = Dir
Loop

End Sub

Sub Unzip1(str_FILENAME As String)
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String

    'Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=False)
    Fname = str_FILENAME
                                        
                                        
    If Fname = False Then
        'Do nothing
    Else
        'Root folder for the new folder.
        'You can also use DefPath = "C:\Users\Ron\test\"
        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If

        'Create the folder name
        strDate = Format(Now, " dd-mm-yy h-mm-ss")
        FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"

        'Make the normal folder in DefPath
        MkDir FileNameFolder

        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.Application")

        oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

        'If you want to extract only one file you can use this:
        'oApp.Namespace(FileNameFolder).CopyHere _
         'oApp.Namespace(Fname).items.Item("test.txt")

        'MsgBox "You find the files he  " & FileNameFolder
        Debug.Print "You find the files he  " & FileNameFolder

        On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
    End If
End Sub


I have attached my macro file for you reference.

Thanks a lot for your help in advance.


Hi All,

I have the below macro which loops through all files in a directory and then unzips the zip files. I have another macro to download some file from different urls actually a userform which has listbox with all links listed in it and the names of the files to be named post download from those links. I want to identify the zip files and post download unzip and rename them as per the name reflecting in the lisbox and save in the same directory. Actually all those files contain the excel files which I want to rename as per the names reflecting in listbox.

Code:

Sub RUNZIPPER()

'Run before you leave and keep excel running in the background
Application.OnTime TimeValue("19:00:00"), "UnZipMe"


End Sub

Sub UnZipMe()

Dim str_FILENAME As String, str_DIRECTORY As String, str_DESTINATION As String

'Your directory where zip file is kept
str_DIRECTORY = "C:\Users\Graeme\Documents\Alex TEST\"

'Loop through all zip files in a given directory
str_FILENAME = Dir(str_DIRECTORY & "*.zip")

Do While Len(str_FILENAME) > 0
    Call Unzip1(str_DIRECTORY & str_FILENAME)
    Debug.Print str_FILENAME
    str_FILENAME = Dir
Loop

End Sub

Sub Unzip1(str_FILENAME As String)
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String

    'Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=False)
    Fname = str_FILENAME
                                        
                                        
    If Fname = False Then
        'Do nothing
    Else
        'Root folder for the new folder.
        'You can also use DefPath = "C:\Users\Ron\test\"
        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1)  "\" Then
            DefPath = DefPath & "\"
        End If

        'Create the folder name
        strDate = Format(Now, " dd-mm-yy h-mm-ss")
        FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"

        'Make the normal folder in DefPath
        MkDir FileNameFolder

        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.Application")

        oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

        'If you want to extract only one file you can use this:
        'oApp.Namespace(FileNameFolder).CopyHere _
         'oApp.Namespace(Fname).items.Item("test.txt")

        'MsgBox "You find the files he  " & FileNameFolder
        Debug.Print "You find the files he  " & FileNameFolder

        On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
    End If
End Sub


Please find my macro file on this link : http://www.4shared.com/document/_S41..._ver_4_2_.html

Thanks a lot for your help in advance.


Hi,

I searched the newsgroup and found some code that gives me hyperlinks
to files in a directory. In addition to linking to files in the
directory, is it possible to link to files in all of the sub-folders of
the directory?

For instance, if I have a folder C:\Steve\ with several files and some
sub folders (C:\Steve\Folder1\ and C:\Steve\Folder2\ etc.) each with
several files, I want to be able to run a macro that would make links
to all the files just by inputting the first directory C:\Steve\. Is
this possible?

Ideally, I would like a new worksheet for each folder having the same
name as that folder, but this is more of a bonus. The part above is
what I really need.

Here is the code I have so far (thanks to Bill Manville):

Sub HyperlinksToDirectory()
' puts hyperlinks to each of the files in a directory of your choice
' into the active sheet starting at the active cell
Dim stDir As String
Dim stFile As String
Dim R As Range
Set R = ActiveCell
stDir = InputBox("Directory?", , Default:=CurDir())
stFile = Dir(stDir & "\*.*")
Do Until stFile = ""
R.Hyperlinks.Add R, stDir & "\" & stFile, , , stFile
Set R = R.Offset(1)
stFile = Dir()
Loop
End Sub

Thanks,
Steve Mackay




I use J-Walk menu makr extensively (though not frequently)
I now have two add-ins showing up under tools/add-ins selector box
that have an "incorrect" name, although the add-in itself is exactly
what I want.
The file is named as I want
The name of the .xla properties is as I want.
Somehow, sometime, I must have entered something incorrectly and
now the addin selection box names of these two files does not reflect
what they are actually for.
Now, after all that, is there a way to rename what shows up in the
selector box? or do I have to create those two add-ins over from scratch?






Team
I am using MS 2010 and please tell me know how to rename multiple (more than 100) PDF files which in a folder as per the list in an excel spreadsheet. for example, I have contract agreement paper in PDF format for 100 employees and I have their employee ID numbers, last name, first name in a excel spreadsheet. I have to rename all the pdf files with the employee number_a commen name as "Contract Agreement" (123456_Contract Agreement)

I'm trying to create a file listing macro to display the following information contain under a specified directory (which also would include any sub-directories):

Col A: Complete Directory Path
Col B: Filename
Col C: File Extension
Col D: File Size
Col E: Creation Date/Time Stamp
Col F: Last Modified Date/Time Stamp

I'm trying to create a file listing to help rename photos files. (I already have the renaming macro created, so that piece is unnecessary - unless you wish to provide it for other viewers.) I want to rename these files so that they have the Creation Date/Time Stamp in the filename (ex: "DSC012937-20110707-112745.jpg" ---> "Original Filename-Date-Time.OriginalExtension"). This will also allow me to identify where I may have duplicate pictures since the Date/Time stamp will be equal.

Also, any additional available photo ot mp3 related fields (i.e. Artist, Album, Track, etc.) you can provide will allow me to create a multi-use macro for renaming files.

Any assiatance is greatly appreciated. Thanks to all that took the time to view this posting.


Hi there-

Is there a quick way to rename all instances of a directory "09-08" in a specified folder?

I need to rename all of the folders named "09-08" to "08-09" within any subdirectory of a specified folder....

Any help appreciated in advance!


Hi All,
I have below macro which creates a new directory on a network drive and then copies some files to the same directory. The directory name is as different every day as it also has date in it's name. I am able to create a new directory every day using the below macro but it doesn't allow me to copy the files from a different directory to the new created directory. Please help.
Code:

Sub RunDownloadTool()
Dim filePath As String
Dim foldername As String
filePath = "\\OCS\Datadump\Daily\"
foldername = ThisWorkbook.Worksheets("Sheet1").Range("B1").Value
MkDir (filePath & foldername)
FileCopy "\\OCS\Datadump\Daily\REGO\AML.bat", "\\OCS\Datadump\Daily\" & foldername & "\" & AML.bat
End Sub


The Above macro gets stuck on the FileCopy Line.
Thanks a lot for your help in advance.


I need some code that will reside in a Master file where data ranges in each file within a directory needs to be copied to.

Simply stated, the loop code will open every file in a single directory and, one at a time, will copy/paste to the Master.

I can handle the code to do the copying/pasting. What I need is the loop code to go through all of the Excel files in the directory and stop when the process has been performed on the last file.

Been searching the web, but am getting errors when I try to run the code that I've found so far.

I'm running Excel 2007, FYI. Thanks in advance!


In Access, am needing to unzip all .zip files located in a particular folder (30+ files), without having winzip installed. After unzipping them, will need to delete some of the files that will contain a specific word in their file names, but that is round 2. First, need to unzip them. Been trying to modify code found here, http://www.rondebruin.nl/windowsxpzip.htm, but no luck yet from within Access, getting compile error on GetOpenFielname, and not sure if I'm missing a library reference or if Access doesn't use that. Any tips would be appreciated, thank you.



I have created a macro that creates/saves a text file. The macro will
run on various people's computers and need to create/save the file in
directory that the macro file is located in. By default Excel saves
the file in the directory Excel has been told to put files in.

How do I cause the macro to save the file to the directory the
spreadsheet containing the macro is in?

jim





I am looking for a VBA program that deletes the specific columns of hundreds of CSV files in a directory. So the first row contains the headers. In the table below the columns "You" and "Too" should be removed completely from the csv file.

While I know this can be done slowly by opening a file and then selecting the columns then deleting it takes a long time. I need a way to automate this so that I can have the column deleted completely based on the column header name.

Also, all the csv files do not have same column header names, but there should be a rule where if the column header is supposed to be deleted but doesn't exist in the csv file then move to next header to delete.

The csvs are pretty big too sometimes up to 500mb. So I rather not have to open each one to delete the column by running the macro. If the vba could run through entire directory and delete the specific columns that would be great.

Ex
Why You Bad Too Yes data daea 32 3ad


Hello all,

I have a directory of one sheet workbooks that are all the same template and need to have their sheet renamed from "Sheet1" to whatever the filename is to prepare them for merging into one larger workbook.

I know the code I need to make the change is 'activeSheet.name = activeWorkbook.name' but since I can't use Application.FileSearch anymore I need to know how to run a module that will easily open the files in the directory one at a time, make the name change, save the file and move on to the next one. I don't need to step in at any point as all the files are already saved with the correct name and they're all getting the same change.

Since nothing I've found online so far has worked I'm basically starting from scratch with a new module.

Can someone walk me through the proper way to run the loop to do this?

Thanks/


Hello,

I am trying to find some completed excel VBA code or a macro that allows me to specify a directory and automatically generates a list in Excel of the folders, subfolders and files - i.e. a full directory tree. I don't wish to use the DOS command for a few reasons. Please can someone advise if they know of any such code or if they have something to hand (don't have time to modify unfortunately so looking for something complete)

Kind Regards,
Raheel


Every month I get some Executable Zip files that contains sales datas in Excel files.

I'm trying to find a way to automaticly unzip these files into a specific directory. I'm able to run the EXE file from Excel but not able to specify the destination directory or press ALT-U to unzip it.


Sub Unzip()
Shell pathname:="c:\zip.exe", windowstyle:=shellnormfocus
SendKeys ("C:\UnzippedFiles_directory")
End Sub

Any ideas how I can take control of this Winzip window ???