Calculated cells in Excel not interacting with Web using ASP 1

grahamrobert · Aug 20, 2001

Can anyone help!

I am presently creating a web page which interacts with an excel spreadsheet.

I use VBscript to connect to Excel in similar lines to accessing a mdb file.

1. I can read out a recordset (using SQL) from a named area in the spreadsheet and write this in the browser
2. I can update cells in the spreadsheet by using the UPDATE command in SQL
3. I can read out the newly updated cells and write them to the browser....

However any of the cells in the named area that are "calculated cells" do not upate as they should considering the UPDATE command changes their inputs

eg.
page1.asp

Cell 1 5
Cell 2 5
Cell 3(=Cell 1*Cell2) 25

Use a form to update CELL 1 (to 4 say)and post to page2.asp and read out the new recordset

page2.asp

Cell 1 4
Cell 2 5
Cell 3(=Cell 1*Cell2) 25

NB Cell 3 has not updated

GabeC · Aug 20, 2001

I'm not sure what you are doing here. If you can send me your code I will take a look at it.

Thanks,

Gabe Condie

grahamrobert · Aug 20, 2001

Gabe,

Here is the code the calculated cell (CELL 2 * CELL3 *4) is the cell that does not come back recalculated

Thnks for taking the interest

EXCELTEST3.ASP

<% @LANGUAGE="VBScript" %>
<% Option Explicit %>

<%

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection&quot

objConn.Open ("TestGDickExcel&quot

Dim RS
Dim SQL

SQL = "Select * FROM input;"

SET RS = objConn.Execute(SQL)

If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"

'If you don't receive any errors, then

'you must have connected to the database successfully

Else
response.write "You have successfully connected to the excel spreadsheet."%>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head
<body>
<br>
<p>
<%
While NOT RS.EOF
response.write RS("Name&quot

%>... <%
response.write RS("Value&quot

%><br><%
RS.movenext
WENd
End If
%>
<form ACTION="

http://eolas.ie.kworld.kpmg.com/pensions/Ryan/Booklet/testexcel4.asp"

METHOD=GET >

<input type="text" name="CELL2" ><font size ="1">CELL2

<input type="text" name="CELL3" ><font size ="1">CELL3

<p><input type="submit" value="Proceed" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>

<%
rs.close
Set rs = nothing

objConn.close
Set ObjConn = nothing
%>
<p><a href="testexcel4.asp">

exceltest4</a>
<br>
</html>

EXCELTEST4.ASP

<%

Dim objConn1
Set objConn1 = Server.CreateObject("ADODB.Connection&quot

objConn1.Open ("TestGDickExcel&quot

Dim CELL2
Dim CELL3

CELL2 = Cint(Request.QueryString("CELL2&quot

)
CELL3 = Cint(Request.QueryString("CELL3&quot

)

Dim updateSQL
Dim updateSQL1

updateSQL = "UPDATE ID SET ID = '" & CELL2 & "' WHERE Name LIKE 'Jim' "
objConn1.Execute(updateSQL)

updateSQL1 = "UPDATE ID SET ID = '" & CELL3 & "' WHERE Name LIKE 'John' "
objConn1.Execute(updateSQL1)

Dim newSQL
newSQL= "Select * FROM ID;"

Dim newRS
Set newRS = objConn1.Execute(newSQL)

%>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>

<a href="testexcel3.asp">exceltest3</a><br><%

While NOT newRS.EOF
response.write newRS("ID&quot

%>... <%
response.write newRS("Name&quot

%><br><%
newRS.movenext
WENd

'Close your connection and ensure you're finished using the rs variable.
%>
<p>
<%
newRS.close
Set RS = nothing

objConn1.close
Set objConn1= nothing
%>
</body></html>

ID Name
1 Joe
6 John
5 Jim
120 CELL 2 * CELL3 *4

FengShui1998 · Aug 20, 2001

grahamrobert,

Did anyone answer this question? I don't think this is possible because the web page does not have access to you local EXCEL app. You can display the cells but not have a formula to calculate the cells.

If anyone knows how to do this, I have someone else who is also interested in the solution.

fengshui1998

SauerC · Aug 21, 2001

I'm not sure, but this website may have some info you are looking for

http://www.15seconds.com/Issue/970515.htm

-Christian

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Calculated cells in Excel not interacting with Web using ASP 1

grahamrobert

Programmer

GabeC

Programmer

grahamrobert

Programmer

FengShui1998

MIS

SauerC

Technical User

Similar threads

Part and Inventory Search

Sponsor