Accessing data from Excel and put it onto the webpage

**cassandra** · Nov 23rd, 2000, 02:16 AM

I need to extract data from excel file and display onto the webpage.. pls help.

**HarryW** · Nov 23rd, 2000, 03:02 AM

The easiest way is probably to create a macro in Excel which will output the HTML for a table to a text file. Try this:

Code:

Dim rg As Range
Dim ws As Worksheet

Set ws = Application.ActiveWorksheet  'I have forgotten if this is correct, but you get the idea

rg.Select(top_left_cell, bottom_right_cell) 'change for your cells

Open text_file_path_and_filename For Random As #1
Put #1, "<TABLE>" & vbNewLine
For y = 1 To rg.RowCount
	Put #1, vbTab & "<TR>" & vbNewLine
	For x = 1 To rg.ColumnCount
		Put #1, vbTab & vbTab & "<TD>"
		Put #1, rg.Cells(x, y).Value
		Put #1, "</TD>" & vbNewLine
	Next x
	Put #1, vbTab & "</TR>" & vbNewLine
Next y
Put #1, "</TABLE>"
Close #1

Set rg = Nothing
Set ws = Nothing

My Excel VBA is a bit rusty, so I expect that's full of errors, but I think you get the idea. Once you've run that macro with your excel worksheet as the current worksheet you can copy and paste the contents of the text file into your HTML. Of course that's a bit crude if you want to do this a lot, but it's fairly simple to extend this example if you want.

**cassandra** · Nov 23rd, 2000, 03:28 AM

Is it possible to just extract the data from the excel file and display it onto the form coz i have already create the form..

**HarryW** · Nov 23rd, 2000, 04:23 AM

Oh, you want to put the data in a form. Well yes, it is possible. I believe there are a number of utilities out there that you can use to extract information from Excel files, maybe they could be of some use.

What I gave you there is just code to display an Excel spreadsheet as an HTML table, but you could change it without too much fuss to display the data in a number of formats. I don't really know how your form is layed out; are you displaying a form of one record at a time or a grid of values? Either could have the data inserted without too much fuss I would have thought.

**cassandra** · Nov 23rd, 2000, 04:45 AM

Below is my program.. I cannot extract data from excel and display onto the web page. Pls advice..

<HTML>
<HEAD><TITLE>Clean Room Monitoring System</TITLE>
<SCRIPT LANGUAGE="VBScript">

</SCRIPT>
</HEAD>
<BODY>
<H3>Clean Room Monitoring System</H3><HR>
<FORM>
Air Sucction
<INPUT TYPE="Text" SIZE="6" VALUE="value1">
<br>
Fire Alarm
<INPUT TYPE="Text" SIZE="6" VALUE="value2">
<br>
Gas Detection
<INPUT TYPE="Text" SIZE="6" VALUE="value3">
<br>
Power Failure
<INPUT TYPE="Text" SIZE="6" VALUE="value4">
<br>
Temperature Sensor
<INPUT TYPE="Text" SIZE="6" VALUE="value5">
<br>
Water Leakage
<INPUT TYPE="Text" SIZE="6" VALUE="value6">
</FORM>
</BODY>
</HTML>

**da_silvy** · Nov 23rd, 2000, 05:57 AM

this is code i use:

Code:

<%
Dim MyConn, sConnString
Set Myconn = Server.CreateObject("ADODB.Connection")
ConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _ 
    "DBQ=" & Server.MapPath("\dasilvy\db\links.mdb") & ";"
MyConn.Open(ConnString)
x=1
strsql = "SELECT * FROM DG"
   Set rs = Server.CreateObject("ADODB.RecordSet")
   RS.open strsql, MyConn, 3, 3
%>
<FORM> 
Air Sucction 
<INPUT TYPE="Text" SIZE="6" VALUE="<%=rs("name")%>"> 
<br> 
Fire Alarm 
<INPUT TYPE="Text" SIZE="6" VALUE="<%=rs("file")%>"> 
<br> 
Gas Detection 
<INPUT TYPE="Text" SIZE="6" VALUE="<%=rs("times")%>"> 
<br> 
Power Failure 
<INPUT TYPE="Text" SIZE="6" VALUE="<%=rs("desc")%>"> 
<br> 
Temperature Sensor 
<INPUT TYPE="Text" SIZE="6" VALUE="<%=rs("title")%>"> 
<br> 
Water Leakage 
<INPUT TYPE="Text" SIZE="6" VALUE="<%=rs("size")%>"> 
</FORM>

i would suggest creating the databases in access...
(just my personal preference)
the variables are from a download manager database,
so you can change <%=rs("name")%> to a field name (like suction) or something

also change \dasilvy\db\links.mdb to the location of ur database

tell me if this works for you

Thread: Accessing data from Excel and put it onto the webpage

Thread Tools

Display

Posting Permissions