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?

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

-----------------------------------------------------------------------------------------------

Also the library is really the DLL located in this folder

C:\Program Files\Common Files\System\ado\msado15.dll






"hitmonkong" wrote:

> 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?

See if this works

Application.RegisterXLL "msado15.dll"


"hitmonkong" wrote:

> 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?

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" wrote:

> See if this works
>
> Application.RegisterXLL "msado15.dll"
>
>
> "hitmonkong" wrote:
>
> > 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?

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" wrote:

> 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" wrote:
>
> > See if this works
> >
> > Application.RegisterXLL "msado15.dll"
> >
> >
> > "hitmonkong" wrote:
> >
> > > 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?

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

"Joel" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > See if this works
> > >
> > > Application.RegisterXLL "msado15.dll"
> > >
> > >
> > > "hitmonkong" wrote:
> > >
> > > > 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?

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

"hitmonkong" <(E-Mail Removed)> wrote in message
news:43D73FEC-9D91-4EC0-A34B-(E-Mail Removed)...
> 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?

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" wrote:

> 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
>
> "Joel" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > See if this works
> > > >
> > > > Application.RegisterXLL "msado15.dll"
> > > >
> > > >
> > > > "hitmonkong" wrote:
> > > >
> > > > > 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?

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" wrote:

> 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" wrote:
>
> > 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
> >
> > "Joel" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > See if this works
> > > > >
> > > > > Application.RegisterXLL "msado15.dll"
> > > > >
> > > > >
> > > > > "hitmonkong" wrote:
> > > > >
> > > > > > 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?

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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
> > >
> > > "Joel" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > See if this works
> > > > > >
> > > > > > Application.RegisterXLL "msado15.dll"
> > > > > >
> > > > > >
> > > > > > "hitmonkong" wrote:
> > > > > >
> > > > > > > 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?