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

 

[ryan.naquin]

I got help from the Chandoo forum on this same issue and I got the code below to work. But I would like to have the savedialog box to open and use the namefile string as the name and then allow the user of the worksheet to select where he wants to save the file, instead of how it is now where it is saved to \desktop\tickets
Thanks!

Sub SaveFile()
    Dim NameFile As String
        With Worksheets("SO1")
        NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
        End With
ThisWorkbook.SaveAs Environ("USERPROFILE") & "\Desktop\tickets\" & NameFile
End Sub

 

[ryan.naquin]

I got it to make the save as dialog come up with the correct name. I am also able to select a different folder and click save. Then I get a runtime error '13'.
When I click debug the yellow error points to the line:
If Namefile = False Then
Am I doing something wrong?

Sub SaveFile()
    Dim NameFile As String
        With Worksheets("SO1")
        NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
        End With
        NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Desktop\tickets\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
        If NameFile = False Then
        MsgBox "File not saved"
        Else
        ThisWorkbook.SaveAs Filename:=NameFile
        End If
End Sub

 

[ryan.naquin]

Thread solved. The following code works for me.

Sub SaveFile()
    Dim NameFile As Variant
        With Worksheets("SO1")
        NameFile = .Range("M3") & "_" & .Range("C11") & "_" & .Range("B22") & ".xls"
        End With
        NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Desktop\tickets\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
        If NameFile = False Then
        MsgBox "File not saved"
        Else
        ThisWorkbook.SaveAs Filename:=NameFile
        End If
End Sub

 

[texitech]

Thread solved. The following code works for me.

Sub SaveFile()
    Dim NameFile As Variant
        With Worksheets("SO1")
        NameFile = .Range("M3") & "_" & .Range("C11") & "_" & .Range("B22") & ".xls"
        End With
        NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Desktop\tickets\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
        If NameFile = False Then
        MsgBox "File not saved"
        Else
        ThisWorkbook.SaveAs Filename:=NameFile
        End If
End Sub

Hi Ryan,

I was trying your above code, It works perfectly, but my criteria is little different from yours. I wanted to save the file in .pdf format. And the file should open publish, can you help me on this.

I tried replacing .xls to .pdf in the above code, the file gets created but it will not open after publish. Once I open the file from the saved location it throws an error as unsupported file & the file size is becoming too high (in MBs).

Many Thanks in advance.

Regards
Texitech

 

[Cdmassey987]

Okay so I think the code above is pointing me in the right direction. I am trying to save large workbooks while clearing out sheets. Basically an archival function that gives us a new workbook for the new year. so I was thinking 1) create a copy 2)clear out old data 3) Save As.

problem is the data is different on each sheet and from workbook to workbook (over 100 workbooks in all) any chance there is a command to search a sheet to find cells with any data and then clear those cells?

 

[mikerickson]

I think that this code might help.

Sub test()
    Application.Dialogs(xlDialogSaveAs).Show "myFileName"
End Sub

Sub test2()
    If Application.Dialogs(xlDialogSaveAs).Show("otherName") Then
        MsgBox "file was saved"
    Else
        MsgBox "file not saved"
    End If
End Sub

 

[Cdmassey987]

mrkerickson,

thank you. I really like both of these. I really like the 2nd one confirming the save or not save. Next I have to work on getting everything cleared out. Is the best way simply to specify sheets and ranges? would I need to denote all the various sheets at the beginning of the command using Dims?

 

[mikerickson]

Both of those SaveAs the AcitveWorkbook. All of its sheets and all of its ranges.

 

[SVCooper]

Hi Ryan,

I was trying your above code, It works perfectly, but my criteria is little different from yours. I wanted to save the file in .pdf format. And the file should open publish, can you help me on this.

I tried replacing .xls to .pdf in the above code, the file gets created but it will not open after publish. Once I open the file from the saved location it throws an error as unsupported file & the file size is becoming too high (in MBs).

Many Thanks in advance.

Regards
Texitech

I'm still puzzling through a similar problem myself, but I think you'll find that you probably need to use the ActiveSheet.ExportAsFixedFormat method

 

[jonathan_f]

Hi I’m new to all this.

I’m trying to create two save scenarios first a save as dialog box that will allow the user to save a PDF to a file of there choice but takes its file name from “AQ5”.

And another that does the same but takes its save location from “AR9”