I have not done real COM in a number of years, what with working mostly with C# nowadays, and have a small issue now.

I was asked yesterday for help by an Excel-wizard who writes complex spreadsheets for the Risk Management Team at our company. They are not IT people so they do not have access to certain SQL Server data directly, but only through an intranet interface written years ago.

This guy creates complex Risk Management formulas in XLS that he distributes to his team-mates. Many times they need to get info from corporate databases so they have to stop, load a browser and type a value to get a one string answer (e.g. know CustNo and need CustName). This is a PITA because values and queries are sometimes complex (more so than this trivial example though certainly that is one of them), and they are needed several times for several Excel rows (as lookups).

Easy, I said. I can produce a COM DLL that encapsulates the many functions they need and impersonates the needede SQL Server access they do not have (nor knowledge of how to do a query).

I quickly produced a proof-of-concept with the client_Number_Name example above and he was delighted as he could easily use it as an Excel Add-on that becomes transparent to his users as an Excel native function.

This is a basic skeleton of what I did:

#include c:\common\include\library.h

Define Class MyClass AS Session OLEPUBLIC
cServer = "DB_ServerNameHere"
cConnString = "connection string to SQL Server here"
oConnObject = "whatever we need here to connect"
* other properties here
*...

* define COM Attributes for properties (R/O) and Methods to be hidden/exposed through COM
cServer_COMATTRIB[1] = COMATTRIB_RESTRICTED+COMATTRIB_NONBROWSABLE+COMATTRIB_READONLY
*...

DIMENSION Cust_Name_COMAttrib[5]
Cust_Name_COMATTRIB[1] = 0
Cust_Name_COMATTRIB[2] = "Return Customer's Name from CustNo"
Cust_Name_COMATTRIB[3] = "Cust_Name"
Cust_Name_COMATTRIB[4] = "String"
Cust_Name_COMATTRIB[5] = 1

********************************
Procedure Cust_Name(cCustNo as String) As String
   * connect and do something to retrieve a value
EndProc

The functions I wrote work fine but my question is on the exposure issue.
By declaring "As Session" I automaticaly hide most-everything. I do not want to confuise them so I want to expose only what they need to access. Therefore I hid some properties (e.g. "cServer") as internal. I loaded the object in an Object Browser (VFP and also the one in VS 2005) and indeed the visibilite/exposure is correct but I do not get the Cust_Name descriptiojn (or any description in my own functions), as defined in _COMATTRIB[2].

What am I doing wrong? Or are the _COMATTRIB array specs wrong?

Bonus question. Considering they will be using these functions (in the DLL) as Excel functions, therefore sometimes recalculated in hundreds of rows, sometimes not at all for periods of time, would it better to maintain an open connection once in the DLL (in INIT) or to connect/disconnect in each Function/Procedure? Not sure about this one. Timing woul dbe faster with an open connection to SQL Server (through our LAN), but I'm not happy with 10 or so users haviong open connections for long periods of time dutring the workday.

Sorry for the long-winded post.

[Update] I think it should be open-close in each function even though it might take time (I'll have to test when he teaches me how to do add-ons in Excel :-), as I have no idea how/when the object gets released/connection closed from Excel if I establish the connection in INIT().