Trim specific number of characters from multiple files

[chin67326]

I have a userform. I want to enter a number in the textbox and click a button that will allow me to select a folder. I then want all the files in that folder to be trimmed/reduced/shortened by the number entered in the textbox. For example, if the name of a file is 'appleboxforstorage' and I entered the number 9 in the textbox, then the new filename would be 'orstorage'. By the way, no filename extensions are shown in the folder as I have that feature turned off (i.e. hide extensions for known filetypes).

I have attached a workbook with what I have done so far. I only have the code to open and browse for a folder.

I picture of the userform is below also:

010119.png

Thank you!

[JLGWhiz]

I incorporated the truncation code into your folder picker code.

Please Login or Register  to view this content.

[chin67326]

Thanks and happy new year!

I tried it and it did not work. I got a debug runtime error 52 with the below line highlighted in yellow:

Please Login or Register  to view this content.

Also, regarding the below line, why did you include .xls? The files which I am trying to shorten their filenames are not Excel files.

Please Login or Register  to view this content.

Also, maybe the error is caused because some of the characters in the filename are non-alphanumeric (Chinese) characters. There must be a way to bypass non-alphanumeric I think and hope.

[JLGWhiz]

Change this line as shown below. I used ".xls*" because I assumed that your files would be Excel files since not other types were specified in the OP. The code worked in testing with Excel files, so it should work with other files if the wildcard is used by itself as an extension.

Please Login or Register  to view this content.

[chin67326]

Thank you. You are right, it works but only with filenames containing alphanumeric characters. I tested it with various file types containing alphanumeric filenames and it worked. However the files I am using contain mostly Chinese characters and the code creates a run-time error 52 as shown below unfortunately.

error020219.png

There must be some way to have a code written that bypasses and ignores non alphanumeric characters I believe. I just don't know how.

I have attached an example file containing a non alphanumeric filename so you can see the error yourself.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[chin67326]

@AliGW, oh - sorry boss. That makes sense. Especially saving on server space too. By the way, are you able to lend your expertise to help me get a solution to bypass non-alphanumeric characters?

Happy new year.

[AliGW]

No, sorry - I don't do VBA. Just an administrative note.

[mohan.r1980]

Try Below Code

Please Login or Register  to view this content.

[chin67326]

mohan.r1980, thanks but it did not work. First it only got as far as opening the folder picker and then the below error showed:

b03.01.19.png

[mohan.r1980]

working here
see attachment

[chin67326]

mohan.r1980, I tried it every possible way and it is not working. It keeps on prompting to open a folder and does not carry out the process. Then the runtime error 9 shows. JLGWhiz's code worked but only with alphanumeric filenames. Non-alphanumeric (e.g. Chinese) character filenames don't work and I am looking for a code that can work with non alphanumeric filenames.

Thanks for trying anyway...

[chin67326]

Just checking, is there anyone here with the expertise to help me find a solution to this problem? Much appreciated to anyone who can solve this. Thank you!

Emma

[mohan.r1980]

thanks but it did not work. First it only got as far as opening the folder picker and then the below error showed:

My code is for multiple file picker.
you have to select multiple files in your folder

[chin67326]

Hi mohan.r1980, thanks for your reply. I tried picking files and still got an error.

As a test, can you please rename any test file with the below filename and try your code? Do you get an error or were you able to trim the filename? It seems I cannot trim filenames that have Chinese (non-alphanumeric) characters.

Please Login or Register  to view this content.

Please help me solve this.

Emma

[mohan.r1980]

show me the desire Input and output of given filename (which you exactly wanted).

[chin67326]

Thanks for your reply.

For example, let us say I have a file in a folder with the below filename:

Please Login or Register  to view this content.

The above is 29 characters in length.

What if I want to trim the above file name and reduce the amount of characters from left to right. For example, I may want to remove the first 20 characters. I will therefore enter 20 in the textbox of the userform and when I click the command button, the new file name will be as shown below:

Please Login or Register  to view this content.

Therefore 20 characters is trimmed from the filename, leaving only 9 characters.

That is what I want but I need a code that can recognize and process the Chinese characters. These are non-alphanumeric characters. The codes that you and others have suggested do not seem to support Chinese characters. I Googled online and saw that there is a code that can be used that will make some sort of Function call and tell the code to recognize the Chinese characters but I don't know how to write the code.

Thank you.

[chin67326]

mohan.r1980, any luck?

Emma

[jindon]

Do you want to change;

"Vegetables正在画的房子GardenVariety" to "GardenVariety"

"Flooring米饭可以购买不同的质&#37327.xlsx" to "37327.xlsx"
?

[chin67326]

Hi jindon, yes pretty much you are correct. It all depends on the value that I will input in the textbox. But yes, basically I want to trim the filenames.

Thanks,
Emma

[jindon]

OK

The problem is that vba can not read these characters directly.
So if you copy all the file names that you want to change in a Text file, and if the file can be opened by Excel, it can be done.

Anyway, you can not handle these files as they are.

[chin67326]

That is a pity as there are about 3000 files; too much time to copy them.
I thought there was some clever way for VBA to bypass the Chinese characters in the code.

[jindon]

I don't think so, unfortunately.