Resave all files in folders and their sub folders

yinkajewole

Board Regular
Joined
Nov 23, 2018
Messages
209
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?
 

Moses99

New Member
Joined
Jul 2, 2019
Messages
11
' 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]
 

Moses99

New Member
Joined
Jul 2, 2019
Messages
11
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
 

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
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:
A
B
1
Old File Names
New File Names
2
Ana Trujillo.jpgAna Trujillo_RENAMED.jpg
3
antagonist.txtantagonist_RENAMED.txt
4
Antonio Moreno.jpgAntonio Moreno_RENAMED.jpg
5
ayo-ogunseinde-2.jpgayo-ogunseinde-2_RENAMED.jpg
6
Book1.xlsxBook1_RENAMED.xlsx
7
Book2.xlsmBook2_RENAMED.xlsm
8
Book2.xlsxBook2_RENAMED.xlsx
9
bootstrap.min.cssbootstrap_RENAMED.min_RENAMED.css
10
Browse_To_Website.xlsmBrowse_To_Website_RENAMED.xlsm
11
Cartesian Product.xlsmCartesian Product_RENAMED.xlsm
12
Christina Berglund.jpgChristina Berglund_RENAMED.jpg
13
clem-onojeghuo-1.jpgclem-onojeghuo-1_RENAMED.jpg
14
clem-onojeghuo-2.jpgclem-onojeghuo-2_RENAMED.jpg
15
clem-onojeghuo-3.jpgclem-onojeghuo-3_RENAMED.jpg
16
content.garnett.csscontent_RENAMED.garnett_RENAMED.css
17
customers.txtcustomers_RENAMED.txt
18
Elizabeth Lincoln.jpgElizabeth Lincoln_RENAMED.jpg
19
erik-lucatero-2.jpgerik-lucatero-2_RENAMED.jpg
20
Frédérique Citeaux.jpgFrédérique Citeaux_RENAMED.jpg
21
grab.curgrab_RENAMED.cur
22
grabbing.curgrabbing_RENAMED.cur
23
Hanna Moos.jpgHanna Moos_RENAMED.jpg
24
joe-gardner-1.jpgjoe-gardner-1_RENAMED.jpg
25
joe-gardner-2.jpgjoe-gardner-2_RENAMED.jpg
26
kaci-baum-1.jpgkaci-baum-1_RENAMED.jpg
27
kaci-baum-2.jpgkaci-baum-2_RENAMED.jpg
28
Laurence Lebihan.jpgLaurence Lebihan_RENAMED.jpg
29
Maria Anders.jpgMaria Anders_RENAMED.jpg
30
Martín Sommer.jpgMartín Sommer_RENAMED.jpg
31
modx-revo-icon-48.svgmodx-revo-icon-48_RENAMED.svg
32
prism.cssprism_RENAMED.css
33
style1.cssstyle1_RENAMED.css
34
textfile.htmltextfile_RENAMED.html
35
Thomas Hardy.jpgThomas Hardy_RENAMED.jpg

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

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:

Forum statistics

Threads
1,078,365
Messages
5,339,765
Members
399,322
Latest member
jpalleyne

Some videos you may like

This Week's Hot Topics

Top