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!

 

[Fluff]

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

Try removing this line

Sheets("Quotation").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

 

[Cuzzaa]

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

Hi Fluff, thanks for trying to help.

Unfortunately that didn't work. Just for reference, I only want to print to PDF the sheet 'Quotation'. At the moment it only prints to the Active Printer, but I am trying to set this to always use the 'Microsoft Print to PDF printer'.

When I removed that line it bypassed the popup to save the document before it prints and didn't do anything.

Any other ideas?

 

[Fluff]

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

This section of code

    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

is creating a pdf file in this folder

ChDir "Z:\example\example\example\example\"

named with the name of the active sheet.

 

[Cuzzaa]

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

That's what I also thought would happen, but if the active printer is set to a standard printer, then on click of the VBA button it seems to override the popup asking to save the file as a PDF and just instead prints the file normally and just displays msgbox 'Your quote has been generated!'

If the active printer is the microsoft PDF printer then it runs as expected and presents the user with popup asking what to call the PDF document before saving as PDF.

 

[Fluff]

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

If you remove the line of code I initially mentioned, then you will get a PDF file saved with the name of the active sheet.

 

[Cuzzaa]

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

When I tried that it didn't work as it didn't present the user with the popup to name and save the 'Quotation' sheet as a PDF. I need the user to be able to name the sheet to whatever they want, not just the name of the active sheet.

Do you know of any other way to simply just set the default printer type to Microsoft Print to PDF?

Thank you

 

[Fluff]

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

You do not need to change the printer.
Do you just want to give the user the chance to change the filename, or the path as well?

 

[Cuzzaa]

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

Just the filename, I set the path already for ease for the user as all quotes should be saved here

[COLOR=#333333] ChDir "Z:\example\example\example\example\"[/COLOR]

using example as to not state the actual directory

I'm a bit confused though, the code works perfectly but only when the active printer is set to the Microsoft Print to PDF so wouldn't it be easier just to set this?

If I last printed to an actual printer, and then try clicking on the button it doesn't work because it just literally prints out.

If I then go to print settings and change it to Microsoft Print to PDF and then cancel, then click on the button to run the VBA code then it works fine again.

Surely all I need to do is somehow set the active printer to the Microsoft Print to PDF and it will work as expected every time the button is clicked?

Sorry to be a pain. Thanks for offering to help me out with this

 

[Fluff]

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

There is absolutely no point in playing around with the active printer, when export to Pdf will do the job.

Sub NewQuoteGenerate()


Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet
[COLOR=#0000ff]Dim Fname As String
   Fname = InputBox("Please enter a name for the file")[/COLOR]
    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
    
    ChDir "Z:\example\example\example\example\"
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, filename:=[COLOR=#0000ff]Fname & ".pdf"[/COLOR] _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True
    
    sourceSheet.Activate
    
End Sub