CODEWITHSUNDEEP
vbams-office
JoshL
JoshL

Reputation: 164

Setting the filename for an Excel file in the SaveAs window without sendkeys in vba

I would like to know if the SaveAs window filename textbox has a property that I could set text to. Currently I'm using the sendkeys function, which works fine for most things until I run into the special characters like parentheses.

My current code is basically:

SaveFldr = "S:\Desktop\grape
Filename = "Grape Data"
Application.SendKeys "{F12}"  ' opens the save as window
Application.SendKeys SaveFldr & Filename & ".pdf"
Application.SendKeys "{Enter}"

Basically I'm looking for a way to set the filename without the sendkeys function. Such as..

Filename.Textbox = Filename

** Assuming the textbox within the SaveAs window was named that.

Upvotes: 1

Views: 556

Answers (1)

Storax
Storax

Reputation: 12167

You could use code like the following one

Sub CallSaveAs()
    Dim fileSaveName As Variant
    Dim SaveFldr  As String
    Dim fileName As String

    SaveFldr = "S:\Desktop\grape"
    fileName = "Grape Data"

    fileSaveName = Application.GetSaveAsFilename( _
        fileFilter:="PDF Files (*.PDF), *.pdf", _
        InitialFileName:=SaveFldr & Application.PathSeparator & fileName)

    If Not TypeName(fileSaveName) = "Boolean" Then
        MsgBox "Save as " & fileSaveName
        ' Save the sheetas PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName
    End If

End Sub

End Sub

Upvotes: 2

Related Questions