It worked, I had to make a few few small tweeks to it, but it works good.

"OssieMac" wrote:

Hi again Brian,

I was a bit confused by what you were attempting to do but now I think that
I might understand your problem.

GetSaveAsFilename Displays the standard Save As dialog box and gets a file
name from the user WITHOUT ACTUALLY SAVING ANY FILES.

If the user does not select a filename or cancels etc then the filename will
return as false. The user can also edit the file name in the dialog box.
Clicking on Save in the dialog box only gets a filename. It does not save as
one would expect.

You only use this if you want the user to select an existing file or one
that follows a particular pattern like the following example and perhaps then
edit the name.

Note that fileSaveName must be declared as a Variant so that it can return a
boolean value of false or the filename string otherwise the code will error.

The fileFilter must follow the names that you see in the save as dialog box
under normal use of Save As. I have use a macro enabled which is only
available in xl2007.

Of course you can edit the code to create strFile whatever you want. I did
not have the values that you have used.

Unless you want the user to be able to select the filename then there is
really no point in using GetSaveAsFilename. Go straight to
ActiveWorkbook.SaveAs Filename:= .

The Excel 2007 help is misleading because it only places a message where the
SaveAs need to be.

Sub UsingGetSaveAsFilename()

Dim fileSaveName As Variant
Dim strFile As String

strFile = "This New*"

fileSaveName = Application.GetSaveAsFilename _
(InitialFileName:=strFile, _
fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm")

If fileSaveName < False Then
ActiveWorkbook.SaveAs Filename:= _
fileSaveName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Else
MsgBox "Not saved." & vbCrLf & _
"User cancelled without selecting a filename."

End If

End Sub

--
Regards,

OssieMac