PDF Merging Work Around with VBA for digital signatures (1 Viewer)

sxschech

Registered User.
Local time
Today, 03:48
Joined
Mar 2, 2010
Messages
791
Background:
Our department does not have Adobe Acrobat Pro. We are using Bluebeam Extreme. I have been successfully using PDFTkServer through VBA to automate the process of merging PDF files. Due to the current situation, there is now an increase in the number of digitally signed pdfs received via email or sent to a shared network drive for processing. I am unable to merge these directly with PDFtk or manually with Bluebeam. According to Bluebeam, this is the intended behaviour in order to avoid fraud. A colleague provided a manual work around by opening the PDF in Chrome Browser and click the print button and choose save as PDF. After doing this, I can then use PDFtk or Bluebeam as normal to merge the files.

Question:
Does anyone know if vba can open the pdf and save as via chrome (or some other way) directly so I don't have to stop the workflow and manually do those steps?

Secondary Question:
Might there be vba code that could detect whether the pdf is digitally signed so that I don't have to open and re-save every pdf if it doesn't have a signature? I thought I had found a method using pdftk, although it seemed to work on some test files, it failed when I started receiving new pdfs because it generated an error message that it was unable to open the file.
 

Isaac

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
Question:
Does anyone know if vba can open the pdf and save as via chrome (or some other way) directly so I don't have to stop the workflow and manually do those steps?
Could automate I.E. to do it instead. There might be a way to do so with Chrome, but there's probably more accumulated knowledge and articles on doing it with I.E.

Edit: About the other question. Here is a crazy sounding idea that just may work. First, I opened a PDF that I knew for sure was NOT signed in any way (created from doing a SaveAs, in Word, to save as PDF)...I opened it in Notepad as plain text. I could not find the text "sig" anywhere. Then, I opened a PDF that I knew for sure WAS digitally signed, also in Notepad as plain text. I found numerous occurrences of the text "signature". At least some of these occurrences appeared to be things that I think might be present in all digitally signed PDFs, like "Signature1" or "Type/SigFieldLock" or "Type/SigRef>"
I have no idea which of these (if any), is "always present" in digitally signed PDFs, but my point is that you could probably take 5 signed pdfs in your environment, and 5 unsigned pdfs, open them in notepad and see if there is some commonly occurring text - which almost certainly there is.
After that point it would be a snap to write some VBA function that opened a file as text and determined yes/no the presence of some text therein.

That might be the crudest method that nobody's ever heard of, but you see a lot of fascinating stuff by opening random files in Notepad and looking at the plain English portions. Worth a try?

HTH.
 
Last edited:

sxschech

Registered User.
Local time
Today, 03:48
Joined
Mar 2, 2010
Messages
791
Hi Isaac. Thanks for the suggestions. I will take a look into the ie and notepad. Your notepad suggestion is similar to what pdftk did as it had a command to search for the Signature field and save that to a text file, so maybe if I use your method, the file will open via vba whereas the pdftk seemed to have a problem opening some of the files.
 

Isaac

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
Hi Isaac. Thanks for the suggestions. I will take a look into the ie and notepad. Your notepad suggestion is similar to what pdftk did as it had a command to search for the Signature field and save that to a text file, so maybe if I use your method, the file will open via vba whereas the pdftk seemed to have a problem opening some of the files.
There are a couple ways to read text in VBA but if you just fire up a Scripting.Filesystemobject object, then set a Textstream object to the OpenTextFile (for reading) method, that should work. Good luck & let us know how you get on!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
21,358
Just in case it helps, here's some examples for reading text files. Cheers!

 

sxschech

Registered User.
Local time
Today, 03:48
Joined
Mar 2, 2010
Messages
791
Thanks. May not get to this until next week now due to a family issue. Will let you guys know the result once I have a chance to return to this.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
21,358
Thanks. May not get to this until next week now due to a family issue. Will let you guys know the result once I have a chance to return to this.
Good luck, on both counts.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:48
Joined
May 7, 2009
Messages
19,175
im not sure if this will work on your Merging of pdfs.
Mr.Albert Kallal of UA, post this .dll for merging pdfs.
see if it can do the job.
 

Attachments

  • PDFMerge.zip
    761.8 KB · Views: 437

sxschech

Registered User.
Local time
Today, 03:48
Joined
Mar 2, 2010
Messages
791
Hi TheDBGuy, had a chance to try the code from your link and it doesn't seem to find the keyword "Signature". However, after looking at the text via notepad and doing a find in there as suggested by Isaac, the keyword shows up not only in the original pdf but in the one that I processed via chrome. This would mean that potentially, I wouldn't be able to rely on this as a test of whether the file needs further processing in order to be combinable, unless there is some other indicator I can go by that we are not currently aware of.

Hi arnelgp, thanks for the zip file. I didn't actually try it because looks like there is a bat file and registry stuff and don't want to get flagged by IT. Also, I did open the pdf files provided in the zip and doesn't look like these have been digitally signed and since the issue isn't with combining regular pdfs that was another reason.

Hi Isaac, tried the code in the link you provided and got Run-time error '462' The remote server machine does not exist or is unavailable. Stopped at this line: Do Until ie.ReadyState = 4
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
21,358
Hi TheDBGuy, had a chance to try the code from your link and it doesn't seem to find the keyword "Signature". However, after looking at the text via notepad and doing a find in there as suggested by Isaac, the keyword shows up not only in the original pdf but in the one that I processed via chrome. This would mean that potentially, I wouldn't be able to rely on this as a test of whether the file needs further processing in order to be combinable, unless there is some other indicator I can go by that we are not currently aware of.

Hi arnelgp, thanks for the zip file. I didn't actually try it because looks like there is a bat file and registry stuff and don't want to get flagged by IT. Also, I did open the pdf files provided in the zip and doesn't look like these have been digitally signed and since the issue isn't with combining regular pdfs that was another reason.

Hi Isaac, tried the code in the link you provided and got Run-time error '462' The remote server machine does not exist or is unavailable. Stopped at this line: Do Until ie.ReadyState = 4
Hi. Thanks for the update and sorry to hear it didn't work for you.
 

Isaac

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
Hi TheDBGuy, had a chance to try the code from your link and it doesn't seem to find the keyword "Signature". However, after looking at the text via notepad and doing a find in there as suggested by Isaac, the keyword shows up not only in the original pdf but in the one that I processed via chrome. This would mean that potentially, I wouldn't be able to rely on this as a test of whether the file needs further processing in order to be combinable, unless there is some other indicator I can go by that we are not currently aware of.

Hi arnelgp, thanks for the zip file. I didn't actually try it because looks like there is a bat file and registry stuff and don't want to get flagged by IT. Also, I did open the pdf files provided in the zip and doesn't look like these have been digitally signed and since the issue isn't with combining regular pdfs that was another reason.

Hi Isaac, tried the code in the link you provided and got Run-time error '462' The remote server machine does not exist or is unavailable. Stopped at this line: Do Until ie.ReadyState = 4
Hi @sxschech
Personally I still think that there will be some identifiable difference in the keywords in the files. I suggested several that seemed to be present in my test digitally signed file, (but I realize it may be different for you), but, I acknowledge that the hardest part would be discovering what those keywords are. I'm not at all sure that they will be the text "signature". There are programs out there that do text comparisons. Perhaps you could use them to compare 2 specific files: Create each file from Word saveas PDF, so you know for sure they are both not digitally signed. Then use some tool on one of them to add a signature. Then compare those 2 files. (This way the files have minimal content - keep it to a single word, in the original Word run) for you to eyeball, (or for you to use a text editor to compare content differences).

As for the I.E. code, can you post the full code you are using? I have seen that error before, always in instances where CreateObject("OfficeProgramName.Application") is being used, and unfortunately, have not found very many reliable solutions for it. I do know that it can occur if the code is deployed on a remote machine, but, if that were the case I think the error would have presented prior to the line of code you referenced.
 

sxschech

Registered User.
Local time
Today, 03:48
Joined
Mar 2, 2010
Messages
791
Isaac, here is the code I tried:
Code:
Sub View_Tech_Recalls()
'https://www.mrexcel.com/board/threads/using-a-macro-to-open-then-print-a-webpage.596387/
'https://www.access-programmers.co.uk/forums/threads/pdf-merging-work-around-with-vba-for-digital-signatures.314065/post-1722818
'20200930
    Dim ie As Object
    Dim TimeOutWebQuery As Integer
    Dim TimeOutTime As Date
    Dim waituntil  As Date
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate "C:\Users\Downloads\2020.08.24-053-LTR-Notification-Rev0.pdf" 'Range("Recall_URL")
    ie.statusbar = False
    ie.toolbar = True
    ie.Visible = True
    ie.Resizable = True
    ie.AddressBar = False
    
    
    TimeOutWebQuery = 5
    TimeOutTime = DateAdd("s", TimeOutWebQuery, Now)
    Do Until ie.ReadyState = 4
        DoEvents
        If Now > TimeOutTime Then
            ie.stop
            GoTo ErrorTimeOut
        End If
    Loop



    ie.ExecWB 6, 2
    'Application.Wait (Now + TimeValue("0:00:03"))
     waituntil = Now + TimeValue("00:00:03")
   Do
      DoEvents
   Loop Until Now >= waituntil
    
    

ErrorTimeOut:

    
    Set ie = Nothing
End Sub
 

Users who are viewing this thread

Top Bottom