Using VBA to pass ADODB.Recordset to HTML javascript function

[brodaddy2002]

I am trying to pass a recordset to an HTML javascript. The reason is that my company's web page sercurity policy does not allow me to create an "ActiveXObject" in javascript. I was experimenting with passing a recordset from VBA to HTML javascript function as a work around. I can't seem to pass any variables to my javascript function other than hard coded text. Is what I am trying to do even possible? I know that javascript does not have a pass by reference feature. The web site How to find and call javascript method from vba - Stack Overflow says that it should work to pass variables to javascript function. Any assistance will be greatly appreciated. HTML script in header code is

    <title>CSMP</title>
     ********>
    
        
        function myFunction(name) {
            alert(name);
        }
        
        function main(obj)
        {
        
        var objRS=obj;
        alert("In main function");
          var DP = "Microsoft.ACE.OLEDB.12.0";
          var DS = "C:/Users/jeremy/Documents/N431/CustomDatabase/department1.accdb";  //local database path
          var DB = "tblPersonnel";


          var adOpenForwardOnly = 0;
          var adLockReadOnly = 1;
          var adCmdText = 1;
          
          //I left this code in here because it works if I am able to create an ActiveXObject
    /*       try 
          {
            var objRs = new ActiveXObject("ADODB.Recordset");
          }
          catch (e)
          {
            alert("ADODB namespace not found.");
            exit(0);
          } */






            var strConn =  "Provider="         +DP+
                    ";Data Source="      +DS;


            var strComm = "SELECT * "+
                    "FROM tblPersonnel";
            objRs.open(strComm, 
                     strConn, 
                     adOpenForwardOnly,
                     adLockReadOnly,
                     adCmdText);


          objRs.MoveFirst();
          while (objRs.EOF != true) 
          {
            alert(objRs("City")+"\t"
                 +objRs("State")+"\t");
            objRs.MoveNext();
          }


          objRs.Close
          objRs = null;
        }
        
     *********>




    <!-- This is just a button to test myFunction on webPage -->

The VBA Code is

Sub Macro1()



    Dim adCmd As ADODB.Command
    Dim Connection As ADODB.Connection
    Dim Cnct As String
    Dim Recordset As ADODB.Recordset
          Dim adOpenForwardOnly As Integer: adOpenForwardOnly = 0
      'dim adLockReadOnly as Integer: as=d = 1
     ' dim adCmdText = 1
     Dim src As String
    
    Dim DBname As String: DBname = "C:\Users\jeremy\Documents\N431\CustomDatabase\department1.accdb"
    
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
    Cnct = Cnct & "Data Source=" & DBname & ";"
    Connection.Open ConnectionString:=Cnct
    
    src = "SELECT * FROM tblPersonnel"
    
    Set Recordset = New ADODB.Recordset
    
    Dim w As SHDocVw.InternetExplorerMedium
    Set w = IEWindowFromTitle("CSMP")
    
    Dim CurrentWindow As HTMLWindowProxy: Set CurrentWindow = w.Document.parentWindow
    
    Dim tempString As String
    
    With Recordset
        .Open Source:=src, ActiveConnection:=Connection
        tempString = Recordset.GetString ' this does produce proper string
        Dim codeFormula As String: codeFormula = "main(tempString)"
        
        CurrentWindow.execScript code:=codeFormula
        'Debug.Print Recordset.GetString
    End With
    
    Recordset.Close


    
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing
    End Sub

Function IEWindowFromTitle(sTitle As String) As SHDocVw.InternetExplorer


    Dim objShellWindows As New SHDocVw.ShellWindows
    Dim win As Object, rv As SHDocVw.InternetExplorer


    For Each win In objShellWindows
        If TypeName(win.Document) = "HTMLDocument" Then
            If UCase(win.Document.Title) = UCase(sTitle) Then
                Set rv = win
                Exit For
            End If
        End If
    Next


    Set IEWindowFromTitle = rv


End Function

 

[John_w]

You could try implementing the JavaScript code with the Microsoft Script Control object, specifiically ScriptControl.Add to create the JavaScript function and ScriptControl.Run with the required parameter variables to run/call the function.

Though not related to your question, for an example of the use of ScriptControl see Parsing JSON in Excel VBA - Stack Overflow