How to Save a File using a Cell Value as the Name
Posted by David Holstein on October 18, 1999 11:46 PM
I would like to know how to save a file basing the name on a cell value, after the macro saves the value of 1 will be added to the cell so next time it will not overwrite (eg. Wage0001.xls, Wage0002.xls). I would like the the option of using letters and having the cell value appended to it in some way.
If anyone could help it would be appreciated.
Thanks
David
Posted by Ivan Moala on October 19, 1999 2:27 AM
David try this;
Private Sub DavidSave()
'**********************************
'* Assumes cell ref @ C1 *
'* and format # = 0000 ie 0001 etc*
'**********************************
Dim StrFileName
Dim SaveFile As String
Dim Nm As String
Dim LenNm As Integer
Dim Number As String
Nm = Application.ActiveWorkbook.Name
LenNm = Len(Nm)
Number = Format(Range("C1"), "0000")
SaveFile = Left(Nm, LenNm - 4) & Number & ".xls"
StrFileName = Application.GetSaveAsFilename(SaveFile)
If StrFileName <> False Then
ActiveWorkbook.SaveAs FileName:=SaveFile
Range("C1") = Number + 1
End If
End Sub
regards
Ivan
Posted by Ivan Moala on October 21, 1999 5:26 AM
David
slight error in that your file should be initially
saved as per format Filename0000.xls then enter
macro below (changed) 4 to 8 in the
SaveFile = Left(Nm, LenNm - 4) & Number & ".xls"
should be
SaveFile = Left(Nm, LenNm - 8) & Number & ".xls"
