Opening an existing excel worksheet in asp

[morgancaroline]

I have written an asp which queries a database but instead of writing the results in a html table, I want to write them in an existing excel worksheet (a template) using code such as

Set cn = Server.CreateObject("ADODB.Connection&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot
cn.Open "DSN=zdev_dep;uid=sa;pwd=sa;"
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.ActiveConnection = cn
Dim RowCnt, FieldCnt
RowCnt = 2
FieldCnt = 3

Cells(RowCnt, FieldCnt).Value = "Department"
' Fill rows with records, starting at row 2. 
Count = 0
Do While Not rs.EOF
RowCnt = Count + 9
Cells(RowCnt, 1).Value = 9988
For FieldCnt = 1 To 6
Cells(RowCnt, FieldCnt + 1).Value = rs.Fields(FieldCnt)
Next FieldCnt
Count = Count + 1
rs.MoveNext
loop

rs.Close
cn.Close  

Does anyone know how I tell the computer I'm refering to the cells of an existing excel worksheet?
thanks

 

[ToddWW]

I am not aware of any way that you can edit Excel on the server. You can automate Excel on the client using VBScript (IE Only), or you can re-write the Excel worksheet on the server.

At any rate, check out this thread. It has three articles pertaining to Excel and ASP. You might go to the microsoft.com Knowledge Base and do some of your own searching.

thread333-151169

I know the thread is titled Creating Excel Documents with ASP, but the third MS article that discusses Excel Automation might help.

Besides, if you don't find a way to accomplish what you want, you may have to just re-write the entire Excel worksheet which in that case, these articles are right down your alley.

ToddWW

 

[hblackorby]

I believe you can create an ODBC connection to an Excel sheet using ADODB connections (and an Excel DNS driver), but I'm not sure what all you can do with it. Try going into the ODBC in your Control Panel, creating an Excel DNS and playing with that. What you might end up doing is using the FileSystemObject to copy your template file and then use the Excel ODBC driver to populate it. I would be suprised if the driver allowed you to format though.

Hope that helps. Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[morgancaroline]

Unfortunately this is for a project at work and I do not have access to the settings. I may have to renounce and just write out the whole excel sheet. Thanks for the links and advice
Caroline

 

[FengShui1998]

morgancaroline,

Before you write your table to the browser, input the following line:

Response.ContentType = "application/vnd.ms-excel"

The browser should open the client Excel and display the data as is.

Cheers,
fengshui_1998

 

[morgancaroline]

I have written this line and excel opens up fine when I write a simple table. However, only simple <tr><td> tags are used to create the table. Anywhere I change the colspan or use <br> or try to input values in the table queried from a database using vbscript, the table ignores the formatting/values. Also, if I come into the page from another page and use a querystring to pass a variable, I get an error message saying, &quot;IE cannot download from the internet 01 from webdev1 (my directory). The download is not available. This could be due to your security or language settings or because the server was unable to retrieve the requeasted file.&quot; And excel is unable to download.
Is anybody familiar with these problems?

 

[ToddWW]

Caroline,

If you have the Save As Web Page option in your Excel, you can format up a worksheet in Excel and save it as a web page.

Then use that format to write the document in ASP.

ToddWW

 

[FengShui1998]

morgancaroline,

Here is a good url to read for formatting an EXCEL for ASP.

http://asp101.aspin.com/home/tutorial/document/xls?cob=asp101