PDF Redirect software no longer works

[BallunarCrew]

I have been using PDF Redirect VBA code to merge files from an array into one PDF file. It has been pretty straight forward code provided in samples from the company, EXP Systems, and has been working for many years. All of a sudden it no longer works inside the Access database. I have downloaded the most up to date version of the software but it is from 2010. I am wondering if anyone knows what is a solution to this issue. I have gone back to older, backup copies of the database and it does not work there either. This started about 8-22-2020. I have emailed the company with no response. The standalone software runs fine on my desktop and I can merge the files that way so I suspect Microsoft has changed something and the PDF Redirect software no longer works with Access. I have researched alternatives and about the only thing I am seeing is Acrobat Professional and a cost of about $15 a month per seat it is a little cost prohibitive for my little office. This database is in Office 365 Access.

 

[sxschech]

There is a free tool called pdftkserver which allows you to use vba to send command line instructions for merging, separating and doing many other things with pdf files. If you decide to use it, I can post a bit of example code that I used.

https://www.pdflabs.com/tools/pdftk-server/

 

[BallunarCrew]

Thanks for the link sxschech. I am looking at their website right now. A code sample would be great especially if they do not have anything on their site.

 

[BallunarCrew]

I have downloaded pdftk software and it works from the command prompt.
C:\Users\ballu>pdftk F:\PatientScans\DeaMar\FirstFile.pdf F:\PatientScans\DeaMar\SecondFile.pdf cat output DeaMar_Merge_10182020.pdf
Calling it from the VBA code is not working though. I am getting errors - unable to find file then Unable to open PDF file. I am not doing the shell command right I think.
Dim TempBool
Dim MergeCmdString as String
MergeCmdString = "pdftk F:\PatientScans\DeaMar\Merged.pdf F:\PatientScans\DeaMar\ThirdFile.pdf cat output F:\PatientScans\DeaMar\DeaMar_Merge_10182020.pdf"
TempBool = Shell(MergeCmdString, vbMinimizedNoFocus)

I have copied the text that I am setting to the MergeCmdString variable exactly, excluding the quotes, to the command prompt and it works fine. F: is mapped to a local hard drive folder.

 

[sxschech]

Hope it can wait until I get into the office on Monday the 19th.

 

[sxschech]

Here is some code. This isn't the complete code, just the relevant parts. If you need more than this let me know.

The first code isn't really necessary, however, it may be of use to notify if pdftk is not installed so that the code won't error out in such a case.

    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

This code uses a variable to store the command line, so if you prefer to insert the command line directly, that would go in the stMergeFiles slot of the code in the shell command. I added "M" to the name to indicate it was the merged version. Then later on the code would remove the M. I'm using a comma separated list for the files to be merged and that is why there is a split function. Using [tt]Chr(34)[/tt] to deal with quote issue.

[tt]stMergeFiles = Chr(34) & CustomSplit(pdfname, ",", 1) & Chr(34) & " " & Chr(34) & CustomSplit(pdfname, ",", 2) & Chr(34) & " cat output " & Chr(34) & Replace(CustomSplit(pdfname, ",", 1), ".pdf", "M.pdf" & Chr(34))[/tt]

On my system, sometimes files would take longer to merge and to prevent the code from continuing, I put a message box to prevent that and then click OK once the black screen of the shell command has closed. If you happen to know how to identify when the shell command is done, then you wouldn't need the message box. I haven't figured that part out.

[tt]Dim stMergeFiles As String
Dim retVal As Variant
[/tt]
Here is the shell command

retVal = Shell("pdftk " & stMergeFiles, vbMaximizedFocus)
MsgBox "Please wait for pdftk 'BLACK SCREEN' to close and then press OK to continue." & vbCrLf & vbCrLf & "Note:Big files may take longer to merge.  Please be patient", vbOKOnly + vbExclamation, "Files Merged"
DoEvents

Here is the custom split function

Public Function CustomSplit(strField As String, delim As String, intPosition As Variant) As String
    If InStr(strField, delim) > 0 Then
    On Error GoTo errcheck
        CustomSplit = Split(strField, delim)(intPosition)
    End If
errcheck:
    'Debug.Print Err.Number & Err.Description
End Function

 

[BallunarCrew]

Thanks for this information - I almost have it working the way I need it to. It appears that I can't specify a folder for the input or output files - just the file names and defaulted to the Documents folder. Is this your experience as well? I need to put that file in a specific user's folder. I know I can write code to move it to the correct folder.

 

[sxschech]

You should be able to specify the files and locations as needed. Not sure how you have set up your code that would cause such a limitation. Maybe you don't have the files quoted properly?

Example:
[tt] "C:\Users\Downloads\First Page File.pdf" "C:\Users\October2020\Attach to Second Page.pdf" "C:\Users\Downloads\Attach to Third Page.pdf" cat output "C:\Exports\Merged PDF 3 Pager.pdf"[/tt]