none
How do I pull variables from an excel spreadsheet to constitute values referenced by a hta? RRS feed

  • Question

  • I'm an IT lead trying to use some an hta application to create a dashboard for my techs...

    I have 'Rapid Access for Monitoring'

    Which honestly works great - I can define the variables and manually set the ip addresses..

    But it's awful to work on...  Counting cells, multiple revisions etc...

    It would be more useful if I could have Excel organize the input variables and call the cells for the application inputs.

    i.e.

    Instead of manually defining each cell by hand, inporting from an excel cell...

    SO I could make it change easily by manually editing the hta once then changing only the excel sheet...

    ...

    This isn't probably correct code but

    ..

    set excelsheet = ".\input.xlsx"

    ...

    set objExcel = CreateObject("Excel.Application")

    ...

    <ColumnB></ColumnB>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t (varfromobjExcel,Sheetvar'Sheet 1'.Cells(A.Row, 2)');return false;">(varfromobjExcel,Sheetvar'Sheet 1'.Cells(A.Row, 2)</a></td><ColumnB></ColumnB>

    So that the ping application cell would both ping and label from the excel sheet...

    ------------------------

    What I have currently is formatted as: application.hta

    <html>
    <head>
    <title>Rapid Access for Monitoring</title>

    <HTA:APPLICATION
    windowState="maximize" />

    <script language="javascript" type="text/javascript">
    var shellObject = new ActiveXObject("Shell.Application");
    var mstsc = "C:\\Windows\\System32\\mstsc.exe";
    var putty = "C:\\Program Files (x86)\\PuTTY\\putty.exe";
    var ping = "ping.exe";
    </script>

    </head>
    <body>

    <style type="text/css">

    body {
    background: #fad6a5;
    }
    table.maintable
    {
        border-width:1px;
        border-style:outset;
        border-color:white;
        border-collapse:collapse;
        background: #ffffff;
        width: 100%;
    }


    table.maintable td
    {
        padding:0.1em 1ex 0.1em 1ex;
        color:black;
        border-style:solid;
        border-color:gray;
        border-width:1px;
    }


    table.maintable tr.header th
    {
        padding:0.1em 1ex 0.1em 1ex;
        color:#4682B4;
        border-style:solid;
        border-color:gray;
        border-width:1px;
        filter:progid:DXImageTransform.Microsoft.Gradient(startColorstr='#E8E8E8', endColorstr='#909090', gradientType='0');
    }


    table.maintable tr.header th.colA { width: 10%; }
    table.maintable tr.header th.colB { width: 10%; }
    table.maintable tr.header th.colC { width: 14%; }
    table.maintable tr.header th.colD { width: 12%; }
    table.maintable tr.header th.colE { width: 8%; }
    table.maintable tr.header th.colF { width: 13%; }
    table.maintable tr.header th.colG { width: 13%; }
    table.maintable tr.header th.colH{ width: 32%; }

    /* blue */
    a:link
    {
        color: #0000ff;
        background-color: transparent;
        text-decoration: none;
    }

    /* steelblue */
    a:visited
    {
        color: #0000ff;
        background-color: transparent;
        text-decoration: none;
    }

    /* deepskyblue */
    a:hover
    {
        color: #0000ff;
        background-color: transparent;
        text-decoration: underline;
    }

    </style>


    <p align="left"><a href="#" >Version Date 11/4/2014</a></p>
    <p align="left"><a href="#" >Author: Aryeh Levy</a></p>

    <p align="center"><b><a href="#" >Monitoring Systems</a></b></p>

    <table class="maintable" align="center">
    <tr class="header"><th class="colA">Name</th>

    <th class="colB">IP Address</th>

    <th class="colC">Model</th>

    <th class="colD">Supervisor</th><th class="colE">OS</th>

    <th class="colF">APs PING</th>

    <th class="colG">APs PROGRAM</th>

    <th class="colH">Location</th></tr>

    <tr><td><a href="#" onclick="shellObject.ShellExecute(putty, '-telnet telnetable address');return false;">Switchname A1 </a></td>

    <td><a href="#" onclick="shellObject.ShellExecute(ping, '-t input Pingable IP Here');return false;">IP Address Label B1 </a></td>
    <td>Column C 1</td>
    <td>Sup Type D1</td>
    <td> IOS Column E1 </td>

    <td>Column F 1 </td>

    <td></td><td>Name / Location Column</td></tr>

    <tr><td><a href="#" onclick="shellObject.ShellExecute(putty, '-telnet Putty Telnet IP');return false;">Name</a></td><td><a href="#" onclick="shellObject.ShellExecute(ping, '-t ping-t ip');return false;">ping ip label</a></td><td>namelabel</td><td>suplabel</td><td>IOS</td><td></td><td></td><td>Dlocation</td></tr>

    </table>


    Section Delimiter To Be Deleted

    <p align="center"><b><a href="#" >Resources</a></b></p>
    <table class="maintable" align="center">
    <tr class="header"><th class="colA">Name</th>
    <th class="colB">IP Address</th><th class="colC">Model</th>
    <th class="colD">Supervisor</th>
    <th class="colE">OS</th>
    <th class="colF">APs PING</th>
    <th class="colG">APs PROGRAM</th>
    <th class="colH">Location</th></tr>

    <ColumnA></ColumnA>
    <TableActionsDefinition></TableActionsDefinition><tr><td><a href="#" onclick="shellObject.ShellExecute(putty, '-telnet TelnetIP');return false;">Switchname</a></td><td>
    <ColumnA></ColumnA>


    <ColumnB></ColumnB>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 1.1.1.1');return false;">1.1.1.1</a></td><ColumnB></ColumnB>

    <ColumnC></ColumnC>
    <td>Model1.1</td>
    <ColumnC></ColumnC>


    <ColumnD></ColumnD>
    <td>SUP1.1</td>
    <ColumnD></ColumnD>

    <ColumnE></ColumnE>
    <td>IOS</td>
    <ColumnE></ColumnE>

    <ColumnF></ColumnF>
    <td title="AP1.1, 1.1.1.2, 1.1Location&#13;AP1.1.1.1, 2.2.2.2, 3.3.3.3 Location">
    <ColumnF></ColumnF>


    <ColumnGa></ColumnGa>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 2.2.2.2');return false;">ApDisplay</a>, 
    <ColumnGa></ColumnGa>

    <ColumnGb></ColumnGb>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 3.3.3.3');return false;">3.3.3.3</a></a></td>


    <ColumnE></ColumnE>
    <td><a href="http://2.2.2.2">2.2.2.2</a>,<a href="http://3.3.3.3">3.3.3.3</a>
    <ColumnE></ColumnE>


    <ColumnF></ColumnF>
    <td><a href="http://www.google.com">Location is Google</a>,<a href="http://www.thatwebsite.com">THe label</a>
    <ColumnE></ColumnE>

    </table>

    Section Delimiter To Be Deleted
    Section Delimiter To Be Deleted

    <p align="center"><b><a href="#" > Resources</a></b></p>
    <table class="maintable" align="center">
    <tr class="header"><th class="colA">Name</th>
    <th class="colB">IP Address</th><th class="colC">Model</th>
    <th class="colD">Supervisor</th>
    <th class="colE">OS</th>
    <th class="colF">APs PING</th>
    <th class="colG">APs PROGRAM</th>
    <th class="colH">Location</th></tr>

    <ColumnA></ColumnA>
    <TableActionsDefinition></TableActionsDefinition><tr><td><a href="#" onclick="shellObject.ShellExecute(putty, '-telnet TelnetIP');return false;">Switchname</a></td><td>
    <ColumnA></ColumnA>


    <ColumnB></ColumnB>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 1.1.1.1');return false;">1.1.1.1</a></td><ColumnB></ColumnB>

    <ColumnC></ColumnC>
    <td>Model1.1</td>
    <ColumnC></ColumnC>


    <ColumnD></ColumnD>
    <td>SUP1.1</td>
    <ColumnD></ColumnD>

    <ColumnE></ColumnE>
    <td>IOS</td>
    <ColumnE></ColumnE>

    <ColumnF></ColumnF>
    <td title="AP1.1, 1.1.1.2, 1.1Location&#13;AP1.1.1.1, 2.2.2.2, 3.3.3.3 Location">
    <ColumnF></ColumnF>


    <ColumnGa></ColumnGa>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 2.2.2.2');return false;">ApDisplay</a>, 
    <ColumnGa></ColumnGa>

    <ColumnGb></ColumnGb>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 3.3.3.3');return false;">3.3.3.3</a></a></td>


    <ColumnE></ColumnE>
    <td><a href="http://2.2.2.2">2.2.2.2</a>,<a href="http://3.3.3.3">3.3.3.3</a>
    <ColumnE></ColumnE>


    <ColumnF></ColumnF>
    <td><a href="http://www.google.com">Location is Google</a>,<a href="http://www.dcot.com">DCOT</a>
    <ColumnE></ColumnE>

    </table>
    Section Delimiter To Be Deleted

    <p align="center"><b><a href="#" >Second Resources</a></b></p>
    <table class="maintable" align="center">
    <tr class="header"><th class="colA">Name</th>
    <th class="colB">IP Address</th><th class="colC">Model</th>
    <th class="colD">Supervisor</th>
    <th class="colE">OS</th>
    <th class="colF">APs PING</th>
    <th class="colG">APs PROGRAM</th>
    <th class="colH">Location</th></tr>

    <ColumnA></ColumnA>
    <TableActionsDefinition></TableActionsDefinition><tr><td><a href="#" onclick="shellObject.ShellExecute(putty, '-telnet TelnetIP');return false;">Switchname</a></td><td>
    <ColumnA></ColumnA>


    <ColumnB></ColumnB>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 1.1.1.1');return false;">1.1.1.1</a></td><ColumnB></ColumnB>

    <ColumnC></ColumnC>
    <td>Model1.1</td>
    <ColumnC></ColumnC>


    <ColumnD></ColumnD>
    <td>SUP1.1</td>
    <ColumnD></ColumnD>

    <ColumnE></ColumnE>
    <td>IOS</td>
    <ColumnE></ColumnE>

    <ColumnF></ColumnF>
    <td title="AP1.1, 1.1.1.2, 1.1Location&#13;AP1.1.1.1, 2.2.2.2, 3.3.3.3 Location">
    <ColumnF></ColumnF>


    <ColumnGa></ColumnGa>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 2.2.2.2');return false;">ApDisplay</a>, 
    <ColumnGa></ColumnGa>

    <ColumnGb></ColumnGb>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 3.3.3.3');return false;">3.3.3.3</a></a></td>


    <ColumnE></ColumnE>
    <td><a href="http://2.2.2.2">2.2.2.2</a>,<a href="http://3.3.3.3">3.3.3.3</a>
    <ColumnE></ColumnE>


    <ColumnF></ColumnF>
    <td><a href="http://www.google.com">Location is Google</a>,<a href="http://www.websites.com">Fourth</a>
    <ColumnE></ColumnE>

    </table>
    Section Delimiter To Be Deleted

    <p align="center"><b><a href="#" >Resources</a></b></p>
    <table class="maintable" align="center">
    <tr class="header"><th class="colA">Name</th>
    <th class="colB">IP Address</th><th class="colC">Model</th>
    <th class="colD">Supervisor</th>
    <th class="colE">OS</th>
    <th class="colF">APs PING</th>
    <th class="colG">APs PROGRAM</th>
    <th class="colH">Location</th></tr>

    <ColumnA></ColumnA>
    <TableActionsDefinition></TableActionsDefinition><tr><td><a href="#" onclick="shellObject.ShellExecute(putty, '-telnet TelnetIP');return false;">Switchname</a></td><td>
    <ColumnA></ColumnA>


    <ColumnB></ColumnB>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 1.1.1.1');return false;">1.1.1.1</a></td><ColumnB></ColumnB>

    <ColumnC></ColumnC>
    <td>Model1.1</td>
    <ColumnC></ColumnC>


    <ColumnD></ColumnD>
    <td>SUP1.1</td>
    <ColumnD></ColumnD>

    <ColumnE></ColumnE>
    <td>IOS</td>
    <ColumnE></ColumnE>

    <ColumnF></ColumnF>
    <td title="AP1.1, 1.1.1.2, 1.1Location&#13;AP1.1.1.1, 2.2.2.2, 3.3.3.3 Location">
    <ColumnF></ColumnF>


    <ColumnGa></ColumnGa>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 2.2.2.2');return false;">ApDisplay</a>, 
    <ColumnGa></ColumnGa>

    <ColumnGb></ColumnGb>
    <a href="#" onclick="shellObject.ShellExecute(ping, '-t 3.3.3.3');return false;">3.3.3.3</a></a></td>


    <ColumnE></ColumnE>
    <td><a href="http://2.2.2.2">2.2.2.2</a>,<a href="http://3.3.3.3">3.3.3.3</a>
    <ColumnE></ColumnE>


    <ColumnF></ColumnF>
    <td><a href="http://www.google.com">Location is Google</a>,<a href="http://www.accesskey.com">Sitename</a>
    <ColumnE></ColumnE>

    </table>

    </table>

    </body>
    </html>

    --------------------

    Wednesday, August 5, 2015 2:37 PM

Answers

  • Hi Aryeh,

    >>How do I pull variables from an excel spreadsheet to constitute values referenced by a hta? <<

    Based on my understanding, there are several technology like ADO.Net, Office automation, Open XML that we can retrieve data from spreadsheets.

    And based on the code above, it seems that you want to automate the Excel via JavaScript. Here is an example for your reference:

     function getValue(cellName) {
         
         var excelApp = new ActiveXObject("Excel.Application");
         var aWorkbook = excelApp.Workbooks.Open("C:\\ShareWorkbook.xlsm");
         return aWorkbook.Worksheets("Sheet1").Range(cellName);
       
     }
    
     function TestGetValue() {
         alert(getValue("A1"));
     }

    You can get more detail about Excel object model from link below:

    Welcome to the Excel 2013 developer reference

    The code sample need ActiveX enable in the broswer since it created ActiveX to automate Excel.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 6, 2015 6:09 AM
    Moderator

All replies

  • I'd be happy enough if I could save my input data as a csv and then call the csv, but not sure about that either...

    Would be fine if I had arrays from the csv, and I merely has to say array5(value 5) or something similar, simply could import a csv and reference the row (array) +1 since the top line would be labels...


    <ColumnF></ColumnF>
    <td><a href="http://www.google.com">Location is Google</a>,<a href="http://www.accesskey.com">Sitename</a>
    <ColumnE></ColumnE>

    Wednesday, August 5, 2015 2:52 PM
  • Hi Aryeh,

    >>How do I pull variables from an excel spreadsheet to constitute values referenced by a hta? <<

    Based on my understanding, there are several technology like ADO.Net, Office automation, Open XML that we can retrieve data from spreadsheets.

    And based on the code above, it seems that you want to automate the Excel via JavaScript. Here is an example for your reference:

     function getValue(cellName) {
         
         var excelApp = new ActiveXObject("Excel.Application");
         var aWorkbook = excelApp.Workbooks.Open("C:\\ShareWorkbook.xlsm");
         return aWorkbook.Worksheets("Sheet1").Range(cellName);
       
     }
    
     function TestGetValue() {
         alert(getValue("A1"));
     }

    You can get more detail about Excel object model from link below:

    Welcome to the Excel 2013 developer reference

    The code sample need ActiveX enable in the broswer since it created ActiveX to automate Excel.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 6, 2015 6:09 AM
    Moderator