Renaming long file to first subset identified by "_"

[draconius]

I have a list of files where the file name is "851113_Mail_132013_11886808.pdf" and need to rename it to "851113.pdf". I have found a couple other codes, but nothing seems to work. I have even tried a work around of delimiting the column by _, copy pasting column a into column B and using the below code

Please Login or Register  to view this content.

However, even with the correct file name "851113.pdf" in Column B and "851113_Mail_132013_11886808.pdf" in Column A, it claims file not found... This would have to be the "workaround method where I have macros running to pull out the first set of numbers and add on ".pdf", but even this code isn't working.

Optimally, I would like a code in one to read just up to the _ and rename the files in the folder as the first 6-10 digits.

I haven't worked with pulling out the beginning of a string before in vba...only excel and SAS.

Any assistance would be greatly appreciated.

[JBeaucaire]

Assuming your pathname is correct, you basically want to rename every file IN that path to whatever string leads up to the first _ character?

Please Login or Register  to view this content.

[draconius]

I have encountered an issue with this when there will be duplicate records. I get the error "File already exists". Is there a way to either skip these all together? So "If file exists, then +1"?

[JBeaucaire]

Is this better:

Please Login or Register  to view this content.

[draconius]

Negative. That produces results below. I realize my error as above I asked "if file exists, then +1"... You produced exactly what was asked...the fault is mine

Before:
851516_From_Me_Now
851516_From_Me_Now_2
851516_From_Me_Now_3

After
851516
8515162
8515163

Desired effect is either skip the duplicates all together ("851516", "851516_From_Me_Now_2", "851516_From_Me_Now_3") or add "_#" ("851516", "851516_1", "851516_2")

We need to retain the first segment of 6 as a whole without affecting the name.

Thank you for all your help! I will be "nothing says 'thanks' like a steak dinner" for all your help!

[JBeaucaire]

Take out the - 1 in the macro and it will keep the _ character.


If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[draconius]

That wouldn't work because then the loop would see an _ on the just fixed file and try to change it again...

It creates a never ending loop within a loop that doesn't go beyond the second file if there is an _.

Disregarding the desire of _ or +1, is there a simple "if exists, then skip"? Thanks again

[JosephP]

perhaps

Please Login or Register  to view this content.

[JBeaucaire]

Nice, JP. the OP originally had not wanted the extensions. Does including them resolve the issue?

[JosephP]

the first post said nothing about removing extensions that I saw-what did I miss?

[JBeaucaire]

Optimally, I would like a code in one to read just up to the _ and rename the files in the folder as the first 6-10 digits.

I may have misunderstood this request.

[draconius]

Thank you JP! On a small trial run, it looks like it is working perfectly. However, when I step through i can go indefinitely. I'll return with the results.

Again...HUGE THANK YOU TO YOU BOTH!