Renaming an Excel file using a macro
 

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

Renaming an Excel file using a macro
 

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

Renaming an Excel file using a macro
 

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.

----
mkarja


"Sinex" wrote in message ...
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

Renaming an Excel file using a macro
 

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

--
Regards,
Tom Ogilvy


"Sinex" wrote in message
...
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