Adding a reference to an object library in code (VBA for Word 97/2000)

Topic

New Reply

I’ve looked through posts, unless I missed one, on these subject, but still can’t quite get the syntax down for adding a reference to an object library in code. I need to add a reference at run time to “Microsoft Visual Basic for Applications Extensability 5.3”.

What I have so far is:

Dim ExtLib As Object
Set objCurProj = GlobTemp.VBProject
Set ExtLib = CreateObject(objCurProj.References.????)

Thanks!!
Troy

Reply | Quote

Replies

> I need to add a reference at run time to “Microsoft Visual Basic

Why?

Troy, check out the threads in this search. Looks like VBProject.References.AddFromFile may be what you want, but it sounds dangerous, hence the initial why question. HTH –Sam

Reply | Quote

The following line will add the reference, but you will need to check the path and the actual file name on your system, as it could differ :

 ThisDocument.VBProject.References.AddFromFile _
    "C:Program FilesCommon FilesMicrosoft SharedVBAVBEEXT1.OLB"

You could search for VBEEXT*, and base your file path on the result.

Andrew C

Reply | Quote

Thanks!! Almost home free!!

Just one more thing: Is there some vba element that refers to this path that I could use? Since this will be done on multiple PC’s, at some point the path probably won’t be the same.

I could probably take it from the location of

ProgramsProgram FilesCommon FilesMicrosoft Shared

I just don’t know how to find that in the code.

Thanks again!!
Troy

Reply | Quote

The location of shared files should be available from the registry. The following key has a path value which should provide what you want :

HKEY_LOCAL_MACHINESOFTWAREMicrosoftShared Toolsvbeext1.olb.

I get C:Program FilesCommon FilesMicrosoft SharedVBAVBEEXT1.OLB as the content of the path value from that key.

Andrew C

Reply | Quote

Sorry to have to drag this out one more level, but I have one more question.

I assume I can use the GetSetting function to retrieve this value. Give that, the syntax is:

GetSetting(appname, section, key[, default])

In this case I assume:

section = HKEY_LOCAL_MACHINESOFTWAREMicrosoftShared Tools
key = vbeext1.olb

But what exactly do I put for the appname? Is it “Windows” or “Word” or “Application.????”

Thanks again!! As always, I’m learning a lot!!
Troy

Reply | Quote

Troy,

The VBA functions GetSetting, SaveSetting etc only work on one Registrty key, i.e. HKEY_CURRENT_USERSoftwareMicrosoftVB and VBA Program Settings, and so cannot provide what you want. As you are using Word, VBADUDE’s little snippet will work (for Word 2000 anyway, not sure about 97).

In Excel, and other flavours of VBA and VB itself, you would need an API call to achieve the same result.

Hope you can sort things now.

Andrew C

Reply | Quote

Troy,

With all the difficulties of using different versions and possible OS differences, probably adding the reference from the library filename causes just too many problems. Luckily though you can add the reference by use the GUID (Globally Unique Identifier), which should be the same for each VBA version. Hopefully the following will resolve any problems you might be having :

    Sub AddVBEEXT_Ref()
        MajVer = Val(Left(Application.VBE.Version, 1))
        MinVer = Val(Right(Application.VBE.Version, 1))
        Application.VBE.ActiveVBProject.References. _
            AddFromGuid "{0002E157-0000-0000-C000-000000000046}", _
                MajVer, MinVer
    End Sub

It seems to work fine for me for Office 97 and 2000, WindowsME and NT. Perhaps others might try it and report any problems that might arise.

Andrew C

Reply | Quote

Andrew,

This seems like a great idea, but I get an error message when I try to run it (see attached screensnip).

Betraying my sketchy knowledge of this stuff: is the GUID universal and assigned to a library file when the file is created (and therefore the GUID for that library will be the same on every installation of that library on every machine), or rather is it the case that the OS on the individual machine assigns the GUID for that library, at the time the library is registered on that machine? Would the error message indicate the latter is the case?

Gary

Reply | Quote

Gary,

Thanks for the feedback. My own knowledge is certainly not encyclopaedic, but as I understand it, the GUID, it is generated from an algorithm that ensures that the value is unique and can never be created on a different machine. The same value should then mean the same thing to all PCs at all times. So yes the GUID is assigned once and for all to a library file (or any dll or exe and other COM and ActiveX objects).

What version of VBA are you using ?. You do need to pass the correct Major and Minor version numbers (of the VBA version, not Office version), otherwise the error you are reporting occurs. But the code as posted should get that correct. Unless there are versions of VBA with x.xx as version numbers. Try using the literal value and see what happens.

Andrew

Reply | Quote

Hi again,

Thanks for the explanation; I thought it was along those lines, but the current glitch was making me wonder…

I should have indicated in my first post (but didn’t): I’m using Office 2000 so it’s VB6.
When I step through the code, MajVer gets a value of 6 and MinVer gets 0.

So I’m stumped.
Attached though, is a snip of the dialog which displays when I click on Help > About Microsoft Visual Basic.
It does appear that versions of VBA have additional numbers, but I’m not sure whether that would affect the GUID for the VB Extensibility library. (Unless different updated versions of the extensibility library get different GUIDs – is that what’s meant by “DLL Hell”? )

Gary

Reply | Quote

Gary,

I’m a bit stumped now myself. Set a reference to the library manually and run the following :

Dim refVBE As VBIDE.Reference
For Each refVBE In ThisWorkbook.VBProject.References
Debug.Print refVBE.GUID, refVBE.FullPath
Next refVBE
Set refVBE = Nothing
End Sub

and see what value it returns for the GUID in question.

Andrew

Reply | Quote

Andrew,

You replied just as I was typing “How do I get the GUID for the VB Ext. library on my machine?”

Anyway, the mystery remains ’cause the code returns the identical identiier as in your code.

Lex is now doing pushups, which is a sure sign it’s time for me to go to bed!

Gary

Reply | Quote

I think I may have cracked it. It seems if you pass value 0 and 0 as Min and Maj version numbers it opts for the default. I suspect that your problem may be due to having an older version also available and some confilct occurs. Probably if you use 5 (the first value it meets) for the MajVer value it might work. Try that. But the following might be the best bet, (shorter too)

Sub AddVBEEXT_Ref()
    Application.VBE.ActiveVBProject.References. _
    AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
End Sub

Let me know how you get on.

Andrew

Reply | Quote

I really appreciate all the back and forth on this. I’ve learned a lot, and this works, at least it does for Word 2000.

Any idea with the GUID is for the extensibility library for Word 97?

I’ve got both on my machine, but I think Word 2000 put its own references in because I cannot find the reference entries that others with true Word97 machines have.

I’d be happy to look it up myself if I know where to look.

Thanks!!
Troy

Reply | Quote

Troy,

It is the VBA Extensibilty library, and so the samed library is used for all VBA apps, i.e it’s the same for Word, Excel, Powerpoint etc. I see SammyB pointed out there might be dangers involved. Not in setting the reference but in what you do with it. It really has nothing to do with Excel or Word, but the VBA-VBE environment, and is concerned mainly with components of that environment such as Modules and procedues etc.

The GUID for Word 97 is the same as Word 2000, the only difference being the additional parameters that are used. However as you might see from Gary’s posts above conflicts can and do happen. I replicated his situation on a PC that had VBA versions 5 & 6 libraries. The solution seems to use version numbers 0, which then use the first default reference in the registry.

If you try to set a reference that is already set you will get an error, so I am changing the code to account for this. It could easily happen if the file is saved while the reference is set, as that information is also saved. Hopefully the folllowing code will work for all versions of Office (from 97 onwards anyway) including XP, though I have not tried it. You should test your final code on all versions.

    Sub AddVBEEXT_Ref()
        On Error GoTo ErrHandler
        Application.VBE.ActiveVBProject.References. _
        AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
        On Error GoTo 0
    Exit Sub
    ErrHandler:
        If Err  32813 Then
             MsgBox "ERROR setting VBA Extensibility Reference" & _
             vbCrLf & "Error No : " & Err.Number & " " _
             & Err.Description, vbExclamation
        End If
    End Sub

Hope that helps you finalise on this.

Reply | Quote

Permit me just one more related question, and I promise I’ll end this thread (at least until I have another question ).

Can this reference be set for one VBA project FROM another? I already have an Install.doc VBA project that copies these files to the right location, and set’s one of them as a global template. Since setting the reference appearts to be a one time operation, it seems it would be appropriate to put the code in the install.doc project.

If it can be done, can you help with the syntax? I’ve tried using the VBProjects(Filename) collection, but it keeps telling me subscript out of range.

Also, if it can be done, do I need to have code to open the file first and then save it after the reference is added?

Thanks again for your patience!! I really appreciate all the time you give helping novices like me make some baby steps (or sometimes giant leaps!!).

Troy

Reply | Quote

Troy,

Try the following syntax and see if it helps
Documents(“FileName”).VBProject.References.AddFromGuid _ etc.

If the file might be moved from one PC to another running a different version of Office it might be best to set the reference when it is required, otherwise set the reference and save the file with the setting.

Hope that helps further, and do not be concerned about requesting more assistance. We all learn from doing this sort of thing. Keep the thread going as long as it takes.

Andrew C

Reply | Quote

Thanks!! I think that about does it (except for some testing I need to do with Word97 just to make sure). This is what I ended up with:

On Error GoTo ErrHandler
Documents.Open FileName:=GlobalTemplatePath & “FormatDocuments.dot”, _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:=””, PasswordTemplate:=””, Revert:=False, _
WritePasswordDocument:=””, WritePasswordTemplate:=””, Format:= _
wdOpenFormatAuto
ActiveDocument.VBProject.References. _
AddFromGuid “{0002E157-0000-0000-C000-000000000046}”, 0, 0
On Error GoTo 0
ActiveDocument.Save
ActiveWindow.Close
Exit Sub
ErrHandler:
If Err 32813 Then
MsgBox “ERROR setting VBA Extensibility Reference” & _
vbCrLf & “Error No : ” & Err.Number & ” ” _
& Err.Description, vbExclamation
End If

Thanks again for willingness to teach the less learned!!
Troy

Reply | Quote

Andrew,

Thanks for posting this and the “find GUID” code. Want to confirm that this now works fine on my PC – this is really great .

See my post re “Simpler Set Reference Dialog” for an implementation of your code.

Gary

Reply | Quote

try this snippet

Sub dd()
Dim s As String
s = System.PrivateProfileString(“”, “HKEY_LOCAL_MACHINESOFTWAREMicrosoftShared Toolsvbeext1.olb”, “Path”)
MsgBox s
End Sub

Reply | Quote

‘API method – cut and paste this code to a standard module in office 97 or 2k
‘blah blah – standard disclaimers about api calls – note fixed length string

‘——————————-

Declare Function RegOpenKeyEx Lib “advapi32.dll” Alias “RegOpenKeyExA” (ByVal hKey As Long, _
ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Declare Function RegQueryValueEx Lib “advapi32.dll” Alias “RegQueryValueExA” (ByVal hKey As Long, _
ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As Any, lpcbData As Long) As Long

Declare Function RegCloseKey Lib “advapi32.dll” (ByVal hKey As Long) As Long

Public Const HKEY_CURRENT_USER = &H80000001
Public Const HKEY_LOCAL_MACHINE = &H80000002
Public Const KEY_QUERY_VALUE = &H1
Public Const ERROR_SUCCESS = 0&
Public Const READ_CONTROL = &H20000
Public Const STANDARD_RIGHTS_READ = (READ_CONTROL)
Public Const KEY_ENUMERATE_SUB_KEYS = &H8
Public Const KEY_NOTIFY = &H10
Public Const KEY_CREATE_LINK = &H20
Public Const SYNCHRONIZE = &H100000
Public Const KEY_READ = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or _
KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not SYNCHRONIZE))
Public Const KEY_EXECUTE = (KEY_READ)
Public Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or _
KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))
Public Const REG_DWORD = 4
Public Const REG_SZ = 1
Public Const REG_BINARY = 3

‘note the fixed length string
Dim sData As String * 75
Dim sPath As String

Sub blah()

Dim hResult As Long, retVal As Long, sSubKey As String, _
sSetting As String, lType As Long, lDataLen As Long

sSubKey = “SOFTWAREMicrosoftShared Toolsvbeext1.olb”
retVal = RegOpenKeyEx(HKEY_LOCAL_MACHINE, sSubKey, 0&, KEY_QUERY_VALUE, hResult)

‘error_success = 0
If retVal = ERROR_SUCCESS Then

sSetting = “Path”
lDataLen = Len(sData)

If hResult 0 Then
retVal = RegQueryValueEx(hResult, sSetting, 0&, lType, sData, lDataLen)
Else
MsgBox “error opening key”
End If

retVal = RegCloseKey(hResult)
Else
MsgBox “Unsuccessful returning default Word file directory”
End If

sPath = sData

Do Until Asc(Right(sPath, 1)) 0
sPath = Left(sPath, Len(sPath) – 1)
Loop

MsgBox sPath
End Sub

Reply | Quote

Caveat: on my Win 2000/Office 2000 system the file name vbeext1.olb is not found, that’s the name used by Win 98/Office 97.

For Office 2000, it’s VBE6EXT.OLB.

Reply | Quote

caveats – lots of em’!

unless you know the configuration you are working with, this can be a never ending game – this code ran reliably since 98′ at a particular client’s site

we tried the same thing in an “internet downloadable” office solution – HEADACHE – trying to troubleshoot stuff via email, ARGHHHHHHH

tread lightly

Reply | Quote

FINAL NOTE –

for a good look at checking out the registry – check out VB6’s package and deployment project (SETUP1.VBP) in the PDWizard subdirectory – lots of interesting stuff going on in there

note – make sure you save it as another project before you start hacking away on it – this is what the package and deployment wizard uses to create setup packages

note2 – using the pdwizard can make for an easy way to deploy Office based solutions …. especially if they have file dependencies – this way you KNOW the file will be on there machine because you’re installing it

Reply | Quote