Change excel file name by macro

[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.

 

[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]

 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

 

[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

 

[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