VBA macro to open folder and rename files numerically

[chin67326]

Hi folks, I am trying to get a VBA macro which I will assign to a button which will do the following when the button is clicked:

1. Open a window for me to browse and navigate and open a specific folder
2. Rename all the files in the folder in numerical order starting from 1, 2, 3 and so on.

As a note, the files in the folder will already be in alphabetical order however I want the macro to loop through the folder and rename the alphabetical system to a numerical system.

Thanks!

[daboho]

Not test but you can copy your file to new folder to keep your file from damage
Or with this code you can copy folder and open workbook and save as numeric

Please Login or Register  to view this content.

[chin67326]

Thanks daboho but this did not work for me. I was only able to get the Dialog folder picker to open. Also, and most importantly, in my folders there are no Excel files. I believe you think that I want to rename Excel files from your code, but I don't have Excel files. The folders have different files like .txt text files, bitmap, Word and video files. So it would be nice if your code can be written to accommodate and rename any file types as long as they are in alphabetical format. Thanks!

[daboho]

Thanks daboho but this did not work for me. I was only able to get the Dialog folder picker to open. Also, and most importantly, in my folders there are no Excel files. I believe you think that I want to rename Excel files from your code, but I don't have Excel files. The folders have different files like .txt text files, bitmap, Word and video files. So it would be nice if your code can be written to accommodate and rename any file types as long as they are in alphabetical format. Thanks!

Ok try again

Please Login or Register  to view this content.

[chin67326]

Thanks again but I tried the code and nothing happened. The file names remained unchanged.

[daboho]

Thanks again but I tried the code and nothing happened. The file names remained unchanged.

Try again if not work tomorrow will be test
Using copyfile

Please Login or Register  to view this content.

[chin67326]

Thanks. It did not work and I got the below error:

"Run-time error 76:

Path not found"

Also when I ran the below line I got a "Compile error: Invalid qualifier" error

If (fso1.exists(newname.Name)) = True Then fso1.deletefile (v.Name)

If you are able to test it with an actual folder tomorrow then that would be appreciated. Maybe you can create a folder and add multiple files likle text files, music and videos etc.

[bakerman2]

Try this on a testfolder.

Please Login or Register  to view this content.

[Leith Ross]

Hello chin67326,

Here is another method using the Shell...

Please Login or Register  to view this content.

[YasserKhalil]

@Leith Ross
Thanks a lot for your great solutions

If the windows settings are set to hide the extensions of the file names, the code will not assign the extension in the correct approach
So I suggest instead of using .Name in the line

Please Login or Register  to view this content.

To use .Path instead

Thank you very much

[Leith Ross]

Hello Yassir,

As usual my friend, you are correct. I discovered that sometime ago but never wrote anything to determine if the file extensions were hidden, since I show them on my system.

However, this macro will enable file extensions to be shown.

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Leith
The code that you have posted and it is supposed to enable file extensions doesn't work for me. I tried to change 1 to 0 and 0 to 1 and the same ..

[Leith Ross]

Hello Yassir,

According to Microsoft this should work for any NT disk system. If you are accessing your files from a network or the web then it probably won't work and should throw an error. If you are receiving any errors then add a break point to the macro at RegWrite and step through the code. If it makes to that point then it should be enabling/disabling the file extensions.

[YasserKhalil]

That's my bad. It is working but I had to refresh the explorer ..
Thanks a lot for the gift my tutor

[Leith Ross]

Hello Yassir,

You're welcome. Glad you got it sorted out.

[daboho]

hello chin67326 work for me
only small change search file.name by non numeric

Please Login or Register  to view this content.

[chin67326]

Hi all this is the original poster here. I have not had success therefore this thread has not been solved for me. Thank you all for your input. I will list the outcome I had with each attempt:

bakerman2's input:

When I tried the code, I received the below error:

bakerman2.png


Leith's input (also used/considered Yasser's advice):

When I tried the code, I received the below error:

leith.png

Yasser, how did you get the code to work for you? I also refreshed explorer but the same error was present.

dabaho's input:

This approach has been slightly successful in achieving what I am looking for. However, for file names that has a period "." as part of the file name (e.g. "C - renovation list.part1", "D - renovation list.part2", when the code is run, the file changes from a Word file to an unreadable file [i.e. "3.part1", "4.part2"] with a white icon that looks like a sheet of paper and right clicking and selecting Properties reveals that the file opens with "Windows Shell Common Dll". Can you please tweak the code and test it so the files are renamed, even though there may be a period sign in the file name, and at the same time keep the original file type? See the before and after results which I have achieved below when I tried your code:

dabaho.png


Thanks all!

[bakerman2]

I renamed some files like your before image and this was my outcome.
After.PNG
So what exactly do want to be your outcome.

[chin67326]

Thanks bakerman2! I want the outcome just like you achieved but I am looking for a code that can accommodate when Windows has the "hide known file extensions" turned on. Therefore I will obtain the same result as per your above without the .pdf, .xlsm, .xlsx etc. displayed. Therefore, I will obtain a result which just has the file named as 1, 2, 3, 4, 5 and 6 as per your example. I need to have "hide known file extensions" turned on and I believe that is leading to the error I am getting when I try your code. Can you do a code tweak that can accommodate systems that have the "hide known file extensions" turned on? Thanks again.

[daboho]

i has test mycode if hide exstension and turn of file exstension
and this work For me

Please Login or Register  to view this content.

[mohan.r1980]

Hi chin67326,

Please try below code

Please Login or Register  to view this content.

[chin67326]

i has test mycode if hide exstension and turn of file exstension
and this work For me

Please Login or Register  to view this content.

This worked for me too. You have solved what I have been trying to achieve. Thank you! You have been repped.

[daboho]

This worked for me too. You have solved what I have been trying to achieve. Thank you! You have been repped.

Thank for feedback