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
Else
ThisWorkbook.SaveAs strFileName
End If
End Function
