CODEWITHSUNDEEP
vbaexcelpdfpdf-generation
Tuberose
Tuberose

Reputation: 444

Using PrintOut for Adobe PDF with predefined path and file name

I want to print a macro-enabled MS-Word Document that is embedded in a Excel Worksheet which it's name is SalaryPaycheck, from the excel macro module.

I using code below:

Sub PrintIt()

    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim Oshp As Object
    Dim strCurrentPrinter As String

    ActiveSheet.OLEObjects("SalaryPaycheck").Activate
    Set objWord = GetObject(, "Word.Application")
    objWord.Visible = False
    Set objDoc = objWord.ActiveDocument
    objWord.Application.DisplayAlerts = wdAlertsNone
    objDoc.Application.ActivePrinter = "Adobe PDF on Ne06:"


    objDoc.PrintOut Background:=False


    objWord.Quit
    Set objDoc = Nothing
    Set objWord = Nothing
    Application.ScreenUpdating = True
    Exit Sub

End Sub 'Print it

The PrintOut opens a dialog box which asks about path and file name.

I want to made file name and path predefined so PrintOut runs quietly.

Upvotes: 1

Views: 1133

Answers (1)

Storax
Storax

Reputation: 12167

If you have an up-to-date Word version then you could export/save the file to a PDF document directly. Change your code to

Sub PrintIt()

    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim Oshp As Object
    Dim strCurrentPrinter As String

    ActiveSheet.OLEObjects("SalaryPaycheck").Activate
    Set objWord = GetObject(, "Word.Application")
    objWord.Visible = False
    Set objDoc = objWord.ActiveDocument
    objWord.Application.DisplayAlerts = wdAlertsNone
 '   objDoc.Application.ActivePrinter = "Adobe PDF on Ne06:"
 '   objDoc.PrintOut Background:=False

    Dim strOutFile As String
    strOutFile = "<filename>.pdf"

    objDoc.ExportAsFixedFormat OutputFileName:= _
        strOutFile, ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
        wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent



    objWord.Quit
    Set objDoc = Nothing
    Set objWord = Nothing
    Application.ScreenUpdating = True
    Exit Sub

End Sub

Upvotes: 1

Related Questions