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.
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.
you can query an excel sheet just like a database, use ADO
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.OL
sql = "SELECT * FROM [sheet1$]"
Set objRs = CreateObject("ADODB.Record
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
%>
<%
objConn.Open "Provider=Microsoft.Jet.OL
sql = "SELECT * FROM [sheet1$]"
Set objRs = CreateObject("ADODB.Record
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
%>
> 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
> 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
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
o well?
../mk
ASKER
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?
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?
<select name="pCounty" class="text" >
<option value="">(Select a Category) </option>
<%
objConn.Open "Provider=Microsoft.Jet.OL
sql = "SELECT * FROM [sheet1$]"
Set objRs = CreateObject("ADODB.Record
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
%>
<option value="">(Select a Category) </option>
<%
objConn.Open "Provider=Microsoft.Jet.OL
sql = "SELECT * FROM [sheet1$]"
Set objRs = CreateObject("ADODB.Record
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
%>
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
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
ASKER
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.
Did I post in the wrong forum?
I'm trying the code now and it's running without error.. just not with the results.
> 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
> 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
ASKER
no, the excel file will be on the server, as well.
ASKER
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("
conn.Provider="Microsoft.J
conn.Open(Server.Mappath("
set rs = Server.CreateObject("ADODB
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>
no errors, but nothing doing.
<html>
<body>
<%
set conn=Server.CreateObject("
conn.Provider="Microsoft.J
conn.Open(Server.Mappath("
set rs = Server.CreateObject("ADODB
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>
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
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
ASKER
sciber_dude............ that's sad.
What needs to be done for it to work?
What needs to be done for it to work?
ASKER
as in, what do I need to do differently? and can you offer a brief explanation of why it won't work?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@ 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
> 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
ASKER
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.
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.
ASKER
I should clarify... that's intranet site. not website
ASKER
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?
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?
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
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
Typo: "Javascript variable" should be "Javascript array"
The big question here is.. what are you trying to do? and Why?
The big question here is.. what are you trying to do? and Why?
ASKER
<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?
That is correct.
Why do you suggest javascript instead of client-side VBScript?
ASKER
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
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
ASKER
FYI, the data will be something like:
Box Name Length Height Width Weight ETc
Box Name Length Height Width Weight ETc
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.opti
selbox.options[selbox.opti
selbox.options[selbox.opti
selbox.options[selbox.opti
selbox.options[selbox.opti
}
</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
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.opti
selbox.options[selbox.opti
selbox.options[selbox.opti
selbox.options[selbox.opti
selbox.options[selbox.opti
}
</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
All client side..
'--save below as data.js
var famname = new Array("One","Two","Three",
'--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("OP
for (i=0; i<famname.length; i++){
osel.add(new Option(famname[i],famname[
}
}
</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
'--save below as data.js
var famname = new Array("One","Two","Three",
'--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("OP
for (i=0; i<famname.length; i++){
osel.add(new Option(famname[i],famname[
}
}
</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
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.
../mk
Hope you come right.
ASKER
thanks, fellas.
I will try this tomorrow.
I will try this tomorrow.