[RESOLVED] Register DLL's at runtime (to allow for late-binding)

[bobbo]

The first way to use DLL functions is to early bind, using Tools>References on the VBE menu. However, this prevents you from deploying your code (unless you manually set up each user).

The second way is late binding using the CreateObject command. The downside here (besides a minor performance hit) is that the DLL must be registered on the user's computer before calling the CreateObject command. Example:

'dsofile.dll found at: http://support.microsoft.com/kb/224351
Shell "regsvr32 /s """ + ThisWorkbook.Path + """\dsofile.dll""", vbHide
Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open "yourfile.xls", ReadOnly:=True
MsgBox objFile.SummaryProperties.Category
Shell "regsvr32 /s /u """ + ThisWorkbook.Path + """\dsofile.dll""", vbHide

Problem solved, right? Almost. In a locked-down corporate environment, users won't have admin rights and the DLL registration may fail. (This depends on the DLL: specifically, a non-admin can't write to the HKEY_LOCAL_MACHINE registry key.) The work-around is to "manually" register the file into the HKEY_CURRENT_USER key instead. For the dsofile.dll example, it looks like this:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
Set objEnv = objShell.Environment("Process")
strSourceFile = ThisWorkbook.Path & "\dsofile.dll"
strDllFile = objEnv("TEMP") & "\dsofile.dll"
If Not objFSO.FileExists(strDllFile) Then
    objFSO.CopyFile strSourceFile, strDllFile, False
End If
strRegFile = objEnv("TEMP") & "\dsofile.reg"
If objFSO.FileExists(strRegFile) Then
    objFSO.DeleteFile strRegFile
End If
Set objOpenRegFile = objFSO.OpenTextFile(strRegFile, 2, True)
objOpenRegFile.WriteLine ("Windows Registry Editor Version 5.00")
objOpenRegFile.WriteLine ("")
objOpenRegFile.WriteLine ("[HKEY_CURRENT_USER\SOFTWARE\Classes\DSOFile.OleDocumentProperties]")
objOpenRegFile.WriteLine ("@=""DSO OLE Document Properties Reader 2.1""")
objOpenRegFile.WriteLine ("")
objOpenRegFile.WriteLine ("[HKEY_CURRENT_USER\SOFTWARE\Classes\DSOFile.OleDocumentProperties\CLSID]")
objOpenRegFile.WriteLine ("@=""{58968145-CF05-4341-995F-2EE093F6ABA3}""")
objOpenRegFile.WriteLine ("")
objOpenRegFile.WriteLine ("[HKEY_CURRENT_USER\SOFTWARE\Classes\CLSID\{58968145-CF05-4341-995F-2EE093F6ABA3}]")
objOpenRegFile.WriteLine ("@=""DSOFile OleDocumentProperties""")
objOpenRegFile.WriteLine ("")
objOpenRegFile.WriteLine ("[HKEY_CURRENT_USER\SOFTWARE\Classes\CLSID\{58968145-CF05-4341-995F-2EE093F6ABA3}\InprocServer32]")
objOpenRegFile.WriteLine ("""ThreadingModel""=""Apartment""")
objOpenRegFile.WriteLine ("@=""" & Replace(strDllFile, "\", "\\") & """")
objOpenRegFile.WriteLine ("")
objOpenRegFile.WriteLine ("[HKEY_CURRENT_USER\SOFTWARE\Classes\CLSID\{58968145-CF05-4341-995F-2EE093F6ABA3}\ProgID]")
objOpenRegFile.WriteLine ("@=""DSOFile.OleDocumentProperties""")
objOpenRegFile.WriteLine ("")
objOpenRegFile.Close
Shell "regedit /s """ + strRegFile, vbHide

'now continue with CreateObject as before
Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open "yourfile.xls", ReadOnly:=True
MsgBox objFile.SummaryProperties.Category

The trick is to know what to put in the .reg file: I did this by registering the dll on my system (logged in as an admin) and found what keys it used. Then I exported them, replaced HKEY_LOCAL_MACHINE with HKEY_CURRENT_USER, and saved this as my new .reg file.

The net result is you can simply put a copy of your DLL in the folder with your document, and your code works with zero client configuration. (One note: If you replace a DLL with a new one, it may break your "manual" registration. Be sure to review your code before deploying a new DLL.)