Resave all files in folders and their sub folders

[yinkajewole]

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]

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

 

[yinkajewole]

is there no code for that?

 

[Moses99]

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​

 

[yinkajewole]

what if I want to apply it to text files?

 

[strooman]

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.jpg	Ana Trujillo_RENAMED.jpg
3​	antagonist.txt	antagonist_RENAMED.txt
4​	Antonio Moreno.jpg	Antonio Moreno_RENAMED.jpg
5​	ayo-ogunseinde-2.jpg	ayo-ogunseinde-2_RENAMED.jpg
6​	Book1.xlsx	Book1_RENAMED.xlsx
7​	Book2.xlsm	Book2_RENAMED.xlsm
8​	Book2.xlsx	Book2_RENAMED.xlsx
9​	bootstrap.min.css	bootstrap_RENAMED.min_RENAMED.css
10​	Browse_To_Website.xlsm	Browse_To_Website_RENAMED.xlsm
11​	Cartesian Product.xlsm	Cartesian Product_RENAMED.xlsm
12​	Christina Berglund.jpg	Christina Berglund_RENAMED.jpg
13​	clem-onojeghuo-1.jpg	clem-onojeghuo-1_RENAMED.jpg
14​	clem-onojeghuo-2.jpg	clem-onojeghuo-2_RENAMED.jpg
15​	clem-onojeghuo-3.jpg	clem-onojeghuo-3_RENAMED.jpg
16​	content.garnett.css	content_RENAMED.garnett_RENAMED.css
17​	customers.txt	customers_RENAMED.txt
18​	Elizabeth Lincoln.jpg	Elizabeth Lincoln_RENAMED.jpg
19​	erik-lucatero-2.jpg	erik-lucatero-2_RENAMED.jpg
20​	Frédérique Citeaux.jpg	Frédérique Citeaux_RENAMED.jpg
21​	grab.cur	grab_RENAMED.cur
22​	grabbing.cur	grabbing_RENAMED.cur
23​	Hanna Moos.jpg	Hanna Moos_RENAMED.jpg
24​	joe-gardner-1.jpg	joe-gardner-1_RENAMED.jpg
25​	joe-gardner-2.jpg	joe-gardner-2_RENAMED.jpg
26​	kaci-baum-1.jpg	kaci-baum-1_RENAMED.jpg
27​	kaci-baum-2.jpg	kaci-baum-2_RENAMED.jpg
28​	Laurence Lebihan.jpg	Laurence Lebihan_RENAMED.jpg
29​	Maria Anders.jpg	Maria Anders_RENAMED.jpg
30​	Martín Sommer.jpg	Martín Sommer_RENAMED.jpg
31​	modx-revo-icon-48.svg	modx-revo-icon-48_RENAMED.svg
32​	prism.css	prism_RENAMED.css
33​	style1.css	style1_RENAMED.css
34​	textfile.html	textfile_RENAMED.html
35​	Thomas Hardy.jpg	Thomas Hardy_RENAMED.jpg

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

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