How to declare activeX in a VBA script

[wimoost]

Hi,
I am a complete NEWBY in VB(A).
I have a problem with accessing a DLL in my VBA-script.
The DLL is written in Delphi and if called by another Delphi apllication no errors are present.

I have declared the dll in my script as:

-------
Public Declare Function dllOpenDB Lib "vbaProgress.dll" Alias "OpenDatabase" As Integer
----
Public Declare Function qryZoekDealer Lib "H:\Apps\NetFile\vbaProgress.dll" Alias "ZoekDealer" (ByVal iDealer As Long) As Boolean
------


Till so far no problems. But after when I call the function "qryzoekdealer" for the second time then the vba appl. is hanging.

Now they told to write a COM/ActiveX instead of a oldfashioned DLL. So I did BUT i do not know HOW to declare or use the new Component.

I have registrered it with the vba appl using File-->Reference.
The name off the COM is comProgress.DLL and the classname is ConnectWithProgress.
Also this COMponent is working fine in a delphi-delphi connection.

I tried the folowing code without any luck:

------
'create a object reference to the component
Dim obj As comProgress.ConnectWithProgress

'create an instance of the object
Set obj = New ConnectWithProgress

'call the objects dllOpenDB method
obj.dllOpenDB

'end procedure
-------

Hope somebody can help me

Wim

 

[zarkon4]

If you are using a VB Script - try this

Dim obj
obj=CreateObject("comProgress.ConnectWithProgress&quot

if you are using VBA (ie From withing word or excel) then
you could do either the above or set reference to your dll
and do like your doing with the following exception

'create an instance of the object
Set obj = New comProgress.ConnectWithProgress
instead of
Set obj= New ConnectWithProgress

 

[wimoost]

@Zarkon4 Thanks, but non of the two solutions works
I prefer to use the COMServer but when I debug the script it halts on the statement Set obj=New ConnectWithProgress with error: 10092 "ActiveX Automation server cannot create object.

I hope you can help me ?

_______
Wim

 

[zarkon4]

Is the dll registered on the ComServer? If not then you need to do so

 

[wimoost]

Yes it is (in my opinion) beacuse the same dll is working if it is called by a delphi appl.

is there a different way for VB ?
(I did File----> reference and then selected the comServer)

________
Wim

 

[zarkon4]

Is the dll registered on the client pc?

Also make sure your using

Set obj=New comProgress.ConnectWithProgress

not just ConnectWithProgress, I saw that in my previous post I had ommitted this.

 

[wimoost]

Zarkon4, thx yes it is registrered on client pc
this is my code:
________________________________________
Private Sub Module_Load()

'create a object reference to the component
Dim obj As comProgress.ConnectWithProgress

'create an instance of the object
Set obj = New comProgress.ConnectWithProgress

'call the objects OpendataBase method
obj.OpenDataBase

'lTel = 0
'dllOpenDB

End Sub
__________________________________________

 

[zarkon4]

Also, make sure your reference is to the comProgress.DLL .

 

[wimoost]

How do I do that ??
With FILE ---> Reference ----> select comProgress.dll ??

I did

 

[zarkon4]

Yes, Set the reference to the dll you created.

 

[wimoost]

i did

 

[zarkon4]

I have re-read all the previous posts on this, did you write the dll using visual basic or delphi? I am assuming you wrote it in VB (not VBA or Delphi). If in VB then double check your dll code for proper formating of public functions, subs and properties, and make sure your class(s) are set up properly, delphi may want you to use certain class properties, like (MultiUse or GlobalMultiUse, etc).
It sounds like something is not right with the Dll you created.

 

[wimoost]

ok zarkon4 thx.
The dll is written in Delphi (by myself)
Its working great if it is called form another delphi apllication.
But this application i have now problems with is a commercial package wich uses Vb as the sript language (sax engine) I am pretty good with delphi but have no idea about vb. For me the weekend starts now and I will re-read everything and let you know by monday my findings.
Appreciate it very much for your quick responses.
Have a great weekend !!
_______
Wim

 

[wimoost]

Zarkon, I've checked and double-checked everything but I cannot find any typing errors or things I forgot. I hope you or somebody else have some suggestions.

_________
Wim