Results 1 to 3 of 3

Thread: PDF Creator wont close if cell equals file name

  1. #1

    PDF Creator wont close if cell equals file name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all need a little help. Using Ken's macro for single sheet pdf creation. It works great until I make the file name equal a cell. Once I do that the pdfcreator wont end process. Any help would be great, here is the code I have so far:

    [CODE][Sub PDF()
    'Macro Purpose: Print to PDF file using PDFCreator
    ' Designed for late bind, no references req'd
    Dim pdfjob As Object
    Dim sPDFName As String
    Dim sPDFPath As String
    '/// Change the output file name here! ///
    'sPDFName = "test this blows.pdf"
    sPDFName = Worksheets("STOP ORDER").Range("J5").Value
    sPDFPath = "O:\Departments\Engineering\Shared\New Engineer\Temporary Shared Files\STOP-Lift Orders" & Application.PathSeparator
    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
    With pdfjob
    If .cStart("/NoProcessingAtStartup") = False Then
    MsgBox "Can't initialize PDFCreator.", vbCritical + _
    vbOKOnly, "PrtPDFCreator"
    Exit Sub
    End If
    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = sPDFPath
    .cOption("AutosaveFilename") = sPDFName
    .cOption("AutosaveFormat") = 0 ' 0 = PDF
    .cClearCache
    End With
    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
    DoEvents
    Loop
    pdfjob.cPrinterStop = False
    'Wait until the file shows up before closing PDF Creator
    Do
    DoEvents
    Loop Until Dir(sPDFPath & sPDFName) = Worksheets("STOP ORDER").Range("J5").Value

    pdfjob.cClose
    Set pdfjob = Nothing
    End Sub

    /CODE]

  2. #2
    Try this ... it works on my end but you know how that goes. I had to work around this same issue several months back.

    Code:
    Sub PDF()
    Dim sPDFName As String
    Dim sPDFPath As String
    '/// Change the output file name here! ///
    'sPDFName = "test this blows.pdf"
    sPDFName = Worksheets("STOP ORDER").Range("J5").Value
    sPDFPath = "O:\Departments\Engineering\Shared\New Engineer\Temporary Shared Files\STOP-Lift Orders" & Application.PathSeparator
    '"C:\Departments" & Application.PathSeparator
    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
    With pdfjob
    If .cStart("/NoProcessingAtStartup") = False Then
    MsgBox "Can't initialize PDFCreator.", vbCritical + _
    vbOKOnly, "PrtPDFCreator"
    Exit Sub
    End If
    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = sPDFPath
    .cOption("AutosaveFilename") = sPDFName
    .cOption("AutosaveFormat") = 0 ' 0 = PDF
    .cClearCache
    End With
    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
    DoEvents
    Loop
    pdfjob.cPrinterStop = False
     
     'Wait until PDF creator is finished then release the objects
        Do Until pdfjob.cCountOfPrintjobs = 0
            DoEvents
        Loop
     
    pdfjob.cClose
    Set pdfjob = Nothing
    End Sub

  3. #3
    Thanks Tommy, I made that change plus added a 3 sec time delay works perfect now. Now off to try and get this pdf emailed using Lotus Notes.

Tags for this Thread

Posting Permissions

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