CODEWITHSUNDEEP
vbaexcel
Matthew Bond
Matthew Bond

Reputation: 191

Enabling Save as automatically through vba (Without clicking save on prompt)

enter image description here

How can I get excel to automatically select save through VBA without manually clicking it. E.g- I have Code which selects each value on a Data Validation list and gets me to the stage below, although I have to click save each time.

I have tried adding in:

  Application.EnableEvents = True

But it still only takes me to the stage on the image.

    Sub PDFActiveSheet()
    Dim ws As Worksheet
    Dim strPath As String
    Dim myFile As Variant
    Dim strFile As String
    On Error GoTo errHandler

    Set ws = ActiveSheet

    'enter name and select folder for file
    ' start in current workbook folder
    strFile = Cells.Range("B1") & " Period " & Cells.Range("J1")

    strFile = ThisWorkbook.Path & "\" & strFile

    myFile = Application.GetSaveAsFilename _
         (InitialFileName:=strFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")

    If myFile <> "False" Then
        ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False, _
        From:=1, _
        To:=2  
   End If   
    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler

    End Sub

Upvotes: 2

Views: 5748

Answers (1)

Rory
Rory

Reputation: 34075

To avoid prompting:

Sub PDFActiveSheet()
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler

Set ws = ActiveSheet

'enter name and select folder for file
' start in current workbook folder
strFile = Cells.Range("B1") & " Period " & Cells.Range("J1")

strFile = ThisWorkbook.Path & "\" & strFile & ".PDF"

    ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False, _
    From:=1, _
    To:=2

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler

End Sub

Upvotes: 2

Related Questions