Command to Open "Save As..." Window 1

[Vidaman]

I'm trying to write a macro that on a button click, opens another file then opens the "Save As" window so that the user can enter a new file name and location of their choosing. I have no problem opening the file, but the ActiveWorkbook.Save As command wants to actually perform the save as function immediately. I just want the macro to end by leaving the Save As window open.

Thoughts?

-Mike

 

[msmith]

Mike,

There are a couple of ways to do this. Here are two procedures that demonstrate the general ideas:

Sub ActiveWorkbookSaveAs()
Dim FName As String

  FName = Application.GetSaveAsFilename(InitialFilename:="", FileFilter:="Microsoft Excel Workbook (*.xls),*.xls")
  If FName <> &quot;False&quot; Then
    ActiveWorkbook.SaveAs FName
  End If
End Sub

OR

Sub ActiveWorkbookSaveAs()
Dim Result As Boolean
  Result = Application.Dialogs(xlDialogSaveAs).Show
End Sub

Notes: Set InitialFilename to a nonempty string to display a suggested filename. FileFilter can include multiple file types. Keep in mind that GetFilenameSaveAs only returns a file path and name as a string. You can do whatever you want with this; in this case, you are supplying it to the SaveAs method.

Look at the VBA Help for the GetFilenameSaveAs method and the Dialogs collection (built-in Excel dialogs) for more detail as well as additional parameters.


Regards,
Mike

 

[Vidaman]

Mike,

Excellent! This is just what I was looking for. Thanks.

-Mike