Using VBA to pass ADODB.Recordset to HTML javascript function

brodaddy2002

Board Regular
Joined
Jan 21, 2013
Messages
67
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
Code:
    <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
Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
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
 

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top