Add Microsoft ActiveX Data Objects 2.8 Library in code

[hitmonkong]

Hi,

Is there any way that I can add Microsoft ActiveX Data Objects 2.8 Library
in the VBA code? When we distribute our product, we want to lock our VBA
codes and so the users will not be able to add/remove the references.
Therefore, we need to be able to add the Microsoft ActiveX Data Objects 2.8
Library manually in the code.

Is taht possible?

 

[Joel]

In VBA help I found the results below (Search for the string)

Set a Reference to a Type Library


In this help item it says the following:

-----------------------------------------------------------------------------------------------
If you haven't set a reference to the Microsoft Excel type library, you must
declare the variable as a generic variable of type Object. The following code
runs more slowly.

Dim appXL As Object

 

[Joel]

See if this works

Application.RegisterXLL "msado15.dll"

 

[hitmonkong]

Hi Joel,

Thank you very much for your help. Unfortunately, it didn't work. I think
you will still need to manually add the reference from VBA Tool.

hitmonkong

 

[Joel]

As long as you refernce the objects as object and the size of variables as
they are defined in the DLL you don't really need the reference. Excel can
learn an object from the object header. You have may have to declar some of
the variables variants.

You can call any function in a dll by defining the function like below

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

The only problem is the path of the DLL may vary from PC to PC.

 

[hitmonkong]

I also found a way to do it.

ActiveWorkbook.VBProject.References.AddFromGuid _
"{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5

The only thing is that you will need to know the GUID for the reference you
are trying to add. I am not sure if GUID is the same from machine to machine
either.

hitmonkong

 

[Peter T]

Just for ideas, will need more error handling and checking to verify 2.8
exists on user's system, if not keep or grab the newest version available.

Sub test()
' needs trust access to vb Project required
Dim bFlag As Boolean
Dim objRefs As Object, objRef As Object

Set objRefs = ThisWorkbook.VBProject.References

On Error Resume Next

Set objRef = objRefs.Item("ADODB")
If Not objRef Is Nothing Then
bflag = objRef.Major = 2 And objRef.Minor = 8
If Not bflag Then
' probably best not to do this !
objRefs.Remove objRef
Set objRef = Nothing
End If
End If

If Not bflag Then
Set objRef = _
objRefs.AddFromGuid("{2A75196C-D9EB-4129-B803-931327F72D5C}", 2, 8)
End If

End Sub

Regards,
Peter T

 

[Joel]

I would think the addfrom file method is better. Try this code. Will always
work

Pfiles = Environ("CommonProgramFiles")
Set FSearch = Application.FileSearch
With FSearch
.LookIn = Pfiles
.SearchSubFolders = True
.Filename = "msado15.dll"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
If .FoundFiles.Count = 0 Then
MsgBox ("Did not find file msado15.dll - exiting macro")
Exit Sub
End If
FName = .FoundFiles(1)
ActiveWorkbook.VBProject.References.AddFromFile FName

End With

 

[hitmonkong]

Thank you so much. It definitely is much better than using the GUID and it
actually works as well. I just need to figure out how to set the "Trust
access to Visual Basic Project" a default.

Jill

 

[Joel]

Automatically setting the trust Access would be a security violation and I
don't think it can be full automatic.

Maybe a sset of key commands

ActiveSheet.Application.SendKeys keys:="%", Wait:=True

ActiveSheet.Application.SendKeys keys:="%T", Wait:=True

ActiveSheet.Application.SendKeys keys:="%M", Wait:=True

ActiveSheet.Application.SendKeys keys:="%S", Wait:=True

ActiveSheet.Application.SendKeys keys:="%T", Wait:=True

ActiveSheet.Application.SendKeys keys:="%V", Wait:=True

 

[hitmonkong]

Joel,

Thank you so much once again for your help and advice.

I don't think there is any way to automatically check the trust Access (the
method you suggested didn't work either). I am have to resort back to using
the GUID. However, that's going to be dangerous because it is possible that
not all users that we are distributing the package to will have the same
GUID.

hitmonkong