asked on

Renaming Work books in a folder using a macro

I'd like to rename all the Questionaires in a folder using this convention ManagerName_FundName_mmyy
ManagerName=A2 in each work book
FundName=B2 in each work book
mmyy=C2 in each work book

The macro should go to the 1st work book in folder L:Research\Questionnaires and Loop through all the workbooks renaming each one. Each work book will have macros so I would like it to automatically enable the macros as well so that the text box doesn't keep coming up.

dirknibleck

I think this should work. I'm not sure about the macro warning though. Also, I typed this here, so it isn't debugged.

sub rnm()

dim file as string
dim wb as workbook
dim ws as worksheet

file = dir("L:Research\Questionnaires\")

while file <> ""

set wb = application.workbooks.open("L:Research\Questionnaires\" & file)
set ws = wb.worksheets(1)
wb.saveas "L:Research\Questionnaires\" & ws.range("A2") & "_" & ws.range("B2") & "_" & ws.range("C2")
wb.close false
kill file 'This line deletes the original file
file = dir
wend

end sub

kamunya

ASKER

What does the kill file function do?

dirknibleck

in the wb.saveas line, I am saving a new copy of the file under a new name. As a result, the original file remains.

The kill function deletes a file. So kill file should delete the original file.

However, it should probably be

Kill "L:Research\Questionnaires\" & file

ASKER CERTIFIED SOLUTION

dirknibleck

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial