How to select items in listbox field from VBA

[Bruno69]

Good evening,

I’d be grateful if anyone could help in my problem.

I have to fill out the fields of a administrative pdf forms from Access (where I have my database), under VBA. I have no problems with the main part of the fields, except with listboxes having a multiple choice selection.

I put my code below, and the form for example is attached. Even if I send a sorted array of integers to set the currentValueIndices, I get the error code 13 : type incompatibility. The array type is 8194 (array 8192 + integer 2), and all my values are integer.

Could anyone help to understand how to pass the array to the pdf listbox field and set the selected items, please.

Many thanks

Bruno

from Adobe Acrobat SDK

«To set a multiple selection for a list box that allows it, pass an array as argument to this property, containing the indices (sorted in ascending order) of those strings in the options array. » 

    Dim apPDDoc, apVisuPDDoc, formAcrob, javscrobj As Object
    Dim champTravail As Object
    Dim nomFichOr, nomFichDest As String
    Dim Cpt, nbLangagesSel As Integer
    Dim valListBox() As Integer
    
    Dim chLangage As String
    chLangage = "PYT;JAV;CPP" ' Valeurs possibles PYT JAV PAS CPP VBA XML
    
    Dim langagesList As Object
    Set langagesList = CreateObject("Scripting.Dictionary")
    langagesList.Add "CPP", 0
    langagesList.Add "CPP_l", "C/C++"
    langagesList.Add "JAV", 1
    langagesList.Add "JAV_l", "Java"
    langagesList.Add "PAS", 2
    langagesList.Add "PAS_l", "Pascal"
    langagesList.Add "PYT", 3
    langagesList.Add "PYT_l", "Python"
    langagesList.Add "VBA", 4
    langagesList.Add "VBA_l", "VBA"
    langagesList.Add "XML", 5
    langagesList.Add "XML_l", "XML"
    
    nomFichOr = "C:\Bruno\ManipPdfAccess\Form_Travail_remplLstbox.pdf"
    nomFichDest = "C:\Bruno\ManipPdfAccess\Form_Travail_remplLstbox_rempli.pdf"
    
    Set apPDDoc = CreateObject("AcroExch.PDDoc")
    Set apVisuPDDoc = CreateObject("AcroExch.AVDoc")
        
    If apVisuPDDoc.Open(nomFichOr, "") Then
        Set formAcrobat = apVisuPDDoc.GetPDDoc()
        Set javscrobj = formAcrobat.getJSObject
    
        Set chTravail = javscrobj.getfield("chLangage")
        ' Extraction des langages sélectionnés
        ' Extraction of the selected values from chLangage
        nbLangagesSel = (Len(chLangage) + 1) / 4
        
        ReDim valListBox(0 To nbLangagesSel - 1)
        
        For Cpt = 0 To nbLangagesSel - 1
            ' Va chercher dans le dictionnaire l'indice de liste associé au langage à sélectionner
            ' Fills the array with selected items, through the dictionnary
            valListBox(Cpt) = CInt(langagesList.Item(Mid$(chLangage, 4 * Cpt + 1, 3)))
        Next
        
        ' Tri du tableau, en principe par une fonction appelée (array sort, done by a function)
        ' Sorting the array, normaly made by function call
        Dim i, temp As Integer
        Dim finTri, pasDeModif As Boolean
        finTri = False
        pasDeModif = True
        While Not finTri
            For i = 0 To UBound(valListBox) - 1
                If valListBox(i) > valListBox(i + 1) Then
                    temp = valListBox(i)
                    valListBox(i) = valListBox(i + 1)
                    valListBox(i + 1) = temp
                    pasDeModif = False
                End If
            Next i
            If pasDeModif Then finTri = True Else pasDeModif = True
        Wend
        
        ' Là, erreur 13, c'est pourtant tableau d'integer
        ' Here there is error code 13 type incompatibility
        chTravail.currentValueIndices = valListBox()
        formAcrobat.Save 1, nomFichDest
    Else
        MsgBox ("Pas de document de ce nom disponible")
    End If
    
    
    Set chTravail = Nothing
    Set javscrobj = Nothing
    Set formAcrobat = Nothing
    Set apVisuPDDoc = Nothing
    Set apPDDoc = Nothing

[Bruno69]

I found a solution, using an ExecuteThisJavaScript on a Fields object from AFormAut.App, but its not a very elegant one.

Is it a possibility to pass the array directly through the jsobject getfield ?

Many thanks

Bruno

[ls_rbls]

From your code posted earlier, it is saying in French about the error with  the use of Integers in the array.

It fails to explain that you must not declare with your variables that will be processed with integer number values  using string values.

So it is possible to pass the array as long as you fix that with the following recommendations:

• error code 13 : type incompatibility. The array type is 8194 (array 8192 + integer 2) 

• https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/type-mismatch-err... 

[Bruno69]

Thanks ls_rbls for your answer.

I'm not sure I understood all what you recommended, I tried to declare my array as an long values one, then declare it without any other indication (Dim valListBox() -> becomes an array of variant). Unfortunately, it didnt fix my problem.

Its written in Adobe Acrobat SDK that you must "pass an array as argument to this property, containing the indices (sorted in ascending order) of those strings in the options array" and "It accepts a single integer or array of integers as an argument".

I can understand where it does fail.

In another answer, I put the "solution" I found, not very elegant, but working.

[Bruno69]

Here is the whole code which does work.

A bit heavy, I 'll try to make it lighter.

I'm still interested if somebody has another solution.

    Dim apPDDoc, apVisuPDDoc, visuPDFDoc, javscrobj As Object
    Dim champTravail, champsduPDF, formAcrobat As Object
    Dim nomFichOr, nomFichDest, cpltJS As String
    Dim Cpt, nbLangagesSel As Integer
    Dim valListBox()
    
    Dim chLangage As String
    chLangage = "PYT;JAV;CPP" ' Valeurs possibles PYT JAV PAS CPP VBA XML
    
    ' Set the dictionnary
    Dim langagesList As Object
    Set langagesList = CreateObject("Scripting.Dictionary")
    langagesList.Add "CPP", 0
    langagesList.Add "CPP_l", "C/C++"
    langagesList.Add "JAV", 1
    langagesList.Add "JAV_l", "Java"
    langagesList.Add "PAS", 2
    langagesList.Add "PAS_l", "Pascal"
    langagesList.Add "PYT", 3
    langagesList.Add "PYT_l", "Python"
    langagesList.Add "VBA", 4
    langagesList.Add "VBA_l", "VBA"
    langagesList.Add "XML", 5
    langagesList.Add "XML_l", "XML"
    
    nomFichOr = "C:\Bruno\ManipPdfAccess\Form_Travail_remplLstbox.pdf"
    nomFichDest = "C:\Bruno\ManipPdfAccess\Form_Travail_remplLstbox_rempli.pdf"
    
    Set apPDDoc = CreateObject("AcroExch.PDDoc")
    Set apVisuPDDoc = CreateObject("AcroExch.AVDoc")
        
    If apVisuPDDoc.Open(nomFichOr, "") Then
        Set visuPDFDoc = apVisuPDDoc.GetPDDoc()
        Set javscrobj = visuPDFDoc.getJSObject
        Set formAcrobat = CreateObject("AFormAut.App")
        Set champsduPDF = formAcrobat.Fields
    
        Set chTravail = javscrobj.getfield("chLangage")
        ' Extraction des langages sélectionnés
        ' Extraction of the selected values from chLangage
        nbLangagesSel = (Len(chLangage) + 1) / 4
        
        ReDim valListBox(0 To nbLangagesSel - 1)
        
        For Cpt = 0 To nbLangagesSel - 1
            ' Fills the array with selected items, through the dictionnary
            valListBox(Cpt) = CInt(langagesList.Item(Mid$(chLangage, 4 * Cpt + 1, 3)))
        Next
        
        ' Sorting valListBox, normally done by an array sorting function : valListBox = triTableau(valListBox)
        Dim i, temp As Integer
        Dim finTri, pasDeModif As Boolean
        finTri = False
        pasDeModif = True
        While Not finTri
            For i = 0 To UBound(valListBox) - 1
                If valListBox(i) > valListBox(i + 1) Then
                    temp = valListBox(i)
                    valListBox(i) = valListBox(i + 1)
                    valListBox(i + 1) = temp
                    pasDeModif = False
                End If
            Next i
            If pasDeModif Then finTri = True Else pasDeModif = True
        Wend
        
        ' Setting value sequence using AFormAut.App and JS : does work
        cpltJS = "["
        For Cpt = 0 To UBound(valListBox)
            cpltJS = cpltJS & Trim(CStr(valListBox(Cpt))) & ","
        Next
        cpltJS = Left$(cpltJS, Len(cpltJS) - 1) & "]"
        
        jscriptInstr = "f = this.getField(""chLangage"");" & vbLf & "f.currentValueIndices = " & cpltJS
        champsduPDF.ExecuteThisJavaScript jscriptInstr
        
        visuPDFDoc.Save 1, nomFichDest
    Else
        MsgBox ("Pas de document de ce nom disponible")
    End If
    
    Set champsduPDF = Nothing
    Set formAcrobat = Nothing
    Set chTravail = Nothing
    Set javscrobj = Nothing
    Set visuPDFDoc = Nothing
    Set apVisuPDDoc = Nothing
    Set apPDDoc = Nothing

[ls_rbls]

Here is an old thread that also complements this discussion:

http://khkonsulting.com/2009/03/adobe-acrobat-and-vba-an-introduction/ 

[Thom Parker]

Objects in VBA are different from objects in JavaScript. There is some level of compatibility provided by the JSO interface, but the fact is that you cannot reliably pass data in objects from VBA to the Acrobat JavaScript engine. Arrays are of course a type of object. But you also have the compound problem of assigning an object value (the index array) to a property of a JavaScript field object acquired through getField. Field objects are built-in JavaScript objects. it's hard enough to think about handling generic objects across the JSO boundary, But this is quite complex and I'm sure resolving the object compatibilities is way beyond the abilities of JSO. There are other inefficiencies with this interface as well.

As a general practice, when writing in .NET or VBA I keep interactions with the JSO to a minimum, and only pass arguments as strings or integers. 

For any kind of complex operation, or anything involving object related properties on the JavaScript side, I create folder level JavaScript functions to perform the majority of the work and then call the function from the JSO.  For example VBA code should never act directly on a built-in JavaScript object from within VBA.  

These techniques will fix your issue in the most robust and efficient way.  

[Bruno69]

Thanks Thom for your answer,

Do you mean that the technique I used finally with the .ExecuteThisJavaScript is not the right one ? I'm not a specialist of JS and have a lot to learn.

[Thom Parker · Accepted solution]

Using "ExecuteThisJavaScript" is a very similar approach. It moves the main action into the JavaScript engine and passes parameters as a string. However, it is not as clean, robust, or flexible as using the same code in a Folder Level JavaScript function.   

The Folder level function approach allows you to refine the Acrobat side functionality separately, and within the Acrobat environment. I would place everything that needs to be done in Acrobat in this function (or functions) so that there is a clear separation between the Acrobat functionality, and the Access functionality. Only use the JSO to pass data into the Acrobat side folder level functions. 

[Bruno69]

Many thanks, Thom !

I'll work that.

[Bruno69]

Good evening Thom,

I wish I'll not make you waste your time, I know this is a solved post, but I dont know if I have to create a new one for that question.

You tell to create folder level JS functions (I understand xxx.js file into the app javascript folder) and call those functions from JSO. I found another post in which your answered how to call such  a function from jso (https://answers.acrobatusers.com/Calling-Acrobat-Folder-Level-JS-File-Function-VB-q97064.aspx).

But I dont manage do find a tutorial or a reference into Acrobat instructions manuals I have, in order to understand the way to create and use this kind of functions.

Do you wrote someting about that ? 

I read your post (https://acrobatusers.com/tutorials/folder_level_scripts/) and could create a general folder level script, but I cant find how to create one I can call as a function from VBA.

Many thanks in advance if you can send me some indications.

Bruno

[Thom Parker]

All folder level function are globally visible in the Acrobat scripting environment. 

 So if you define a folder level function like this.

function MyFunction(strInput)

{

   return strOutput;

}

Then you call it from VB like this.

Set jso = gPDDoc.GetJSObject
cRtn = jso.MyFucntion("Hello");

[Bruno69]

Dear Thom,

Many many thanks for your answer. I'm somewhat ashamed because, with your indications, I saw that melting several sources, I'd be able to find the solution by myself, but... I wasn't.

Perhaps, for such numbskulls as I am, could you add the function definition procedure to https://answers.acrobatusers.com/Calling-Acrobat-Folder-Level-JS-File-Function-VB-q97064.aspx (where the solution was quite complete).

Your indications allowed me to solve entirely my problem, I'm very grateful.

Warm regards.

Bruno

[Thorsten22170981z1ds]

I alot want to send a big huge Thanks to Thom Parker for this !

This saved my Project :

folder level function

Thanks Thom Parker , you really made my day.

I can now use VBA->read from SQL Database and finaly generate pdf files and fill boxes and comboboxes!!!

function ClearingCombobox(Whichone)
{
this.getField(Whichone).clearItems();
};

function addComboboxItems(Whichone,myName)
{
var oFld = this.getField(Whichone);
var bFound = false; for(var index=0;index<oFld.numItems;index++) {
if(oFld.getItemAt(index,false) == myName) {
bFound = true; break;
}
} if(!bFound) {
oFld.insertItemAt({cName:myName, nIdx:-1});
}
};

Calling it from VBA:

Dim exPDF
Dim AcroApp As Acrobat.CAcroApp
Dim theForm As Acrobat.CAcroPDDoc
Dim jso As Object

Set AcroApp = CreateObject("AcroExch.App")
Set theForm = CreateObject("AcroExch.PDDoc")

Set jso = theForm.GetJSObject

theForm.Open (PATH)

Set drp_Z01_Durch = jso.getField("drp_Z01_Durch")
exPDF = jso.ClearingCombobox("drp_Z01_Durch")
exPDF = jso.addComboboxItems("drp_Z01_Durch", "Thom")

[Thom Parker]

You're Welcome!! 

On a related note. You have to be careful about what you pass from VB to JavaScript in the function arguments. Scalar types are compatible between lots of different platforms/languages. So passing strings and numbers is find. But Objects in JavaScript are a completely different animal from ojbects in VB.  Especially connected objects like the document and fields. 

It's a good practice to keep all code that deals with the JavaScript objects in folder level functions, and only pass scalar variables from VB into JS.