Solved Export to PDF and Merge Another PDF

[MattBaldry]

Hi All,

Is there a way of exporting to PDF and then merging another PDF file to my report?

I have a code that exports my report to PDF then attaches it to an email. What I would like to do now is export it, merge it, then email the combined file.

Is this possible?

~Matt

 

[theDBguy]

Hi Matt. You could try this out.

UtterAccess.com

www.utteraccess.com

 

[ComputerVersteher]

I use PDFtk Server via commandline:

Public Function MergePDFDocuments(ByVal pdfMaster As String, ByVal pdfChild As String, ByVal pdfMerged As String) As Long
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Const pathToPDFtkExe As String = "path\to\pdftk.exe"
    Const dQuote As String = """" 'Doublequote to enclose paths in strings to escape blanks
    Dim cmd As String
    cmd = "cmd.exe /C " & dQuote & pathToPDFtkExe & dQuote & " " & dQuote & pdfMaster & dQuote & " " & dQuote & pdfChild & dQuote & " cat output " & dQuote & pdfMerged & dQuote
    Debug.Print cmd 'for debuging on a commandline
    Const windowStyleHidden As Long = 0 'hide Window
    Const waitOnReturn As Boolean = True 'wait till exec finished
    MergePDFDocuments = wsh.Run(cmd, windowStyleHidden, waitOnReturn) 'return Result 0 mean no Error, <> 0 is Error-Code
    Set wsh = Nothing
End Function

 

[sxschech]

ComputerVersteher has a great suggestion and it is free. I use pdftk Server too and it works well. Would like to add a couple of things about it.

If other people will be having their own copy of the database, you might want to add this to check if pdftk server is installed as otherwise when someone runs the merge code and doesn't have the software it won't work.

If InStr(Environ("Path"), "pdftk") = 0 Then
        MsgBox "It appears that PDF tool kit is not installed on this computer.  " & _
                "Please refer to user guide for instructions on how to install it. " & _
                "This program is needed in order to merge pdf files.", vbExclamation + vbOKOnly, "Missing Program PDFtk"
        Exit Function
End If

The other thing, at least in where I have used the mergepdf code is that if running additional vba after that which is dependent on the newly merged file (such as attaching it to an email or having it copied to the network after processing locally), I found that I need to keep the command window visible and put up a message box advising the user to click OK only after the command window closes (unless you can set up a timer, but then there are other issues since the timing of how long the merge takes will vary based on the number and size of the original files), otherwise, the code will continue to run and it will have an error because it can't locate the file if the merge hasn't completed prior to the next line of vba code being executed.

 

[ComputerVersteher]

I found that I need to keep the command window visible and put up a message box advising the user to click OK only after the command window closes

Use WScript.Shell and the .Run method with last parameter true and your code will pause till merge is finished. No need for user action or a timer!

 

[sxschech]

I had tried to use things like that over a year ago such as
'https://www.access-programmers.co.uk/forums/showthread.php?t=237688
and didn't work for me because the code continued running. Perhaps I didn't set it up properly which is why threw up my hands and went with a simple message box.

Here is one of the other code blocks I tried. Maybe I missed something?

Public Sub RunCmd(cmd As String)
    'this sub will execute a shell command and wait for execution to finish
    'https://stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete
    'https://www.accessforums.net/showthread.php?t=82817&p=489716#post489716
    '20220127
On Error GoTo ErrHandler_RunCmd

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    'Debug.Print cmd
    wsh.Run cmd, windowStyle, waitOnReturn
    
ExitHandler_RunCmd:
    Set wsh = Nothing
    Exit Sub
    
ErrHandler_RunCmd:
    Resume ExitHandler_RunCmd
End Sub

 

[ComputerVersteher]

Ok, I beautified my original code (and didn't test it )

Try:

wsh.Run cmd, 1, 1

maybe waitOnReturn isn't a boolean, but an integer (then True is converted to -1) .

 

[sxschech]

Thanks. I've been trying a few pdfs of various file sizes and so far your suggestion/edit is working. Appreciate it. One step closer to hands free automation.

 

[ComputerVersteher]

Now retry your code (with same cmd) as it works for me (suprisingly the docs are not wrong )

 

[MattBaldry]

Thanks all,

I shall give these a go when I am back in work.

~Matt

 

[sxschech]

Now retry your code (with same cmd) as it works for me (suprisingly the docs are not wrong )

Had a chance to try today and yes seems to work with the Boolean (original code) from my Post #6. Wonder if it has to do with my computer or windows/office version at the time I grabbed the code as it didn't work for me prior to your encouragement for another go at it. I have a new laptop which replaced a desktop which was well over 5 years old and that is the only easy explanation as to why it didn't appear to work before and works now?

 

[MattBaldry]

Hi Matt. You could try this out.

UtterAccess.com

www.utteraccess.com

Thank you for this link. I went with this and it works perfectly. Very simple and easy code once I downloaded the sample database.

Thank you for your help. This is now solved.

~Matt