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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
213
gjvv
G
S
  • Question Question
Script to loop thru a number of folders and pulling particular cells into a master sheet
Replies
3
Views
450
santa12345
S
mmn1000
  • Question Question
List folders and files
Replies
1
Views
777
Fuji
F
G
  • Question Question
Print all PDFs using VBA
Replies
2
Views
327
spasmex97
S
S
  • Question Question
Adding a new field to the results of my "list the folders inside a specific folder" code
Replies
2
Views
295
John_w
J

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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