Geek Answers Handbook

How to programmatically add a link to a VBA project?

I am deploying an early-binding VBA module that requires Scripting.Dictionary and RegExp .

The script is not expected to work when it runs on another computer. The user must go to Tools-> Reference in the VBA IDE and add a link to these two libraries manually to make it work.

Hence the problem. After asking a non-technical end user to go to the IDE and manually add links, they ask for too many of them.

Another alternative is to rewrite the whole (a very long script written by someone else) to use late binding. I prefer not to take this path if there are other methods.

As an alternative, some people suggest adding the link programmatically like this:

Application.VBE.ActiveVBProject.References.AddFromFile [Path to library]

  • Is this the right decision, and if so, are there flaws in this strategy?
  • If not, are there other methods that will allow the code to stay early, but do not require the links to be manually added by the user.

Suggestions related to direct calls to the Win32 / 64 API are also welcome.

Thank.

+5
source share
1 answer

( # , , , ), , - , , . ( , .) , Excel.

GUID.

- , - . ( script, , Subversion, .) , . (EH , ...)

'Export refs in existing workbook to text file
Private Sub exportRefs_(srcWbk As Workbook)
    Dim fs As FileSystemObject
    Set fs = New FileSystemObject

    Dim tsout As TextStream
    Set tsout = fs.CreateTextFile(fs.BuildPath(getTargetPath_(srcWbk), "refs.refs"))

    Dim ref As Reference
    For Each ref In Application.ThisWorkbook.VBProject.References
        Call tsout.WriteLine(ref.GUID)
    Next ref

    '<EH + cleanup...>
End Sub


'Add refs to newly created workbook based on previously exported text file
Private Sub importRefs_(wbk As Workbook, path As String)
    Dim fs As FileSystemObject
    Set fs = New FileSystemObject

    Dim tsin As TextStream
    Set tsin = fs.OpenTextFile(path)

    Dim line As String
    Dim ref As Reference

    While Not tsin.AtEndOfStream
        line = tsin.ReadLine()

        Set ref = Nothing

        On Error Resume Next
            Set ref = wbk.VBProject.References.AddFromGuid(line, 0, 0)
        On Error GoTo 0

        If ref Is Nothing Then
            Debug.Print "add failed: " & line
        End If
    Wend

    '<EH + cleanup...>
End Sub

, , , , .

+3

More articles:


All Articles