Spreadsheet component in an ActiveX dll

[tmarko]

Is it possible to use the office spreadsheet component
without having a userform to place the component on? I
would like to make calculations on the server instead of
on the clients. My server code constitutes of different
ActiveX dll-s. I would like to populate the spreadsheet
component with data from a recordset from my SQL200
database. In the spreadsheet I would calculate a lot of
formulas that is also coming from the databse. Then I
would like to get the result in XML from the component.
The data then will be selected and parts of it sent to the
client where XML then populates Excel2002 and the figures
are shown on the client.

Is this possible to accomplish? If yes where can I get
some code examples to get some ideas.

 

[Robin Hammond]

Why don't you just pass the recordset direct to XL.

xl posts, asp gets rs from db, sends rs to xl, xl filters and spits out
results.

Here's a start on getting the rs and sending it back from the asp page.

http://www.able-consulting.com/MDAC/ADO/Connection/Recordset_URL.htm#AdoUrlConnection

Back in xl, this is the kind of thing that gets the rs. My asp pages are set
up to accept SQL strings with which they determine what to fetch from the
db, and here's an extract of a function I use to get the data back from the
asp page into xl. You need a reference to ADO2.7 in xl. There's more on this
kind of thing in Bullen, Green, Bovey & Rosenbergs XL2002 vba book.

Public Function GetInternetRS(strSQL As String, _
Optional bForceRefresh As Boolean = False) As ADODB.Recordset

Dim rsReturn As ADODB.Recordset
Dim strConn As String

'returns a recordset based on either preset values held in properties
'or new values passed as optional parameters

Set rsReturn = New ADODB.Recordset
strConn = http://nnn.nnn.nnn.nnn/myasppage.asp"?SQL=" & strSQL

'this forces a refresh if needed
If bForceRefresh = True Then strConn = strConn & _
"&Dummy=" & CInt(10000 * Rnd())

rsReturn.Open strConn, , adOpenStatic, adLockBatchOptimistic

Set GetInternetRS = rsReturn

'this function is in a class with a public property for load success
'which I can test once this has finished
If Not GetInternetRS.EOF Then
LoadSuccess = True
Else
LoadSuccess = False
End If
'note however that you can return a recordset with 0 records if the sql
returns no records and get a loadsuccess = false but still have a valid
response, and this is quite an elegant way of getting an RS set up with all
the correct fields if you want to start populating it.

'disconnect the recordset
GetInternetRS.ActiveConnection = Nothing
Set rsReturn = Nothing
End Function

Robin Hammond
www.enhanceddatasystems.com