MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro for Saving FIle as

Posted by Doug Kensrue on February 15, 2002 8:52 AM

I need help with this macro. I want to save a file using the contents of a cell in the workbook as the name. And can it give you an input box showing the value of that cell as a suggestion with the ability to add or change before finishing the save?

Posted by Mark O'Brien on February 15, 2002 8:58 AM

This assumes the cell is "A1" on "Sheet1"

Application.Dialogs(xlDialogSaveAs).Show Sheets("Sheet1").Range("A1").Value

Posted by Robb on February 15, 2002 9:07 AM

This example uses the InputBox to get the name of the file. You can easily change the "NAME" to a cell and then the user can change it.

' Get the name of the new worksheet
strReply = Application.InputBox("Set-up complete! File is ready for production." & vbCrLf & vbCrLf & _
"Please enter a NAME to save your workbook." & vbCrLf & vbCrLf & _
"SETUP COMPLETE", "Name", , , , , 2)

' Use Save as Dialog box to save the file
SaveAsName (strReply)

Function SaveAsName(strName As String)
Dim strFileName As String
Dim strVersion As String
Dim strName As String
Dim varFileName As Variant

strFileName = strName

strVersion = "v2.9.xls"
strName = strName + " "

varFileName = strName + strVersion

' Show the open dialog and parse the selected
' file name to the String variable "strFileName"
strFileName = Application.GetSaveAsFilename(varFileName, "Microsoft Excel 97 Files (*.xls), *.xls")

' Check to see if they have cancelled from the "Save As" Dialog box
If strFileName = "False" Then
MsgBox "Set-up cancelled! Workbook has been cleaned.", vbOKOnly + vbInformation, "SET-UP CANCELLED"
Exit Function
ThisWorkbook.SaveAs strFileName
End If
End Function