Resave all files in folders and their sub folders

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
267
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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:

Watch MrExcel Video

Forum statistics

Threads
1,095,223
Messages
5,443,183
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top