Automatic add reference to Excel VBA via c#

Probably the easiest solution is to use late binding in your VBA macro. For example, if in my subroutine I am declaring:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

This requires the reference to the Microsoft ActiveX Data Objects X.X Library in order to run. However, by declaring your objects as such:

Dim cn As Object, rs As object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

You can likely avoid the issues with having to add the reference. There is a decent discussion and example of an entire subroutine that is early bound vs. late bound here.

Alternatively, you can add references through VBA itself. In this example you could open the Workbook with C#, then call a macro that will check for the appropriate reference (and add it if it is missing). The code below is taken from here.

Sub AddReference() 
     'Macro purpose:  To add a reference to the project using the GUID for the
     'reference library

    Dim strGUID As String, theRef As Variant, i As Long 

     'Update the GUID you need below.
    strGUID = "{B691E011-1797-432E-907A-4D8C69339129}" 

     'Set to continue in case of error
    On Error Resume Next 

     'Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1 
        Set theRef = ThisWorkbook.VBProject.References.Item(i) 
        If theRef.isbroken = True Then 
            ThisWorkbook.VBProject.References.Remove theRef 
        End If 
    Next i 

     'Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear 

     'Add the reference
    ThisWorkbook.VBProject.References.AddFromGuid _ 
    GUID:=strGUID, Major:=1, Minor:=0 

     'If an error was encountered, inform the user
    Select Case Err.Number 
    Case Is = 32813 
         'Reference already in use.  No action necessary
    Case Is = vbNullString 
         'Reference added without issue
    Case Else 
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _ 
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _ 
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!" 
    End Select 
    On Error GoTo 0 
End Sub

The only thing that you will need to change is the strGUID variable. You can use this little table below to get the appropriate strGUID for whichever version you want to use. You may want to remove the message box portion too, depending on what exactly you are doing with Excel.

╔═════════════════════════╦════════════════════════════════════════╗
║ Microsoft ADODB Version ║                  GUID                  ║
╠═════════════════════════╬════════════════════════════════════════╣
║ 2.5                     ║ {00000205-0000-0010-8000-00AA006D2EA4} ║
║ 2.6                     ║ {00000206-0000-0010-8000-00AA006D2EA4} ║
║ 2.7                     ║ {EF53050B-882E-4776-B643-EDA472E8E3F2} ║
║ 2.8                     ║ {2A75196C-D9EB-4129-B803-931327F72D5C} ║
║ 6.1                     ║ {B691E011-1797-432E-907A-4D8C69339129} ║
╚═════════════════════════╩════════════════════════════════════════╝

And to find these GUID values, I used the code below from here.

Sub ListReferencePaths()
     'Macro purpose:  To determine full path and Globally Unique Identifier (GUID)
     'to each referenced library.  Select the reference in the Tools\References
     'window, then run this code to get the information on the reference's library

    On Error Resume Next
    Dim i As Long
    With ThisWorkbook.Sheets(1)
        .Cells.Clear
        .Range("A1") = "Reference name"
        .Range("B1") = "Full path to reference"
        .Range("C1") = "Reference GUID"
    End With
    For i = 1 To ThisWorkbook.VBProject.References.Count
        With ThisWorkbook.VBProject.References(i)
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = .Name
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1) = .FullPath
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 2) = .GUID
        End With
    Next i
    On Error GoTo 0
End Sub