Help saving workbook

[Russ Morgan]

I need a VBA resolution to save a workbook as a certain name.

EX: I have workbook ONE.XLS open and need it to be saved as ONE082508A.XLS
where "ONE" is the original name, "082508" is the date it is being saved and
"A" is derived from a cell within the workbooks, say cell A1.

Can someone please help me with this?

 

[Bob Bridges]

This isn't too hard. Start by looking up the Workbook.SaveAs method; it'll
tell you the syntax for saving a workbook under a different name, in a
different format, with a password, all that stuff. In the end you'll
probably make it a simple call like

MyWorkbook.SaveAs NewName

....where NewName is a variable into which you've constructed the new
workbook name. As for how to set up that name, I suggest when saving it as a
file name you use yymmdd rather than mmddyy; the files sort much better that
way. Up to you, though. Your program must get the current name of the
workbook, add on the date and then add on the value of A1. If it were me,
I'd do it like this:

NewName = MyWorkbook.Name
ip = pos(NewName, ".")
NewName = Left(NewName, ip - 1)
NewName = NewName & Format(Date, "yymmdd") & Range("A1").Value
NewName = NewName & ".xls"

....or something like that. You'd have to consider the path, too, if you
didn't want it stored in the default folder.

 

[Joel]

With ThisWorkbook
BName = .Name
BaseName = Left(BName, Len(BName) - 4)
MyDate = Format(Date, "MMDDYY")
NewName = BaseName & MyDate & ".xls"
.SaveAs Filename:=.Path & "\" & NewName
End With