Modify existing vba to always print as PDF

Cuzzaa

Board Regular
Joined
Apr 30, 2019
Messages
86
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:

Code:
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?
 
Upvote 0
Re: Help please - modify existing vba to always print as PDF

How about
Code:
  [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
 
Upvote 0
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?
 
Upvote 0
Re: Help please - modify existing vba to always print as PDF

Just add this line
Code:
   Fname = InputBox("Please enter a name for the file")
   [COLOR=#0000ff]If Fname = "" Then Exit Sub[/COLOR]
 
Upvote 0
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?
 
Upvote 0
Re: Help please - modify existing vba to always print as PDF

Not that I'm aware of, unfortunately.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top