Excel VBA with FILES function to rename files
HTML-код
- Опубликовано: 5 сен 2024
- In this Video, I show how to use a "Single line" of code in VBA to rename files in a folder. Just a creative use of VBA and the FILES Function
Workbook can be found here:
drive.google.c...
#VBA #RenameFiles #FilesFunction
I searched for a while to find something that worked as intended. This is such a simple method, and so well explained. Thank you!! You just saved me HOURS of work.
Glad it helped! You are welcome
This was an awesome video Victor!!! You have a really good way of explaining things and it made it so easy to understand. Thanks so much! :)
Thanks for your feedback. I really appreciate it
Awesome video! I just saw your presentation in the mydatasummit, and it was terrific as well; thanks for answering my question on error handling. I hope to see more vba videos from you :)
Oh wow. Thanks so much Yesenla. I was not aware who asked the question, I am glad I was able to give a little perspective to this. Hopefully, I can do more VBA videos 😀😀
@@ExcelMoments that would be awesome! I'm subscribed so that I can get notified of all your upcoming videos :)
Thanks Victor! It saved a lot of time :) God bless you!
You are welcome. Glad to know you found it useful
This video helps a lot!! Thank you so much!
this is what I'm looking for.
You are welcome
Thank you so much!
You are very much welcome
Thank you very much❤❤❤
You are welcome
Thank you! Your Video is helpful and solved my issue. But one question - how can we preserve the original files in the original folder? I ran your macro and realized that the files in the original folder are lost.
Thanks for the question. When you say the files are lost, I know you dont mean deleted. All the video showed was how to rename files, the name changes but the content is intact.
So for me, if you want to keep the old names, you may want to create a copy of the folder as is before doing the rename. In any case, if you know what your rename logic is/was, you can always execute another rename to fix that
Thank you a lot ❤❤❤
You're welcome 😊
Thanks. It is really helpful.
You are welcome
@funkymuskcrat73 Just go to your Folder path cell and then click run....it will work..Once you run the VB script you need to enter in the cell where folder path is entered to run the VB Script again...The Runtime error is due to this ..
Can anyone help me , I want to find a particular words filenames and replace them . the data is stored in Excel . How can it be possible . Can anyone share any video or article .
Hello sanjeev, if you could elaborate more, I thunk it's possible or doable
@@ExcelMoments Hi Thanks for your Reply . So the point is that I am having images with these types of names like . ABC789_1 ABC789_2
LIKE ABC789 is main name and sequence can be 1,2,3,4 max 4 sequences
And I have around 200 or 400 Main names with 4 sequences so approx 800 to 1200 images
I have an excel which contains current main names and names to be replaces for example
ABC789_1 needs to be WCA456_1
Sequence will be same only main name should be renamed according to excel . So any trick through I can find and replace the main names according to the excel .
Hello. I hawe an issue. I receive an error when I try to renamd files that contain ș ț î ă â (romanian characters) and. Do you hawe a solution for this?
This worked great for me many times in the past but today it gives me Runtime Error 53. It populates the file list fine but when I run the macro, which is your code exactly, it give an error. When I go into the debugger it highlights this line "Name Range("D" & I).Value As Range("E" & I).Value." The code is pretty straightforward, it renames the files in Column D to Column E as long as the range has values.
As I said, the macro worked fine in the past but will not today. I copy the worksheets for each project so the ones I used to rename previous file lists are intact. They don't run now either. I reduced the folder contents to 1 file to make sure the file name is correct, made no difference. If it has a problem with the single file name then it's beyond me.
Interesting!! What error does it give? Rather curious!! 'cos the code is really simple
@@ExcelMoments Just the Runtime Error 53. I got it to work but I didn't do anything different. Because of the error which means it can't find a file, it apparently had something to do with a file, e.g. bad name or missing, or the path. I was positive the names and paths were right because the paths pointed to existing directories I set up previously for this purpose. One of the first things I did was to copy and paste the path cell contents back into the cells. I did that instead of re-entering the values because as I said, I knew they were correct. Anyway, I did that 2-3 times with no benefit, made a few other attempts at other solutions, wrote me message, tried the same solution again and it worked perfectly. Somehow it worked on the 4th try for no apparent reason. Problem solved, as they say.
thank you for posting this video. I get an error "Run-time error '1004"; Method 'Range' of object '_Global' failed; debug highlights the row "For I =2 To Range ....; for Range if I put a hard value like 2000, the error Method 'Range"..goes away; and next debug is Run-Time error '75' Paht/File access error; Any suggestions on resolving the error; I have used same script but for me column D = Z and Column E=AA
Alright, for the first error, ensure the column you use has data in it, i mean, your column Z if youa re doing something like Range("Z" & rows.count).end(xlup).row
For Error 75, the very likely case is that the expression you have come up with is not an actual file that Excel can locate, one possibility
we have lots of excel file ...then how to convert the excel file name with the specific cell value....
every file have different cell value
Thanks for your question. What you need essentially would be 2 columns. Old name and new name and a single line of VBA code should fix the rest.
So I guess my only question would be, are you able to create a table with 2 columns, Old Filename and New Filename?
Awesome,What exactly does the Name syntax does ?
The Name function renames a file or folder/directoryThe syntax is Name "Oldname" as Newname
@@ExcelMoments thanks.
@@Arsenal_Legend234 You are welcome
How do I find out more about using the # like you did at 1:10? I can't understand what you called the type of range or the hash so I can't find anything about it in Help.
Spilled Range, or you search for the spilled range operator. This should give you some idea support.microsoft.com/en-us/office/spilled-range-operator-3dd5899f-bca2-4b9d-a172-3eae9ac22efd#:~:text=When%20you%27re%20dealing%20with,which%20spills%20to%20A2%3AA11.
Hello the VBA code is not working 😕 error 53.Can you please advise thank you
hello Anita. That's a file not found error, i would advise you check the file name, ensure it exists, i mean filepath+filename
I so very much need this macro. I follow everything and when i run... i get run time error 53.... What am i doing wrong!! and my file path and file naame exist C:\Users\kelly.horrigan\Desktop\GTM
What are you renaming it to? Are you sure there are no special characters? and hope you have a slash at the end of your file/folder path?
I pulling the filepath C:\Users\kelly.horrigan\Desktop\GTM\* to get the file names into my excel, And for the modified path I use the same path but with out \* so C:\Users\kelly.horrigan\Desktop\GTM
@@kellyhorrigan8117 The modified path needs a slash at the end C:\Users\kelly.horrigan\Desktop\GTM\
@@ExcelMoments THANK YOU!!!!!! I Got it!! Best program ever :)
Thank you very much!
You are welcome