How to open the SAVE AS dialog box with VBA?

[peter_z]

Hey Guys,
Just wondering if anyone knows how to use VBA to open the save as dialog box?

I would like for the end user to be able to save the file and name the file to what ever they want.

Cheers for the help!
Peter

 

[repairman615]

Hello,

<font face=Courier New>    Application.GetSaveAsFilename</FONT>

 

[peter_z]

You're the man!

Cheers works awesome

 

[repairman615]

Your welcome,

Welcome to the board!

Also have a look here.
http://www.mrexcel.com/forum/showthread.php?t=311592&highlight=getsaveas
The one line above does have some limits and could throw some errors around.

 

[brettdj]

Picking up further on the limits comment, for the actual SaveAs dialog you would use something like this

GetSaveAsFilename is used to get the name of a file - but it doesn't save the file by itself

Sub filesave()
    Dim bFileSaveAs As Boolean
    bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show
    If Not bFileSaveAs Then MsgBox "User cancelled", vbCritical
End Sub

 

[peter_z]

Nice one cheers brettdj and repairman615 very helpful advice!
Got this report working nicely now

 

[ryan.naquin]

This code works great for what I'm trying to do. Can you help me take it one step further. Is there a way to have a file name already in the saveas box that comes up. The file name I would like would come from 3 cells. A1_B1_C1 It is important to have the underscores between. I like this code because it gives the user the option to save it in a folder of their choice instead of a predefined one in a code.

Thanks!

 

[dmt32]

This code works great for what I'm trying to do. Can you help me take it one step further. Is there a way to have a file name already in the saveas box that comes up. The file name I would like would come from 3 cells. A1_B1_C1 It is important to have the underscores between. I like this code because it gives the user the option to save it in a folder of their choice instead of a predefined one in a code.

Thanks!

Just use the InitialFilename Optional Variant</STRONG>

following adpated from Helpfile.

Dave

Dim IntialName As String
Dim fileSaveName As Variant
InitialName = Range("A1") & "_" & Range("B1") & "_" & Range("C1")
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
    fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName <> False Then
    MsgBox "Save as " & fileSaveName
End If

 

[mikerickson]

This code works great for what I'm trying to do. Can you help me take it one step further. Is there a way to have a file name already in the saveas box that comes up. The file name I would like would come from 3 cells. A1_B1_C1 It is important to have the underscores between. I like this code because it gives the user the option to save it in a folder of their choice instead of a predefined one in a code.

Thanks!

Application.Dialogs(xlDialogSaveAs) Range("A1").Text & "_" & Range("B1").Text & "_" & Range("C1").Text

 

[ryan.naquin]

Thanks Dave! Where would I fit your code in with this the one from brettj?

Your code kind of works by itself but it is no saving nor showing any errors that it couldnt save!

The save dialog box will pop up with the data from the 3 cells and I click save, but when I goto the path on my computer that file is not there.

On another note how do you copy and paste code with the correct syntax when replying to a thread?