Use Office spreadsheet component in a ActiveX dll

[Guest]

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.

 

[KJTFS]

Yes you can create an excel object , you need to set some extr
references in your projects, "Microsoft Excel 10 Object Library"
beleive that is the name. Then you can

dim xlSht as excel.worksheet

and then do other things like that.

Since you are vauge in what you want I would suggest searching on Exce
object in VB, or Excel Object Library.....

Things like that.

Keith
www.kjtfs.co

 

[KJTFS]

BTW, I have some code at work that uses Excel in VB 6.0 if I remember I
will post it, if not send me an email and I will get it.

addy: (e-mail address removed)
(remove the nospam)


Keith
www.kjtfs.com

 

[Nick HK]

Depending what you're trying to achieve, this may be useful.

http://abstractvb.com/code.asp?A=959

NickHK




| BTW, I have some code at work that uses Excel in VB 6.0 if I remember I
| will post it, if not send me an email and I will get it.
|
| addy: (e-mail address removed)
| (remove the nospam)
|
|
| Keith
| www.kjtfs.com
|
|
| ---
| Message posted
|
|

 

[tmarko]

Instead of sending a lot of unnecessary info from the server to the
clint I would like to make necessary calculations on the server. Then
select the information that a particular user would like to receive.
Tagg everything to XML and send it to the Client and show it in Excel
or a to browser or other client tools.

The calculations are necessary because I stor raw values and formulas
in the database and need to make the calculations somewhere. If it is
possible on the server It would be faster I think.

Before ALL reports (different data from the calculated values) can be
sent I would like to perform the same calculations. But each user only
needs a fraction of the calculated values. Instead of sending all raw
values and formulas tio excel on the clinet I perform that job on the
server and make the selections on the server as well and send exactly
what easch user wants to look at to the client.

Hope this clarify my wishes a bit more.

 

[KJTFS]

Then try using the

application.WorksheetFunction

within the excel object you create. Or you can just make a reference
to the microsoft excel 10 Library and you will be able to use the
application object.

Keith
www.kjtfs.com

 

[KJTFS]

I got your email but my server is not letting me send for some reason s
I will post for the good of the board. It is random stuff but lookin
and seeing what you can do always helps me program and figure thing
out. Don't mind the objTopic or Server crap, that is to deal with th
application.

Dim ObjXL As Object
Dim ObjXLBook As Object
Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open(DATACOL_FILE_PATH
objTopic.PathString & ".xls")

objTopic.TopicValue = ObjXLBook.ActiveSheet.Range("Price").Value

ObjXLBook.Close
Set ObjXL = Nothing
Set ObjXLBook = Nothing


here is from another app

Dim ObjXL As Object
Dim ObjXLBook As Object

Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open(DLookup("LotHoldTemplate"
"tblExportPaths"))
ObjXLBook.sheets("MAIN").Activate
ObjXLBook.sheets("MAIN").Select

ObjXLBook.sheets("MAIN").Range("RunFund").Offset(0, 0).Value
UCase(Trim(strFund))
ObjXLBook.sheets("MAIN").Range("FileLoc").Offset(0, 0).Value
Trim(strFileName)

ObjXL.Application.Visible = True

Set ObjXL = Nothing
Set ObjXLBook = Nothing

IRtdServer_ConnectData = objTopic.TopicValue




To use this remember to make a reference to the Microsoft Exce
Library

 

[tmarko]

Thanks KJTFS!

So I just need the correct reference and I do not need a userform to
drag the component on to right?

I will try to check it out tomorrow.

Is it easy to fill the spreadsheet with values and formulas from a
Recordset coming from the database?

Then to get out all the values calculated and raw values again

Finally tag everything up in XML?

I'm really curious if this will be fast especially if a lot of users
will access the server at the same time?

 

[KJTFS]

Yes the reference will let you get at the functionality of it.

I have added a zip file with just a concept of using th
application.worksheetfunction


Yes it is I think there is functionality of pushing it all into a tabl
and doing it, or you can loop thru the recordset (this is a littl
slower I think) if you are doing it on a server and need speed I woul
suggest looking into the above mentioned concept, I haven't tried it s
I dont know.
Yes XML is good. I am using it right now in a Real Time Data serve
and it is good. I know how to read it in quickly but I still have t
investigate how to create XML files in VBA, right now I just have i
making a XML string...... It should be fast, it doesnt need to creat
a new excel file and open it. (that is why I am using XML now, exce
is tooooooo bulky to open quickly.)

Hope that helps.

Keith
www.ktjfs.co

Attachment filename: example.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=41057

 

[Tom Ogilvy]

Maybe the conversation changed, but

Set ObjXL = CreateObject("Excel.Application")

opens an instance of Excel, not the office spreadsheet component.

just an observation.

 

[KJTFS]

Yes originally it was thought that he needed to open an excel to perfor
what was needed then we figured just excel formulas were needed.

Keith
www.kjtfs.com

 

[tmarko]

I'm a bit mixed up now after reading trough the stuff. I thought
understood during the weekend, but Now I'm a bit lost.

I do not want to open up an instance of Excel on the server do I?

Shouldn't I access the spreadsheet component? I want to mak
calculations on my server in an Active X dll

How do I do that programmatically?

I've been trying to

Dim wksheet as worksheet

or should I dim a workbook as well

then I would like to simulate things like

WkSheet.Cells(lngRow, lngCol + 1) = rsA("Value")

or

WkSheet.Cells(lngRow, lngCol).Name = rsA("Mnemonic")

or

WkSheet.Cells(lngRow, lngCol + 1) = "=" & rsA("Formula")

finally

WkSheet.calculate

then take out the results

Am I totally way out or is this possible?

I would be really glad for some more feedback

 

[KJTFS]

I am sorry I have probably confused you as I have shown 2 ways of doing
something and havent really give much explaination of it. The method
you are using is actually creating an excel object in the memory of the
computer. And the way you are using it looks like it should work. You
will want a method like
worksheet.cells(x,y).formula = "=avg(1,2,3,4)" ' along that line

or if you just need the ability of an excel function you can do
something a little sexier which is set a variable equal to an answer
they would yeild.
yourvariable = application.worksheetfunction.average(1,2,3,4)

in the end yourvariable and cell(x,y) on the sheet refered to by
worksheet will have the same result. The difference will be that their
is not a copy of a workbook floating around in your memory in the
application.worksheetfunction way. Both methods will need a reference
to the Excel Library. I hope this has cleared things up. Sorry for
the confusion, I suffer from being able to do things but I have a poor
ability to put things into words.

Keith
www.kjtfs.com

 

[tmarko]

Thanks KJTFS for still helping me out

If I follow you I need to initiate a woorkbook then a worksheet if I
want something more than a Excel function. I have llike 200 formulas
that are dependent of each other and like 200 raw values. So I think I
need to simulate a population of an Excelsheet and then let this
calculate the values. Just dim a worksheet does not seem to work.

Well need to try something else

 

[KJTFS]

Yes it seems like you are getting it to me. Since you are using 200
linked formulas you will need the spreadsheet (unless you want to use
some funky Data Structure concepts ) . Post the code you are using
to initialize the workbook and make a reference to a cell. I dont need
to see it all but just a bit will be able to let me see what you are
doing.

Keith
www.ktjfs.com

 

[tmarko]

Here is the code I tried to use to fill the spreadsheet

Private Function CalculatePeriodicData(ByRef rsA As ADODB.Recordset) As
Long

Dim objXL As Object
Dim WkSheet As Object

Set objXL = CreateObject("OWC11.Spreadsheet.11")
Set WkSheet = objXL.Worksheets.Add

Dim o As Object
Dim strOld As String
Dim lngRow As Long
Dim lngCol As Long

WkSheet.Activate

‘Looping trough recordset

If Not rsA Is Nothing Then
If rsA.RecordCount > 0 Then
rsA.MoveFirst
Do While Not rsA.EOF


If strOld <> rsA("Mnemonic") Then
lngRow = lngRow + 1
lngCol = 1
'strRange = "A" & Trim(str(lngRow))
strOld = rsA("Mnemonic")

WkSheet.Cells(lngRow, lngCol).Name =
rsA("Mnemonic")
WkSheet.Cells(lngRow, lngCol).value = rsA("Short")

' Check if raw value or formula
If rsA("Formula") = "" Then
' raw value
WkSheet.Cells(lngRow, lngCol + 1) =
rsA("Value")

Else
' formula
If rsA("Formula") = "N/A" Then
WkSheet.Cells(lngRow, lngCol + 1) =
rsA("Formula")

Else
On Error Resume Next
WkSheet.Cells(lngRow, lngCol + 1) = "=" &
rsA("Formula")
End If
End If

'
If lngRow Mod 10 = 0 Then

WkSheet.Cells(lngRow, lngCol).Activate

End If
lngCol = 2
Else
'
If rsA("Formula") = "" Then
' raw value
WkSheet.Cells(lngRow, lngCol + 1) =
rsA("Value")

Else
' firmula
If rsA("Formula") = "N/A" Then
WkSheet.Cells(lngRow, lngCol + 1) =
rsA("Formula")

Else
On Error Resume Next
WkSheet.Cells(lngRow, lngCol + 1) = "=" &
rsA("Formula")
rsA("Formula")
End If
End If
lngCol = lngCol + 1
End If

rsA.MoveNext
Loop
End If
End If


WkSheet.calculate
Dim xmldata As String

xmldata = objXL.xmldata

These are examples of formulas I use:

(OFFSET(SH_SharePriceEOY,0,2)*OFFSET(SH_NrSharesEOY,0,2))

IF(OFFSET(IS_Sales,0,3)>0,-OFFSET(IS_Costgoodssold,0,3)/OFFSET(IS_Sales,0,3),0)


IF(OFFSET(PS_NetCFps,0,2)>0,IF(OFFSET(SH_SharePriceEOY,0,2)>0.0001,OFFSET(SH_SharePriceEOY,0,2)/OFFSET(PS_NetCFps,0,2),"n.a"),"n.m")



The raw vales go very fast (they are coming first in a Company Report).


Raw vales are approx 8000 of these 14000 cells that a Company Report
consists of.
(approx 180 rows= Raw vales) take 3 s

The formulas take MUCH MUCH longer to fill the spreadsheet....
One keyvalue can take between 20-50 s to fill the spreadsheet, (56
columns so it is 56 cells to fill with formulas per Keyvalue)

A total of 100 rows (typical Company Report has 100 formula based
keyvalues) take more than 3000s = 1 hr
50 min to fill the spreadsheet with formulas is NOT OK .

So please get back if you've got an idea to speed everything up. Maybe
change the formulas or populate the spreadsheet in an other way.

Thanks for the help so far!

 

[KJTFS]

Application.Calculation = xlCalculationManual

try setting this property in the very begining of the code. You migh
need to work with that application object a little but I think yo
should be fine, but try if it errors try objXL.application and othe
things, you seem to know what you are doing so you should know what
mean.

Then at the end of the code reverse it

application.calculation = xlcalculationautomatic


(or however you get the app object working.)

Hope that helps.

Keith
www.kjtfs.co

 

[tmarko]

Thanks! Will try that tomorrow.

Sounds like the problem is that the spreadsheet make a calculatio
every time I insert a new formla and become busy all the time.

And with your suggestion I let the spreadsheet make ONE calculation i
the end after all formulas are inside the spreadsheet.

Will be interesting to make the new timing

Then to see how the component work with a lot of users accessing m
server and making calculations

 

[KJTFS]

Yes I am hoping that is what the issue is. We will see, keep m
posted.


Keith
www.kjtfs.co

 

[tmarko]

Now I'm down to 1 min totally on my developing machine, so should be
faster ona "real" server. Might need to enhance it even faster. My
users are really "timeoholics"

Used:


objXL.Calculation =xlCalculationManual

after looping through rsA

objXL.calculate



Thanks.

Now I will be working on filtering the xml data and place it in
Excel2003 and a web browser