Modify existing vba to always print as PDF

[Cuzzaa]

Hi guys

I am using the VBA code below which works fine but it only prints to the last used printer, instead of ALWAYS printing to PDF:

Sub NewQuoteGenerate()


Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet


    Application.ScreenUpdating = False
    Dim response As VbMsgBoxResult
    With Worksheets("Dashboard")
        If Len(.Range("O26").Value) = 0 Then
            response = MsgBox("You have not added any caveats or assumptions!" & Chr(10) & Chr(10) & "Are you sure you want to continue?", 36, "Caveats & Assumptions")
            If response = vbNo Then .Activate: .Range("O26").Select: Exit Sub
        End If
    End With
    Sheets("Quotation").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ChDir "Z:\example\example\example\example\"
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True
    
    Call sourceSheet.Activate
    
End Sub

Please could you help explain what I need to modify this so that when running the vba it ALWAYS prints to printer 'Microsoft Print to PDF'?

Thank you so much in advance for reading!

 

[AxonFantaxon]

I have a bit of code for Print to PDF that should work if Fluffs solution does not work! Let me know

 

[Cuzzaa]

Re: Help please - modify existing vba to always print as PDF

Hi Fluff

I tried using that code (thank you) once it pops up asking for the file name I then enter this and click OK, it looks like it's doing something but then when checking the folder for the saved PDF file, I notice it hasn't saved anything in the set directory. I'm not sure if it is actually saving anywhere? I have a feeling it's not because it doesn't flash up with the normal printing popup box like it would normally. Any ideas?

 

[Fluff]

Re: Help please - modify existing vba to always print as PDF

How about

  [COLOR=#ff0000] Dim Pth As String
    Pth = "Z:\example\example\example\example\"[/COLOR]
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, filename:=[COLOR=#ff0000]Pth &[/COLOR] Fname & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True

 

[Cuzzaa]

Re: Help please - modify existing vba to always print as PDF

Thanks Fluff!

Would you mind including the code that cancels out of the vba macro if the user clicks 'Cancel' rather than OK when being prompted to enter the file name?

 

[Fluff]

Re: Help please - modify existing vba to always print as PDF

Just add this line

   Fname = InputBox("Please enter a name for the file")
   [COLOR=#0000ff]If Fname = "" Then Exit Sub[/COLOR]

 

[Cuzzaa]

Re: Help please - modify existing vba to always print as PDF

Perfect thanks so much Fluff!

Just lastly - Do you know if there's a way to add in vBInformation into the inputbox function, like you would using msgbox?

 

[Fluff]

Re: Help please - modify existing vba to always print as PDF

Not that I'm aware of, unfortunately.