Change excel file name by macro

K

K

Hi all, I have excel files with names (see below) in folder "C:\My
Documents\Forms".

PERMANENT TRANSFER FORM (BT) - 552360.xlsm
PERMANENT TRANSFER FORM (BT) - 512360.xlsm
PERMANENT TRANSFER FORM (BT) - 556360 - PG 1.xlsm
PERMANENT TRANSFER FORM (BT) - 556360 - PG 2.xlsm
PERMANENT FAQS FORM (BT) - 546660 - 546661.xlsm
PERMANENT REQ FORM (BT) - 598774 - 598775.xlsm

I want macro on a button which should shorten the name something like
(see below) of all the files in the folder.

552360.xlsm
512360.xlsm
556360 - PG 1.xlsm
556360 - PG 2.xlsm
546660 - 546661.xlsm
598774 - 598775.xlsm

Please can any friend can help.
 
B

Bob Phillips

Filename = Dir("C:\My Documents\Forms\*.xls"
Do While Filename <> ""

NewName = Right(Filename, Len(Filename) - Instr(Filename, "-") - 1)
Name Filename, NewName

Filename = Dir
Loop
 
K

K

 Filename = Dir("C:\My Documents\Forms\*.xls"
 Do While Filename <> ""

  NewName = Right(Filename, Len(Filename) - Instr(Filename, "-") - 1)
  Name Filename, NewName

  Filename = Dir
 Loop

--
__________________________________
HTH

Bob










- Show quoted text -

thanks for replying Bob. i am getting error on line Name Filename,
NewName. please can you tell me what i am doing wrong
 
D

Dave Peterson

Untested, uncompiled:

Filename = Dir("C:\My Documents\Forms\*.xls")
Do While Filename <> ""
NewName = Right(Filename, Len(Filename) - InStr(Filename, "-") - 1)
Name Filename As NewName
Filename = Dir
Loop
 
B

Bob Phillips

Sorry, my Excel was down last night.

Try this

Filename = Dir("C:\My Documents\Forms\*.xls")
Do While Filename <> ""

NewName = Right(Filename, Len(Filename) - InStr(Filename, "-") - 1)
Name Filename As NewName

Filename = Dir
Loop


--
__________________________________
HTH

Bob

Filename = Dir("C:\My Documents\Forms\*.xls"
Do While Filename <> ""

NewName = Right(Filename, Len(Filename) - Instr(Filename, "-") - 1)
Name Filename, NewName

Filename = Dir
Loop

--
__________________________________
HTH

Bob










- Show quoted text -

thanks for replying Bob. i am getting error on line Name Filename,
NewName. please can you tell me what i am doing wrong
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top