Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-11-10
29
Medium Priority
?
911 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:starfailure
  • 13
  • 9
  • 6
  • +1
29 Comments
 
LVL 15

Expert Comment

by:justinbillig
ID: 12546163
you can query an excel sheet just like a database, use ADO
0
 
LVL 5

Expert Comment

by:mk_b
ID: 12546217
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12546357
> 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:mk_b
ID: 12546537
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
 
LVL 6

Author Comment

by:starfailure
ID: 12546626
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
 
LVL 5

Expert Comment

by:mk_b
ID: 12546690
<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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12546766
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
 
LVL 6

Author Comment

by:starfailure
ID: 12546804
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12546870
> 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
 
LVL 6

Author Comment

by:starfailure
ID: 12546897
no, the excel file will be on the server, as well.
0
 
LVL 6

Author Comment

by:starfailure
ID: 12546920
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12546926
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
 
LVL 6

Author Comment

by:starfailure
ID: 12546965
sciber_dude............ that's sad.
What needs to be done for it to work?
0
 
LVL 6

Author Comment

by:starfailure
ID: 12546971
as in, what do I need to do differently?  and can you offer a brief explanation of why it won't work?
0
 
LVL 11

Assisted Solution

by:sciber_dude
sciber_dude earned 240 total points
ID: 12547152
> 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
 
LVL 5

Accepted Solution

by:
mk_b earned 260 total points
ID: 12547279
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12547725
@ 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
 
LVL 6

Author Comment

by:starfailure
ID: 12547849
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
 
LVL 6

Author Comment

by:starfailure
ID: 12547900
I should clarify... that's intranet site.  not website
0
 
LVL 6

Author Comment

by:starfailure
ID: 12547927
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12548014
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12548027
Typo: "Javascript variable" should be "Javascript array"

The big question here is.. what are you trying to do? and Why?
0
 
LVL 6

Author Comment

by:starfailure
ID: 12548037
<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
 
LVL 6

Author Comment

by:starfailure
ID: 12548063
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
 
LVL 6

Author Comment

by:starfailure
ID: 12548080
FYI, the data will be something like:

Box Name        Length          Height          Width          Weight          ETc
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12548555
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
 
LVL 5

Expert Comment

by:mk_b
ID: 12548602
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
 
LVL 5

Expert Comment

by:mk_b
ID: 12548636
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
 
LVL 6

Author Comment

by:starfailure
ID: 12548817
thanks, fellas.  
I will try this tomorrow.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question