• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shorten filenames with VBA

Simeon

Simeon

New Member
Hi all,

how can i use VBA to shorten all filenames in a folder?
My files all start with "ABC001" and i only need these first six characters of the filename (e. g. full filename ABC001 Holidays)
The Makro need to check if the file begins with "A" and if yes, it should shorten the filename and leave only the first six characters in the name: ABC001

Thanks in advance.

Simeon
 
Simeon
My files all start with "ABC001" and i only need the first six characters of the filename.
... with Your sample ... it has six characters ... hmm?
Note that each filename should be different!
... that should take care or ... would later shorter file overwrite previous?
 
Hi @vletm,
yes, this is only a example how the result should be.
As example i got: ABC001 Holiday.xlsx, ABC002 Traveler.xlsx, ABC003 Railway.xlsx and what i want to have this: ABC001.xlsx, ABC002.xslx, ABC003.xlsx.
So the filename is greater than six characters but i want to keep just the first six :)

The files should be renamed in the same folder i don't need the original filenames. So it's overwriting.

Simeon
 
Last edited:
Simeon
How many hundreds of files do You have to 'rename'?
... with VBA ... maybe cannot 'rename' ( copyas new name and kill original' and killing means to delete something )
About overwriting ... are You over 99% sure, that each original files beginnings are different?
Are ALL files Excel-files in that folder?
 
Hi @vletm i have 120 Files and they are all in one Folder. So the Macro should loop thru the folder where the Macro is located to do the change.
Every file begins the same with "ABCxxx". The x stands for a number, e.g. 001, 002, 003 and so on.
The Macro has to "cut" or "delete" everything after the first six elements in the file name.

I played a little bit around but i'm sill looking for a good solution.

Simeon
 
Simeon
Here is an one sample which worked here,
'minor' warning ... note -
This will delete original-files too! ( after copyas it with a new name )
If ... everything goes well then ... everything went well
otherwise
there would be still ... one file ... Book1.xlsb.
... but of course, You have backups!
 

Attachments

  • Book1.xlsb
    19.2 KB · Views: 43
I think i answered every question @vletm what did i missed?
Alle the files are in One folder and all the files begin the same with ABC and three digits after that.
If i can't rename a file i need to create new file with the right file name und delete the old one.

Or did i missed something to tell you? :)

Simeon
 
@vletm sorry, I posted my last comment while you gave the solution.
It works well, thank you very much :) !
This helps me lot!

Simeon
 
Simeon
I can comment only answers which You have written.

Your missed questions:
About overwriting ... are You over 99% sure, that each original files beginnings are different?
My sample file do not check if there are eg ABC001 testing.xlsx and ABC001 results.xlsx ... those both would handle like ABC001.xlsx.
Are ALL files Excel-files in that folder?
My sample tries to take care only Excel-files ... but if there are older Excel-version files like ABC001.xls then there will be a challenge.
Even 120 files names would be safer to modify manually, especially if this is once-in-the-lifetime-procedure!
... as I wrote ... the code will
#1 save current file as shorter named file
#2 delete original file
sometimes ... step #1 could be missed ... but not step #2 (deletion) ... means ... no more that file
>>> that's why - I tried to give a hint ... have backups before ...
 
Simeon, I'm coming to this too late, if you're saying you tried vletm's solution and liked it. But I wouldn't use the Excel functions at all. You can do it in Excel VBA, but all you need, I should think, is the FileSystemObject, which you can use to cycle through all the files in a folder, check the name and change the ones you want to change. Two advantages: You don't have to open the files, thus saving time, and the FileSystemObject won't insist on their being Excel files; it'll work on any file.

If you've never used the FileSystemObject, holler and I'll show you some documentation and sample code.
 
@vletm Ok, I understood. It's okay for me that the original file will be deleted. The files are created with a split via a different macro i use, so i can reproduce this source files without any problems anytime.
@BobBridges Thanks for your Input. This sounds interesting. Indeed, i don't have much experience with the FileSystemObject, so i would be very grateful if you can show me some documentation and sample code. Hope that i can use this for my propuses.

Simeon
 
I use this for my FSO reference. Here's an untested sample of how I might use it:
Code: 
  Set ofs=CreateObject("Scripting.FileSystemObject")
  Set ofo=ofs.GetFolder("C:\MyFolder\") 'ofo is a folder object, representing the folder where you keep all those files
  For Each ofi in ofo.Files 'the Files property of a folder object is a collection of file objects
    fn=ofi.Name 'the Name property is, obviously, the filename
    If Len(fn)<7 Then Goto IterateFile 'skip file if it's already short
    ofi.Name=Trim(Left(fn,6)) 'change the filename
IterateFile:
    Next ofi

The coding style is of course my own; you'll use your own variable names etc. I also use the FileSystemObject routinely for reading text files and loading them into worksheets, preferring to go to the extra work of parsing them manually for the benefit of being sure exactly how each field will be treated rather than try to use Excel's built-in text-to-columns feature. And it's very handy when I want to open every workbook in a folder, but only if its name follows a certain pattern.
 
Simeon
Here is other way to shorten filenames with VBA,
which work with built in native VBA methods as You have asked.
This work with both OS.
 

Attachments

  • Book1.xlsb
    18.9 KB · Views: 53
Hi @BobBridges thank you for this refrence. I will try to use FSO with the help of your documentation :)

Hi @Hui thanks for sharing this Tool. It's not soo Off Topic, because the Tool does the same like the Macro i need. For my issue it is important that i have a Macro for the renaming process, so i can add it to my existing Macro. For other cases the Tool you shared is enough to do the job :)

Hi @vletm thanks for the second solution. I tried it and it works well. Only 3 Files are not deleted after renaming and i can't find the reason why. The structure of the file names are as explanined and it should work exactly like on the other files. I will debug tomorrow and get deeper into your code - maybe i can find the reason why the macro doesn't delete these 3 original files :)

Thanks to you all for you help.
Simeon
 
Last edited:
Simeon
The basic - The 1st solution and the 2nd solution works different way!
The 1st solution works as I have written above few times - please, reread.
The 2nd solution changes files name; it won't delete!
Both solutions have same rules with filenames - as You have given.
Without Your written answers, there is a challenge to modify!
 
@vletm i saw this difference while debugging your code - it's now clear for me.
I can't find the reason why the second solution skips 3 files. I ran the Code step by step with F8 and now it works well.
I will do some more tests and if questions or problems came up i can't solve i will post here and ask for support :)

Thanks for your time and will to help me with this here.

Simeon
 
You must log in or register to reply here.
Back
Top