Renaming an Excel file using a macro

[Sinex]

Hi,
I made an Excel sheet (say Form.xls). There are 3 fields which a user
will fill (say First, Middle & Last Name). When the user closes the file, I
want to rename the file to FirstName_MiddleName_LastName.xls (using the
values entered in the sheet). Is this possible using some macro....renaming
a file while the file is still open sounds like an issue.

....manoj

 

[Markus Scheible]

Hi Manoj,


fields which a user

will fill (say First, Middle & Last Name). When the user closes the file, I
want to rename the file to

FirstName_MiddleName_LastName.xls (using the

values entered in the sheet). Is this possible using some

macro....renaming

Try:

workbooks("abcd.xls").SaveAs FileName:=Range
("name1").Value & "_" & Range("name2").Value & "_" & Range
("name3").Value & ".xls"


With name1 name2 name3 = your cells...

Best

Markus

 

[mkarja]

You could possibly save a new file as FirstName_MiddleName_LastName.xls
but I don't think you could rename the file when it's open.

 

[Tom Ogilvy]

Assume the values are in sheet1, cells A1, B1, C1 in the order you describe

You can use the Beforeclose event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error goto ErrHandler
application.EnableEvents = False
sPath = ThisWorkbook.Path
if Right(spath,1) <> "\" then _
sPath = sPath & "\"
Application.DisplayAlerts = False
with thisWorkbook.Worksheets("Sheet1")
ThisWorkbook.SaveAs FileName:= sPath &
.Range("A1").value & "_" & .Range("B1").Value & _
"_" & .Range("C1").Value & ".xls"
End With
Application.DisplayAlerts = False
ErrHandler:
Application.EnableEvents = True
End Sub

This code would be placed in the ThisWorkbook Module. It forces a save of
the workbook which is not always desirable.

If you are not familiar with events, seee Chip Pearson's page on Events:

http://www.cpearson.com/excel/events.htm