Do I need a vba-access code to insert digital signature (digital certificate) in a report, Someone help!?

[clebergyyn]

There's this other code here too, below. Also made for Excel-Vba.

Sub DigitalSignedPDFCode()
Dim SignedPDF, FileCount, PFX_pass, strSignFName, inputpdf, epass, outputpdf
Dim pdfPDDoc As New AcroPDDoc, oJS As Object, oSign As Object, oPpklite As Object
Dim oSignInfo As Object
Dim ResultLogin As Boolean
Dim ResultSign As Boolean
Dim arr() As String
Set pdfPDDoc = CreateObject("AcroExch.PDDoc")
Set oJS = pdfPDDoc.GetJSObject
Set oPpklite = oJS.security.getHandler("Adobe.PPKLite", True)
            
strSignFName = "PFX CERTIICATE PATH"
PFX_pass = "PFX PASSWORD"
inputpdf = "PDF FILE FOR SIGN"
outputpdf = "PDF FILE AFTER SIGNED"             
              
'Page 1
Set oFields = oJS.AddField("SignatureField", "signature", 0, Array(80, 220, 255, 250))
Set oSign = oJS.GetField("SignatureField")
ResultLogin = oPpklite.login(PFX_pass, strSignFName)
oPpklite.setPasswordTimeout PFX_pass, 60
arr = Split("", ",")
ResultSign = oSign.signatureSign(oPpklite, arr, outputpdf)

oPpklite.logout
pdfPDDoc.Close
Set oJS = Nothing
Set oSign = Nothing
Set oPpklite = Nothing
Set pdfPDDoc = Nothing

End Sub

 

[GPGeorge]

What did you try?
What, precisely, did you expect to happen?
What, exactly, did happen instead?

We know you want to digitally sign a pdf. You said what you had tried "...doesn't work...", but what we're still missing is the part where you explain what happened when you tried to run that code. An error? Nothing?

 

[theDBguy]

The function gives an error in this code: "Dim pdfPDDoc As New AcroPDDoc".

Hi. Let's clarify something first. Based on the error message you received earlier, could you please tell us if you or don't you have the full version of Adobe Acrobat Pro installed on your machine? Thank you.

 

[Galaxiom]

Only thing I couldn't get to work in Excel VBA that worked in Access VBA was Nz() function. Even a query object that used Nz() had to be modified. Maybe I didn't have a necessary library referenced in Excel.

Nz() is an unusual function because it a Method of the Access Application Object rather than being in the database engine or VBA.

Most built in functions used in queries are in the database engine so easily applied even when the query is run from Excel. VBA functions can also be used but the Application Object is out of scope when a query is called from Excel. That is why Nz() doesn't work properly in an Access query called from Excel.

BTW Some developers do not realise there are both VBA and engine versions of most built in functions in Access and assume that the query is calling the VBA function. The clearest evidence that they are different is Iif(). Both the True and False arguments are evaluated in VBA Iif() while the engine version only evaluates the True argument.

 

[June7]

Right, the engine IIf() only evaluates the False argument when the True condition is not met.

In the VBA IIf() if the False argument has an invalid expression, the entire IIf() expression will fail.

 

[KitaYama]

There's this other code here too, below. Also made for Excel-Vba.

@clebergyyn I know nothing about the signature you're talking about. So I stayed away. Since you don't have a working suggestion yet, it's my 2 cents.

The following code is written for Access.
It Opens a pdf file, adds a text and save it back. I use it on a daily basis. I don't have a signed signature to test it for you. So you may want to cut the signature section off the Excel code and add it to the following.

PDF uses JavaScript for automating tasks. The following code creates a JavaScript code and runs it.

Public Sub AddFieldToPdf(ThisPDF As String, FieldText As String)
    
    Dim App As Object
    Dim AVDoc As Object
    Dim AForm As Object
    Dim js As String
    Dim Msg As String

    Set App = CreateObject("Acroexch.app")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    Set AForm = CreateObject("AFormAut.App") 'from AFormAPI

    If AVDoc.Open(ThisPDF, "") Then
        '// write some js code on a vbs variable
        js = ""
        js = js & "f = this.addField(""Sig" & """, ""text"", 0, [20,10,250,50]);" & vbLf _
        & "f.Value = """ & FieldText & """; " & vbLf _
        & "f.textColor = color.Black;" & vbLf _
        & "f.textFont = Font.ZapfD;"
        '//execute the js code
        AForm.Fields.ExecuteThisJavaScript js 
        App.MenuItemExecute ("Save")
      
    End If

End Sub

You can run the above procedure with something like :

AddFieldToPdf "D:\MyFile.pdf","Add this text"

 

[clebergyyn]

Hi. Let's clarify something first. Based on the error message you received earlier, could you please tell us if you or don't you have the full version of Adobe Acrobat Pro installed on your machine? Thank you.

I will install Adobe Acrobat Pro as soon as I get the code

 

[theDBguy]

I will install Adobe Acrobat Pro as soon as I get the code

Which code are you waiting for? Didn't you already get it from posts #12, #18, #21, and #26?

 

[clebergyyn]

Which code are you waiting for? Didn't you already get it from posts #12, #18, #21, and #26?

The function I need is similar to the last one I posted for Excel-Vba. Only I need for Access-vba. I'm waiting for this function. I will purchase the Acrobat Reader Pro subscription.

 

[June7]

As already stated, code should work with Access as well as Excel when you have Adobe Acrobat Pro installed (not Reader).

 

[KitaYama]

As already stated, code should work with Access as well as Excel when you have Adobe Acrobat Pro installed (not Reader).

It doesn't. I don't know why. But it doesn't.
I don't know it's a different between Excel & Access, or because of a change in libraries etc.
But most of what has been offered doesn't work in Access 365.

I have acrobat pro and searched for more than two months to find a way to add a text to a pdf file in Access.
I even submitted several tickets in Adobe's official forum, but it was in vain. At last I had to mix several cods to be able to add a text.
All web is filled with codes for excel and none of them worked in Access. It maybe because of a library or something that makes Excel and Access different.

This is your offered code in Access 365 :
The first Dim shows this error.

 

[theDBguy]

It doesn't. I don't know why. But it doesn't.
I don't know it's a different between Excel & Access, or because of a change in libraries etc.
But most of what has been offered doesn't work in Access 365.

I have acrobat pro and searched for more than two months to find a way to add a text to a pdf file in Access.
I even submitted several tickets in Adobe's official forum, but it was in vain. At last I had to mix several cods to be able to add a text.
All web is filled with codes for excel and none of them worked in Access. It maybe because of a library or something that makes Excel and Access different.

This is your offered code in Access 365 :
The first Dim shows this error.

View attachment 99860

Hi. Not sure if we're not being clear, but that code is not working for you because you don't have the full version of Acrobat installed on your computer. Once you install the full version of Adobe Acrobat, that code should start working for you. Is that clearer?

 

[KitaYama]

Not sure if we're not being clear, but that code is not working for you because you don't have the full version of Acrobat installed on your computer.

I have. I have pro on all our PCs here.
My own code #26 works for me and my other PCs. but not @June7 's. And also Not a lot of other code I found by Google.
I've tested it on more than 10 PCs. But the result is always the same.
I really don't know why. But for me, it's what it is.

 

[arnelgp]

i have error too on this line (tried acrobat 2020 pro dc) and acrobat pro XI.

 

[KitaYama]

Can it be a reason?
Every site I see, (even @June7 's code) asks to have a reference to Adobe Acrobat 6.0 Type Library.
But I have this.
Maybe it's because of the version of the library.

 

[June7]

I am sure the library has been revised over the years. I developed my code over 10 years ago.

 

[cheekybuddha]

Right, the engine IIf() only evaluates the False argument when the True condition is not met.

In VBA, both parts are evaluated irrespective of the result of the test.
In a query IIf only evaluates the portion required.

Try in Immediate Window:

?IIf(True, 1, 1 / 0)

Run-time error '11':
Division by zero

But in a query:

SELECT TOP 1
  IIf(True, 1, 1 / 0) AS test
FROM SomeTable;

No error

 

[June7]

@cheekybuddha, was that expanded explanation intended for me?
I know and agree, thought that's what I said.

 

[cheekybuddha]

was that expanded explanation intended for me?

For anyone really - just pointing out the different behaviour of IIf() when used within VBA and when used in queries - it's inconsistent

(It wasn't clear to me whether that was the point you were making - apologies if it looked like I was trying to teach granny to suck eggs)

 

[isladogs]

David
Whilst that is true for the example you gave, IIf evaluates both components where a table field is included in the IIf expression.
For example:

SELECT Field1, IIf(Field1="Y",1,1/0) AS test
FROM Table1;