none
VBScript CreateObject method help RRS feed

  • General discussion

  • Hello,

    I've been working on a script that will read the columns of an excel file and store the data into an array. Once I have the arrays I will create checkbox's using the data that they contain. The code I have for this is as follows:

    Sub btn0_OnClick
    Dim arrNames()
    Dim arrValues()
    Dim arrPrograms()
    Dim arrChkNames()
    Dim length
    Dim name
    
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\crizzuti\Desktop\App\test1.xlsx")
    
    i = 1
    x = 0
    length = 0
    y = 0
    z = 0
    w = 0
    n = 0
    
    Do Until objExcel.Cells(i, 1).Value = ""
    	ReDim Preserve arrNames(x)
    	ReDim Preserve arrValues(y)
    	ReDim Preserve arrPrograms(z)
    	ReDim Preserve arrChkNames(w)
    	arrNames(x) = objExcel.Cells(i, 1).Value
    	arrValues(y) = objExcel.Cells(i, 2).Value
    	arrPrograms(z) = objExcel.Cells(i, 3).Value
    	If InStr (arrNames(x)," ") = 0 Then
    		arrChkNames(w) = arrNames(x)
    	Else
    		arrChkNames(w) = Replace(arrNames(x)," ","")
    	End If
    
        HTAFile.WriteLine "   <tr>"
        HTAFile.WriteLine "       <td>"
        HTAFile.WriteLine "           <div class=squaredTwo>"   
        HTAFile.WriteLine "               <input type=checkbox class=" & arrPrograms(z) & " id=checkbox" & length & " value=" & arrValues(y) & " name=" & arrChkNames(w) & ">"
        HTAFile.WriteLine "               <label id=label for=checkbox" & length & ">"
        HTAFile.WriteLine "           </div>"
        HTAFile.WriteLine "       </td>"
        HTAFile.WriteLine "       <td>" & arrNames(x) & "</td>"
        HTAFile.WriteLine "       <td>"
        HTAFile.WriteLine "   </tr>"
     
    	i = i + 1
        x = x + 1
        y = y + 1
        z = z + 1
        w = w + 1
    	length = length + 1
    Loop
    objExcel.Quit
    
    End Sub
    


    The problem I am having is that I want to use the data stored in the arrChkNames() array to test if the checkbox is checked once the button btn0 is clicked. Each checkbox's name attribute will have the names given to it from the arrChkNames() array. So I am trying to do something like this:

    For n = 0 To length
            If arrChkNames(n).Checked Then
    	    MsgBox arrValues(n) & "/" & arrPrograms(n)
            End If
    Next

    The error message I get when I run this code is "Object Required arrChkNames(...)." I understand that I am going to have to use the CreateObject method to retrieve the name from each checkbox to run through the loop. (Or maybe there is another way to do it I am open to anything).

    Please advise how I can do this using the CreateObject method or if there is a better method to do this.

    Thank you.


    Tuesday, July 7, 2015 8:58 PM

All replies

  • An array of check boxes can be extracted by name.

    Here is how to build a table of checkboxes.  To retrieve the values just enumerate eth tables rows and test each checkbox as each row has only one checkbox.

    <html>
    
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>My HTML Application</title>
    
    <script language="vbscript">
    	Sub btn0_OnClick
    	
    		Set objExcel = CreateObject("Excel.Application")
    		Set objWorkbook = objExcel.Workbooks.Open("C:\Users\crizzuti\Desktop\App\test1.xlsx")
    		
    		Do Until objExcel.Cells(i, 1).Value = ""
    			Set tr = document.CreateElement("tr")
    			table1.AppendChild tr
    			Set td = document.createElement("td")
    			tr.appendChild td
    			Set input = document.createElement("input")
    			td.appendChild input
    			input.type="checkbox"
    			input.class=objExcel.Cells(i, 3).Value
    			input.value=objExcel.Cells(i, 2).Value
    			input.name=objExcel.Cells(i, 1).Value
    			Set td1 = document.createElement("td")
    			tr.appendChild td1
    			id1.InnerText = objExcel.Cells(i, 1).Value
    		Loop
    		
    		objExcel.Quit
    		
    	End Sub
    	
            Function GetChecked_OnClick()
       For Each row in table1.Rows
        If row.firstChild.firstChild.Checked Then
         MsgBox "rowchecked --> " & row.firstChild.firstChild.value
        End If
       Next
    End Function </script> <hta:application applicationname="MyHTA" border="dialog" borderstyle="normal" > </head> <body> <input id="btn0" type="button" value="Add Rows"> <input id="GetChecked" type="button" value="Get Checked"> <!-- the following tabl will be gernerated at runtime. --> <table id="table1"/> </body> </html>


    \_(ツ)_/



    • Edited by jrv Tuesday, July 7, 2015 10:00 PM
    Tuesday, July 7, 2015 9:57 PM
  • Thank you for the response. However, this code yields an error.

    The line below gives an error message that says "Could not get the type property. This command is not supported."

    td.appendChild input


    Wednesday, July 8, 2015 1:48 PM
  • Sorry. I forgot my table HTML

    Try this:

    <html>
    
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>My HTML Application</title>
    
    <script language="vbscript">
    
    	Sub btn0_OnClick
    		For i = 0 To 10
    			Set row = table1.insertRow(-1)
    			Set cell = row.insertCell(-1)
    			Set cb = document.createElement("input")
                  		With cb
                    		.type = "checkbox"
                    		.value = "chkItem" & i
                  		End With
    			cell.appendChild cb
    			Set cell = row.insertCell(-1)
    			cell.innerText = "Label " & i
    		Next
    		
    		
    	End Sub
    	
    </script>
    
    <hta:application
    	applicationname="MyHTA"	
    	border="dialog"
    	borderstyle="normal"
    >
    </head>
    <body>
       <input id="btn0" type="button" value="Add Rows">
       <!-- the following tabl will be gernerated at runtime. -->
       <table id="table1">
       <tbody></tbody>
       </table>
    </body>
    </html>


    \_(ツ)_/


    • Edited by jrv Wednesday, July 8, 2015 3:04 PM
    Wednesday, July 8, 2015 3:03 PM