How to late-bind Excel

[maurizio]

I'm using a DLL (written in Delphi) from VBA in order to access a db
server.
As in any case i have a dll initialization, i'd like to provide the dll
with a late
binding to Excel. I don't like GetObject, because you're never sure
WHICH
instance of Excel are you hooking into.

On the other side, many years ago i knew how to pass in the
initialization
routine an Excel reference (i guess it was Excel.Application: converted
to a long?)
and how to retrieve from this a correct reference to Excel inside the
dll initialization
routine. Unfortunately i forgot how to do this: anyone with a solution
to this ?

 

[Bob Phillips]

You could always get a new instance of Excel using CreateObject.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[maurizio]

Bob Phillips ha scritto:

You could always get a new instance of Excel using CreateObject.

Not really, the initiating application is Excel (maybe it was not
clear),
therefore CreateObject is useless.

 

[Bob Phillips]

So why do you need to access Excel at all, you have one?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[NickHK]

maurizio,
Your Delphi DLL would need some Property/Function that accepts "MyXL As
Object", as you want to use late binding, hence you cannot use "MyXL As
Excel.Application".
GetObject is irrelevant in early versus late binding : It is whether you set
a reference to the required library (and hence you can use the exposed
objects) versus no such reference (and everything is a generic Object).

In VBA, something like:
Set MyDelphiDLL.SetXLInstance=Application

where you Delphi DLL has a Property (whatever this VB is in Delphi)
Property Set SetXLInstance (vData As Object)
'vData is no your Delphi reference to the Excel instance

NickHK

 

[maurizio]

1) Delphi can use Excel as a server with early binding (binding Excel's
type library),
but this adds about 350 kb to its code: i don't like it.

2) My DLL is, mostly, a library of functions that are called from VBA.
However, having
the right reference, the DLL can interact with and/or transfer data to
Excel directly.
Further, it would be nice to move part of the code from VBA to the DLL
(maybe
not faster, but at least to protect the code).

3) For Delphi, the reference to Excel is simply a variant (to be
precise an OleVariant,
i.e. a variant compatible with ole types). You create it, for example,
with the usual

xlapp := GetObject ('Excel.Application');

(well, in Delphi the method is called GetActiveOleObject )

4) Delphi knows how to treat xlapp as a IDispatch interface, so it
accepts a
statement like

xlapp.Caption := 'My application';

which is identical to a vba statement.

The whole point was only related to the inconvenience of the GetObject
method:
assuming we have multiple copies of Excel running at the same time, how
do you make yourself sure that you're are linking your dll to the RIGHT
instance ?

My idea was to pass to the dll (in an initialization step) the object
Excel.Application:
after all this should be passed somehow as an address, from which it
should be
possible to recover the right value to initialize the xlapp variable in
Delphi.

Hope this makes a little bit more clear the original post and its
purpose.

 

[NickHK]

maurizio,
As I know nothing of Delphi, I can't help you with that, but in a class in a
VB DLL, I'd do something like:

'**<clsMyXL.cls>
Private MyXLApp As Object

Property Set SetXLInstance(vData As Object)
Set MyXLApp = vData
'Do some checking to make it is an Excel.Application object
End Property

Public Function ManipulateXL(argNewCaption As String) As Long
If MyXLApp Is Nothing Then Exit Function
With MyXLApp
.Caption=argNewCaption
'Or whatever you need to do
End With
End Function

Private Sub Class_Initialize()
'Any code you need
End Sub

Private Sub Class_Terminate()
'release the reference
Set MyXLApp = Nothing
End Sub
'**</clsMyXL.cls>

And calling this from VBA:
Dim MyDelphiClass=MyDelphiDLL.clsMyXL
Set MyDelphiClass=New MyDelphiDLL.clsMyXL
With MyDelphiClass
Set .SetXLInstance=Excel.Apllication
.ManipulateXL "New Caption"
End With
'etc........

NickHK