|
 |
access_asp thread: ms excel
Message #1 by "Petr Nejedly" <petr@n...> on Tue, 26 Feb 2002 00:21:35 -0000
|
|
Hi,
I would like to ask whether it is possible to use data from MS Excel (xls)
file instead of MS Access (mdb) file for an asp page and if it is possible
may i ask how can i do it? thank you very much. Sincerely, Petr Nejedly
Message #2 by "Ian Richardson" <ian@i...> on Tue, 26 Feb 2002 08:52:43 -0000
|
|
Hi,
Iwas recently asked to do something similar by a client and my during
research I discovered that although it is possible to use ms excel as a
datasource for a web page, it is not something I would recommend.
Information I got from Microsoft suggests that MS Excel is not intended for
use as such a datasource and their may be licensing issues.
I don't know how far Office XP has moved to change this. I have included
some code I found below, I used references from www.4guysfromrolla.com, the
microsoft site and http://www.able-consulting.com/ado_conn.htm.
In practise I found that the use of excel is slow and inflexible and all I
managed to achieve was to demonstrate that it is NOT a good idea to power
web applications from ms excel.
Not much I know but I hope it helps.
ian
<html>
<head>
<title></title>
</head>
<body bgcolor="#5B75D9">
<br><br><br><br><br><br><br><br>
<center>
<%
strSwitch = Request.QueryString("switch")
strTitle1 = Request.QueryString("title1")
strRate1 = Request.QueryString("rate1")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "zion"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorType = 3 'Static cursor.
objRS.LockType = 2 'Pessimistic Lock.
If strSwitch = "monthly" Then
objRS.Source = "Select * from monthly"
Else
objRS.Source = "Select * from lumpsum"
End If
objRS.Open
%>
<br>
<%
'Response.Write("Original recordset 1 Data")
'Printing out original spreadsheet headings and values.
'Note that the first recordset does not have a "value" property
'just a "name" property. This will spit out the column headings.
'Response.Write("<TABLE><TR>")
For X = 0 To objRS.Fields.Count - 1
'Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
Next
'Response.Write("</TR>")
objRS.MoveFirst
While Not objRS.EOF
'Response.Write("<TR>")
For X = 0 To objRS.Fields.Count - 1
'Response.write("<TD>" & objRS.Fields.Item(X).Value)
Next
objRS.MoveNext
'Response.Write("</TR>")
Wend
'Response.Write("</TABLE>")
'The update is made here
objRS.MoveFirst
objRS.Update
objRS.Fields(0).Value = strTitle1
objRS.Fields(1).Value = strRate1
objRS.Update
'Printing out spreadsheet headings and values after update.
'Response.Write("<u>The following variables have been re-set:</u>")
'Response.Write("<table width=""200"" border=""0""
cellpadding=""3""><TR>")
'For X = 0 To objRS.Fields.Count - 1
'Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
'Next
'Response.Write("</TR>")
'objRS.MoveFirst
' While Not objRS.EOF
' Response.Write("<TR>")
' For X = 0 To objRS.Fields.Count - 1
' Response.write("<td><font face=""Arial"" color=""white""
size=""-1""><b>" & objRS.Fields.Item(X).Value)
' Response.Write("</b></font></TD>")
' Next
' objRS.MoveNext
' Response.Write("</TR>")
' Wend
' Response.Write("</TABLE>")
'ADO Object clean up.
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Response.Redirect "results1.asp?amount=" & strRate1 & "&switch=" &
strSwitch
%>
</center>
</body>
</html>
----- Original Message -----
From: "Petr Nejedly" <petr@n...>
To: "Access ASP" <access_asp@p...>
Sent: Tuesday, February 26, 2002 12:21 AM
Subject: [access_asp] ms excel
> Hi,
> I would like to ask whether it is possible to use data from MS Excel (xls)
> file instead of MS Access (mdb) file for an asp page and if it is possible
> may i ask how can i do it? thank you very much. Sincerely, Petr Nejedly
>
>
$subst('Email.Unsub').
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 27 Feb 2002 12:10:02 +1100
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Petr Nejedly" <petr@n...>
Subject: [access_asp] ms excel
: I would like to ask whether it is possible to use data from MS Excel (xls)
: file instead of MS Access (mdb) file for an asp page and if it is possible
: may i ask how can i do it? thank you very much. Sincerely, Petr Nejedly
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You can, but why?
Excel is a spreadsheet, not a database. Spreadsheets are not really designed
to be queried like a database can. Instead, they're really designed for
performing calculations...
If you're running queries on data you're best importing the excel data into
a real database, and querying that database.
Cheers
Ken
|
|
|
View Cart
