Resave all files in folders and their sub folders

Y

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
281
Please, what's the fastest code that can resave (perhaps through loop) all the files in a folder and its subfolders and it will maintain the folder and subfolders structure in their destination?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
' Open the excel file
workbooks.Open filename := "exactly the name of your workbook"
'ask to close the workbook
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]workbooks.Close

You have to manually click save or not, then repeat for next excel files.
[/FONT]
 
Upvote 0
this is the code, i have just shown you below

' Open the excel file
workbooks.Open filename := "exactly the name of your workbook"
'ask to close the workbook
workbooks.Close​
 
Upvote 0
Start Windows and in the searchbox in the bottom left hand corner type: cmd also known as the "command prompt". Right click on cmd and run as administrator.

A black box appears with a flickering cursor. This is the command prompt. Go to your folder containing the files you want to rename. My choice was: C:\TEMP. To go to this folder type:

cd \ and hit Enter.

Then type: cd TEMP and hit Enter.

Finally type: dir /b

You see all your file names. Adjust the size of the window to fit your needs. Click the small black box in the upper left corner. A menu appears.

Choose: Edit | Mark. Now select all your files by dragging your cursor and choose: Edit | Copy.

Go to Excel, open a new workbook and select A1 and choose Paste.

Select B1 and choose Paste.

You now have the same file names in column A and column B (see the screenshot below). When you want your file names to be different, change the names in your column B to fit your needs. (I my example I adjusted the file names with _RENAMED)


Finally past the below code in a module. Follow the steps:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Press the keys ALT + F11 to open the Visual Basic Editor
3. Press the keys ALT + I to activate the Insert menu
4. Press M to insert a Standard Module
5. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
6. Press the keys ALT + Q to exit the Editor, and return to Excel
7. To run the macro from Excel, press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name (RenameFiles) to Run it.

My example:
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
1​
[/TD]
[TD="bgcolor: #0070C0"]
Old File Names​
[/TD]
[TD="bgcolor: #0070C0"]
New File Names​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
2​
[/TD]
[TD]Ana Trujillo.jpg[/TD]
[TD]Ana Trujillo_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
3​
[/TD]
[TD]antagonist.txt[/TD]
[TD]antagonist_RENAMED.txt[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
4​
[/TD]
[TD]Antonio Moreno.jpg[/TD]
[TD]Antonio Moreno_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
5​
[/TD]
[TD]ayo-ogunseinde-2.jpg[/TD]
[TD]ayo-ogunseinde-2_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
6​
[/TD]
[TD]Book1.xlsx[/TD]
[TD]Book1_RENAMED.xlsx[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
7​
[/TD]
[TD]Book2.xlsm[/TD]
[TD]Book2_RENAMED.xlsm[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
8​
[/TD]
[TD]Book2.xlsx[/TD]
[TD]Book2_RENAMED.xlsx[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
9​
[/TD]
[TD]bootstrap.min.css[/TD]
[TD]bootstrap_RENAMED.min_RENAMED.css[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
10​
[/TD]
[TD]Browse_To_Website.xlsm[/TD]
[TD]Browse_To_Website_RENAMED.xlsm[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
11​
[/TD]
[TD]Cartesian Product.xlsm[/TD]
[TD]Cartesian Product_RENAMED.xlsm[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
12​
[/TD]
[TD]Christina Berglund.jpg[/TD]
[TD]Christina Berglund_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
13​
[/TD]
[TD]clem-onojeghuo-1.jpg[/TD]
[TD]clem-onojeghuo-1_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
14​
[/TD]
[TD]clem-onojeghuo-2.jpg[/TD]
[TD]clem-onojeghuo-2_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
15​
[/TD]
[TD]clem-onojeghuo-3.jpg[/TD]
[TD]clem-onojeghuo-3_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
16​
[/TD]
[TD]content.garnett.css[/TD]
[TD]content_RENAMED.garnett_RENAMED.css[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
17​
[/TD]
[TD]customers.txt[/TD]
[TD]customers_RENAMED.txt[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
18​
[/TD]
[TD]Elizabeth Lincoln.jpg[/TD]
[TD]Elizabeth Lincoln_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
19​
[/TD]
[TD]erik-lucatero-2.jpg[/TD]
[TD]erik-lucatero-2_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
20​
[/TD]
[TD]Frédérique Citeaux.jpg[/TD]
[TD]Frédérique Citeaux_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
21​
[/TD]
[TD]grab.cur[/TD]
[TD]grab_RENAMED.cur[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
22​
[/TD]
[TD]grabbing.cur[/TD]
[TD]grabbing_RENAMED.cur[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
23​
[/TD]
[TD]Hanna Moos.jpg[/TD]
[TD]Hanna Moos_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
24​
[/TD]
[TD]joe-gardner-1.jpg[/TD]
[TD]joe-gardner-1_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
25​
[/TD]
[TD]joe-gardner-2.jpg[/TD]
[TD]joe-gardner-2_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
26​
[/TD]
[TD]kaci-baum-1.jpg[/TD]
[TD]kaci-baum-1_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
27​
[/TD]
[TD]kaci-baum-2.jpg[/TD]
[TD]kaci-baum-2_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
28​
[/TD]
[TD]Laurence Lebihan.jpg[/TD]
[TD]Laurence Lebihan_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
29​
[/TD]
[TD]Maria Anders.jpg[/TD]
[TD]Maria Anders_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
30​
[/TD]
[TD]Martín Sommer.jpg[/TD]
[TD]Martín Sommer_RENAMED.jpg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
31​
[/TD]
[TD]modx-revo-icon-48.svg[/TD]
[TD]modx-revo-icon-48_RENAMED.svg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
32​
[/TD]
[TD]prism.css[/TD]
[TD]prism_RENAMED.css[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
33​
[/TD]
[TD]style1.css[/TD]
[TD]style1_RENAMED.css[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
34​
[/TD]
[TD]textfile.html[/TD]
[TD]textfile_RENAMED.html[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
35​
[/TD]
[TD]Thomas Hardy.jpg[/TD]
[TD]Thomas Hardy_RENAMED.jpg[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Code: 
Sub RenameFiles()
Dim xDir As String
Dim xFile As String
Dim xRow As Long
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            xDir = .SelectedItems(1)
            xFile = Dir(xDir & Application.PathSeparator & "*")
            Do Until xFile = ""
                xRow = 0
                On Error Resume Next
                xRow = Application.Match(xFile, Range("A:A"), 0)
                If xRow > 0 Then
                    Name xDir & Application.PathSeparator & xFile As _
                    xDir & Application.PathSeparator & Cells(xRow, "B").Value
                End If
                xFile = Dir
            Loop
        End If
    End With
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Permision Denied-VBA Code to list sub-folders
Replies
4
Views
121
howard
H
H
  • Question Question
Macro to List Folders excluding Data Stamp within E:\Cobian
Replies
0
Views
73
howard
H
G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
333
gjvv
G
KlausW
  • Solved
Rename files from old name in column C and new name in column D
Replies
4
Views
195
Domenic
D
S
  • Question Question
Script to loop thru a number of folders and pulling particular cells into a master sheet
Replies
3
Views
488
santa12345
S

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top