File name from a cell

  • Thread starter Thread starter bcreager
  • Start date Start date
B

bcreager

I am trying to have date in a cell become the file name and appear i
the save as dialog box as the file is saved the first time
 
One way:

Option Explicit
Sub testme01()

Dim mySuggestedFileName As String
Dim myFileName As Variant

With Worksheets("sheet1")
mySuggestedFileName = "c:\my documents\excel\test\" _
& Format(.Range("a1").Value, "yyyy_mm_dd") & ".xls"
End With

myFileName = Application.GetSaveAsFilename _
(InitialFileName:=mySuggestedFileName, _
filefilter:="Excel File,*.xls")

If myFileName = False Then
MsgBox "try Later"
Exit Sub
Else
Application.DisplayAlerts = False 'no warning for overwriting
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
End If

End Sub
 
Back
Top