I need to extract data from excel file and display onto the webpage.. pls help.
Printable View
I need to extract data from excel file and display onto the webpage.. pls help.
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.
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..
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.
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">
<!--
Sub Display
Dim value1 as string
Dim value2 as string
Dim value3 as string
Dim value4 as string
Dim value5 as string
Dim value6 as string
Dim wkbk
set wkbk = GetObject("C:\temp.xls")
Workbooks.Open "c:\temp.xls"
value1 = Worksheets("Sheet1").Cells(1, 2).Value
value2 = Worksheets("Sheet1").Cells(2, 2).Value
value3 = Worksheets("Sheet1").Cells(3, 2).Value
value4 = Worksheets("Sheet1").Cells(4, 2).Value
value5 = Worksheets("Sheet1").Cells(5, 2).Value
value6 = Worksheets("Sheet1").Cells(6, 2).Value
Workbooks.Close
End Sub
-->
</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>
this is code i use:
i would suggest creating the databases in access...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>
(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