Renaming Files Using Macro

[DonG]

I have the VBA code that captures file names of files within a folder on my HD and populates those file names into a column in Excel. This column can be retained and a new file name developed in a subsequent column. This part is working well.

Question: Is there a VBA code that will rename the original files to the new name?

BTW I'm using an older version of Excel. (2003)

TIA,

Don

[Leith Ross]

Hello Don,

Welcome to the Forum!

You can use the Name As statement in VBA. Here is information from the VBA help files.

Syntax

Name oldpathname As newpathname

The Name statement syntax has these parts:

Part and Description
oldpathname Required. String expression that specifies the existing file name and location — may include directory or folder, and drive.

newpathname Required. String expression that specifies the new file name and location — may include directory or folder, and drive. The file name specified by newpathname can't already exist.



Remarks

The Name statement renames a file and moves it to a different directory or folder, if necessary. Name can move a file across drives, but it can only rename an existing directory or folder when both newpathname and oldpathname are located on the same drive. Name cannot create a new file, directory, or folder.

Using Name on an open file produces an error. You must close an open file before renaming it. Name arguments cannot include multiple-character (*) and single-character (?) wildcards.

[DonG]

Thanks Leith for the quick reply. I'll give it a shot and see if this newbie can make it work...Thanks again...

Don

[Leith Ross]

Hello Don,

If you run into any problems, let me know.

[DonG]

Hello Don,

If you run into any problems, let me know.

So far so good, it worked great. Had to separate the file name in Excel using several Text To Columns and then with the Concatenate function I got the results I was after. I'll use the recorder to code those into my macro. Then code in a loop for the number of files. But the renaming worked great....thanks much and you have a great day....

Don

[Leith Ross]

Hello Don,

Congratulations on job well done! Glad I could help.

[DonG]

Not sure if I should ask this here as my first question was answered but this is related....

Consider a file name that has multiple parens within it. The data within both parens varies as does the overall file length. The second set of parens is always at the end of the file name. As the data (length) varies I can't find a way to use the Len function.

xxxxxxx(xxxxxx)xxxx-xxxx(xxxxxx)

Is there a code or a function that will isolate (remove and save to another cell) the last set of parens and the data within them? Or do I have to split it up using Text to Data and then Concatenate the needed parts back together?

TIA,

Don