Using VBScript to populate a listbox with contents from an Excel file

The question title is pretty self-explanatory, but I need to populate a listbox with an contents from an Excel file's column.
Then, the selected item in the list will display corresponding data (in the same row) from the file.

Could someone get me going in the right direction?

There is no server-side support for any scripting languages from what I can tell, so I need to do everything on the client side.
LVL 6
starfailureAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

justinbilligCommented:
you can query an excel sheet just like a database, use ADO
0
mk_bCommented:
Here is some code to go though a excel speadsheet?  you need to change Data Source=c:\myfile.xls to the file you need?  and this code assuemse that its looking at sheet1 first field?


<%

  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myfile.xls;Extended Properties=""Excel 8.0;HDR=NO"""
  sql = "SELECT * FROM [sheet1$]"

  Set objRs = CreateObject("ADODB.Recordset")
  objRs.Open sql, objConn, 1, 3
  If objRS.EOF Then
      Response.Write "No Data Found!"
  Else
  Do While Not objRS.EOF

  Response.Write objRS.Fields(1).value &"<br>"

  objRS.MoveNext
  Loop
 
  End If
  objRs.Close
  objConn.Close
  Set objRs = Nothing
  Set objConn = Nothing

%>
0
sciber_dudeCommented:
> There is no server-side support for any scripting languages from what I can tell,
> so I need to do everything on the client side.

Although the code given by mk_b looks good, your last comment threw me off. What exactly are you trying to do? Where is the excel file? where would the ASP page be?

If the ASP page is on the server and the excel file is on the client machine, that would pose problems.
If the ASP page is on the local machine and the excel file is also on the local machine, you would have to configure ASP to run on the Client machine.

If the server does not support ASP/any scripting language and you want to run a script (whereever the excel file is located), you cant do that!

Makes sense?
:) SD

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

mk_bCommented:
sorry speed reading skills are not up to scratch i missed "> There is no server-side support for any scripting languages from what I can tell,  > so I need to do everything on the client side."

o well?

../mk
0
starfailureAuthor Commented:
Sorry for the confusion; Please ignore that last statement.  
The reason i'm trying VBScript is because nothing else is supported.

Anyway, I'm working on mk_b's code and customizing...
Because I havent' done this before, how do I actually place each item returned into a listbox?



0
mk_bCommented:
<select name="pCounty" class="text" >
                   <option value="">(Select a Category) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</option>

<%
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myfile.xls;Extended Properties=""Excel 8.0;HDR=NO"""
  sql = "SELECT * FROM [sheet1$]"

  Set objRs = CreateObject("ADODB.Recordset")
  objRs.Open sql, objConn, 1, 3
  If objRS.EOF Then
     Response.Write "No Data Found!"
  Else
  Do While Not objRS.EOF %>

  <option value="<% =objRS.Fields(1).value %>"><% =objRS.Fields(1).value %></option>

 <%
  objRS.MoveNext
  Loop
 
  End If
  objRs.Close
  objConn.Close
  Set objRs = Nothing
  Set objConn = Nothing
%>
0
sciber_dudeCommented:
Is the file on which you have this code from mk_b an ASP file?

You can use VBScript in a webpage in two ways. Client Side VbScript and Server side VbScript (ASP).

An answer to this would be helpful in helping you with the code.

:) SD
0
starfailureAuthor Commented:
Hmm.. well, this is client side VBScript.
Did I post in the wrong forum?

I'm trying the code now and it's running without error.. just not with the results.
0
sciber_dudeCommented:
> Hmm.. well, this is client side VBScript.
> Did I post in the wrong forum?

Hmm @ client side VBScript. So I assume it is an HTML Page with client side VBScript. I dont think you can do what you want to do without using a scripting language on the server end.

Where is the Excel file stored? If it is on the client machine, then u will run into all sorts of security issues even with using server side VBScript (ASP).

mk_b, do u agree?
:) SD
0
starfailureAuthor Commented:
no, the excel file will be on the server, as well.
0
starfailureAuthor Commented:
is there anything wrong with this (i mean.. there is, but what is it?)
no errors, but nothing doing.


<html>
<body>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("c:\Work Documents\Excel\file.xls"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from [sheet1$]", conn

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "<br />")
    next
    Response.Write("<br />")
    rs.MoveNext
loop

rs.close
conn.close
%>
</body>
</html>
0
sciber_dudeCommented:
Let me get this scenario right...

HTML Page (with extension .htm or .html) using Client Side VBScript trying to access Excel File residing on the server.

Cant be done! sorry.
SD
0
starfailureAuthor Commented:
sciber_dude............ that's sad.
What needs to be done for it to work?
0
starfailureAuthor Commented:
as in, what do I need to do differently?  and can you offer a brief explanation of why it won't work?
0
sciber_dudeCommented:
> sciber_dude............ that's sad.
I know! I am sorry!

> What needs to be done for it to work?
Just copy the code that mk_b gave you as an ASP page. That means you need to get an ASP hosting. or configure your server to host ASP pages. The code he gave you is Server Side ASP and it will work fine.

Client-side scripting offers an easy way to provide additional functionality and flexibility to your website. HTML is very limited, it's main purpose is to display and format content. It allows little or no interaction with the visitors of your site. Client Side Scripting technologies like JavaScript, JScript, VBScript are used to add extra functionality to a web site. Ranging from simple mouse over image effects, animation, form field validation to complex dynamic menu systems, these scripts enhance the functionality and user experience.

The main difference between client and server-side scripting lies in the place and way it is executed. Client side scripts are downloaded by the browser, interpreted by the browser, and executed by the browser, depending on the memory, CPU speed and in when dealing with animation, even the video card in use on the client computer. Server-side scripts run on the server.

If you are using Server side VBScript, it means the ASP (VBscript code) gets executed and the file is returned as a Plain HTML file.

If you are using client side VBscript, by the time the file  is executed, the file being viewed is already at the client machine. So it cant access resources on the server (e.g. your Excel file).

I hope this makes sense.
:) SD
0
mk_bCommented:
ok there where some errors in the code i've go it working now and i tested in on my machine..


data structure? c:\file.xsl in the first field "A" there are 3 values
A
one
two
three


'--save the below code as test.asp in the c:\inetpub\wwwroot\ folder and then run the code though http://localhost/test.asp

<html>
<body>
<select name="pCounty" class="text" >
                 <option value="">(Select a Category) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</option>

<%
  Dim objConn, objRs
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\file.xls;Extended Properties=""Excel 8.0;HDR=NO"""
  sql = "SELECT * FROM [sheet1$]"

  Set objRs = CreateObject("ADODB.Recordset")
  objRs.Open sql, objConn, 1, 3
  If objRS.EOF Then
     Response.Write "No Data Found!"
  Else
  Do While Not objRS.EOF %>

  <option value="<% =objRS.Fields(0).value %>"><% =objRS.Fields(0).value %></option>

 <%
  objRS.MoveNext
  Loop
 
  End If
  objRs.Close
  objConn.Close
  Set objRs = Nothing
  Set objConn = Nothing
%>
</select>
</body>
</html>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sciber_dudeCommented:
@ mk_b -
> save the below code as test.asp

The scenario here is...
HTML Page (with extension .htm or .html) using Client Side VBScript trying to access Excel File residing on the server.

You have given Server Side VBScript (ASP) that would work well. Unfortunately starfailure cant use it coz of the scenario described above.

SD
0
starfailureAuthor Commented:
Thank you both for your help.  
I'm trying to figure out how to make this all feasible (and allow for more funcionality on my company's website), but as far as I can tell, the server supports nothing.
0
starfailureAuthor Commented:
I should clarify... that's intranet site.  not website
0
starfailureAuthor Commented:
is there another way i could pursue to do this?
like, if the data is in a text file or something...  then I could try to make an array of each "record"?
Would VBScript be able to do something like that?
0
sciber_dudeCommented:
1. Why not code it into the HTML page?
2. Why not store the listbox contents as an HTML file and use "HTML Server Side Include" to include it?
3. Have the list box contents as a Javascript variable and include the .js file into the HTML file.

I assume you basically want to have a centralized place for the contents of the listbox and u just want to edit this info at one place and thus edit it whereever you have references the listbox.

:) SD
 
0
sciber_dudeCommented:
Typo: "Javascript variable" should be "Javascript array"

The big question here is.. what are you trying to do? and Why?
0
starfailureAuthor Commented:
<I assume you basically want to have a centralized place for the contents of the listbox and u just want to edit this info at one place and thus edit it whereever you have references the listbox. >

That is correct.

Why do you suggest javascript instead of client-side VBScript?
0
starfailureAuthor Commented:
The data will initially be pulled from a database, which I won't be able to access from the Intranet server.
So, I can at least gather the data locally and put it on the server in the form of a simple text or html file every so often or whenever I need to update it.

My html page is for accessing this data and displaying/formatting it, etc
0
starfailureAuthor Commented:
FYI, the data will be something like:

Box Name        Length          Height          Width          Weight          ETc
0
sciber_dudeCommented:
It is a personal preference. I find that JavaScript is easier to write compared to VBScript especially while messing with form elements.

Please see this webpage to see how Javascript can work. http://www.felgall.com/jstip22.htm

I tried to create a custom HTML page for you... but there seems to be an error.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
      <head>
            <title></title>
            <script type="text/javascript">

                  function MyListBox() {

                        var selbox = document.myform.MyListBox;

                        selbox.options.length = 0;
                  
                        selbox.options[selbox.options.length] = new Option('option one','one');
                        selbox.options[selbox.options.length] = new Option('option two','two');
                        selbox.options[selbox.options.length] = new Option('option three','three');
                        selbox.options[selbox.options.length] = new Option('option four','four');
                        selbox.options[selbox.options.length] = new Option('option five','five');


                  }
            </script>
      </head>
      <body>
            <FORM id="Form1" name="myform">
                  <SELECT id="Select1" size="1" name="MyListBox">
                        <OPTION selected>test</OPTION>
                  </SELECT>
                  <INPUT id="Button1" onclick="MyListBox()" type="button" value="change" name="Button1">
            </FORM>
      </body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

:) SD
0
mk_bCommented:
All client side..


'--save below as data.js
var famname = new Array("One","Two","Three","Four")

'--use this code which will read the data file and the populate the listbox?
<html>
<head>
<title>Test</title>
<script language="JavaScript" src="data.js"></script>

<script type="text/javascript">
function LoadIT(){
   var osel = document.all("cCate")
   var o
   var o = document.createElement("OPTION");o.text="(Select a Sub-Category)            ";o.value="";osel.add(o);

   for (i=0; i<famname.length; i++){
     osel.add(new Option(famname[i],famname[i]));
   }
}
</script>
</head>
<body onload="LoadIT()">

<form method="POST" name="myForm" action="">
<input type="hidden" name="new" value="yes">
<select name="cCate"  id="cCate" class="text"></select>
</form>
</body>
</html>


../mk
0
mk_bCommented:
the above allows you to keep the data seperate from the functions? if you can export your data in the right format like the data.js file then you can probably achive what you require?

../mk

Hope you come right.
0
starfailureAuthor Commented:
thanks, fellas.  
I will try this tomorrow.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.