Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Printing to PDF (VBA/Excel/2000)

    Hello All
    Is anyone aware of code which will control the pathname of the PDF file being generated?
    Regards
    Don

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,265
    Thanks
    2
    Thanked 187 Times in 173 Posts

    Re: Printing to PDF (VBA/Excel/2000)

    Don,
    How are you creating the PDF? Are you using the Adobe PDF printer driver or a third party version? If it's the Adobe version, do you have the PDFMaker.xla add-in for Excel?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing to PDF (VBA/Excel/2000)

    Hi Rory
    Thanks for the quick response. I am testing to determine the active printer, and obtaining confirmation from the user, that the correct one is active. I offer the code below. I'm running Acrobat 6 and the add-in PDFMaker.xla is attached.
    Now the cute part; I will be distributing this code in a workbook. I seek your advice on whether it will be necessary to require a specific version of Acrobat to be installed on the users' machines. Or indeed of other pitfalls which you might think of.

    <pre>Dim loDelimiter As Long
    Dim loPDF_Posn As Long
    Dim stMsgTxt As String
    Dim stPrinter As String
    Dim byPrinterResponse As Byte

    ' check the printer
    ResetPrinter:
    stPrinter = Application.ActivePrinter
    loDelimiter = InStr(1, stPrinter, " on ", vbBinaryCompare)
    loPDF_Posn = InStr(1, stPrinter, "PDF ", vbTextCompare)
    If loPDF_Posn > 0 And (loPDF_Posn = 1 Or InStr(1, stPrinter, " PDF ", _
    vbTextCompare) = loPDF_Posn - 1 And loPDF_Posn < loDelimiter) Then
    stMsgTxt = ""
    Else
    stMsgTxt = vbCrLf & "which appears to be incorrect for this procedure."
    End If
    byPrinterResponse = MsgBox("We are about to print the PDF files." & _
    vbCrLf & "The active printer is: " & vbCrLf & vbTab & stPrinter & _
    stMsgTxt & vbCrLf & vbCrLf & "If this is the correct printer, click ""Yes""" _
    & vbCrLf & "To change printers: Clck ""No""" & vbCrLf & _
    "To abort the ""PDF"" Print activity click ""Cancel""", 291, myTitle)
    If byPrinterResponse = 7 Then
    Application.Dialogs(xlDialogPrinterSetup).Show
    GoTo ResetPrinter
    ElseIf byPrinterResponse = 2 Then
    Exit Sub
    ElseIf byPrinterResponse = 6 And Len(stMsgTxt) > 0 Then
    If MsgBox("Are you certain that you want to print a PDF file with" & vbCrLf _
    & stPrinter & "?", 308, myTitle) = 7 Then GoTo ResetPrinter
    End If


    </pre>

    Regards
    Don

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing to PDF (VBA/Excel/2000)

    You may not be able to rely on the user having PDFMaker.xla installed.

    A safer approach may be to get a copy of the Acrobat SDK from Adobe and work via the SDK.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing to PDF (VBA/Excel/2000)

    Howard
    On reflection, I must agree.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •