current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 15 down vote favorite
9

I can't seem to figure out how to add a reference programmatically to Excel 2007 using vba. I know how to do it manually Open VBE --> Tools --> References --> browse --_> File Location and Name. But that's not very useful for my purposes.

I know there are ways to do it in Access Vb.net and code similar to this kept popping up, but I'm not sure I understand it, or if it's relevant. 

ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:="{0002E157-0000-0000-C000-000000000046}", _
    Major:=5, Minor:=3

Edit: Thanks for your quick responses.They've helped quite a bit but so far no solution will be viable for my problem. (which is clearly my fault for not giving enough details). Here's exactly what I'm trying to do.

I've written a program that runs and messages skype with information when if finishes. I need to add a reference for Skype4COM.dll in order to send a message through Skype. We have a dozen or so computers on a network and a shared file server (among other things). All of the other computers need to be able to run this program. I was hoping to avoid setting up the reference by hand. I had planned on putting the reference in a shared location, and adding it programmatically when the program ran. And that's where I got stumped and asked my original question.

So far, in the solutions presented, in order to add the reference programmatically I will need to add a reference by hand and change the trust centre...which is more than just adding the reference.

Though I guess if I follow through with the solutions proposed I will be able to add future references programmatically. Which probably makes it worth the effort.

Any further thought would be great.

share|improve this question
add comment

3 Answers

up vote 20 down vote accepted

Ommit

There are two ways to add references via VBA to your projects

1) Using GUID

2) Directly referencing the dll.

Let me cover both.

But first these are 3 things you need to take care of

a) Macros should be enabled

b) In Security settings, ensure that "Trust Access To Visual Basic Project" is checked

enter image description here

c) You have manually set a reference to `Microsoft Visual Basic for Applications Extensibility" object

enter image description here

Way 1 (Using GUID)

I usually avoid this way as I have to search for the GUID in the registry... which I hate LOL. More on GUID here.

Topic: Add a VBA Reference Library via code

Link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=267

Way 2 (Directly referencing the dll)

This code adds a reference to Microsoft VBScript Regular Expressions 5.5

Option Explicit

Sub AddReference()
    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As Reference
    Dim BoolExists As Boolean

    Set VBAEditor = Application.VBE
    Set vbProj = ActiveWorkbook.VBProject

    '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
    For Each chkRef In vbProj.References
        If chkRef.Name = "VBScript_RegExp_55" Then
            BoolExists = True
            GoTo CleanUp
        End If
    Next

    vbProj.References.AddFromFile "C:\WINDOWS\system32\vbscript.dll\3"

CleanUp:
    If BoolExists = True Then
        MsgBox "Reference already exists"
    Else
        MsgBox "Reference Added Successfully"
    End If

    Set vbProj = Nothing
    Set VBAEditor = Nothing
End Sub

Note: I have not added Error Handling. It is recommended that in your actual code, do use it :)

EDIT Beaten by mischab1 :)

share|improve this answer
1  
+1 for awesome detail. Great response. –  Ommit Mar 27 '12 at 15:19
    
So it seems that instead of adding the reference by hand I need to add a separate reference by hand and change excel permissions? Granted it will be better in the future, but it seems a little funny now. –  Ommit Mar 27 '12 at 20:09
    
Yes it does sound funny but then that's how it is at the moment :) –  Siddharth Rout Mar 27 '12 at 20:25
    
Great answer. FYI users of this, notice that you still can't use any Word/PowerPoint/other object or Enum outside of a function or procedure with this since the compiler will fail before the WORKBOOK_OPEN event begins executing. So you can't create a Public Word object and you can't define the type of a parameter as a Word/PPT type (eg you can't do something like Sub CopyActiveChartToWord(FormatType as WdPasteDataType)). –  s_a Apr 7 at 16:28
add comment
up vote 7 down vote

There are two ways to add references using VBA. .AddFromGuid(Guid, Major, Minor) and .AddFromFile(Filename). Which one is best depends on what you are trying to add a reference to. I almost always use .AddFromFile because the things I am referencing are other Excel VBA Projects and they aren't in the Windows Registry.

The example code you are showing will add a reference to the workbook the code is in. I generally don't see any point in doing that because 90% of the time, before you can add the reference, the code has already failed to compile because the reference is missing. (And if it didn't fail-to-compile, you are probably using late binding and you don't need to add a reference.)

If you are having problems getting the code to run, there are two possible issues.

  1. In order to easily use the VBE's object model, you need to add a reference to Microsoft Visual Basic for Application Extensibility. (VBIDE)
  2. In order to run Excel VBA code that changes anything in a VBProject, you need to Trust access to the VBA Project Object Model. (In Excel 2010, it is located in the Trust Center - Macro Settings.)

Aside from that, if you can be a little more clear on what your question is or what you are trying to do that isn't working, I could give a more specific answer.

share|improve this answer
    
+1 for posting it before me ;) –  Siddharth Rout Mar 26 '12 at 21:42
add comment
up vote -2 down vote

This is the code I have used in VB.Net

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range

' Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = True

' Get a new workbook.
oWB = oXL.Workbooks.Add
oSheet = oWB.ActiveSheet

The class below outputs an Excel spreadsheet based on the contents of 2 data tables. It's VB.Net but I believe that the OLE Automation is the same for VBA.

You need to include references

Interop.Microsoft.Office.Interop.Excel, Microsoft Excel 14.0 Object Library

This code is 8 years old so sorry there is so much of it but it includes some useful fixes and workarounds to make a proper job.

'Option Strict Off to allow automation of MS Office components
Option Strict Off
Imports System.IO
Imports Microsoft.Office.Core

Public Class ResultXls

    '///////////
    ' Variables
    '///////////

    Private Const _ReportName = "Result"

    Enum WorksheetRows
        EventTitle = 1
        RaceTitle
        RaceDate
        ResultsHeader
        ResultsData
    End Enum

    'Pos, Boat No, Boat Name, Driver, Navigator, Laps, Time, NMiles, Miles, Km, Knots, Mph, Kmph, Points
    Enum BoatHeadings
        Pos
        BoatNo
        BoatName
        Driver
        Navigator
        Laps
        Time
        NMiles
        Miles
        Km
        Knots
        Mph
        Kmph
        Points
    End Enum

    'Class variables
    Private _RaceDb As RaceDb

    Public Sub New(ByVal RaceDB As RaceDb)
        _RaceDb = RaceDB
    End Sub

    '///////////////////
    ' Public Procedures
    '///////////////////

    Public Sub CreateExcelWorksheet(ByVal RaceId As Integer)

        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range

        'Load datatables
        Dim RaceDataTable As DataTable = _RaceDb.GetRaceInfoDataTable(RaceId)
        Dim RaceResultDataTable As DataTable = _RaceDb.GetRaceResultsDataTable(RaceId)

        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

        '************
        ' Page Setup
        '************

        'oSheet.Name = Convert.ToDateTime(RaceDataTable.Rows(0)("RaceDate")).ToString("yyyy-MM-dd") + " - " + RaceDataTable.Rows(0)("RaceName").ToString() + " - " + RaceDataTable.Rows(0)("RaceClass").ToString()

        oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
        oSheet.PageSetup.FitToPagesWide = 1
        oSheet.PageSetup.FitToPagesTall = 1
        oSheet.PageSetup.CenterHorizontally = True

        '******************
        ' Results Headings
        '******************

        'Pos, Boat No, Boat Name, Driver, Navigator, Laps, Time, NMiles, Miles, Km, Knots, Mph, Kmph, Points

        'Set headings
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Pos + 1).Value = "Pos"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.BoatNo + 1).Value = "Number"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.BoatName + 1).Value = "Boat"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Driver + 1).Value = "Driver"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Navigator + 1).Value = "Navigator"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Laps + 1).Value = "Laps"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Time + 1).Value = "Time"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.NMiles + 1).Value = "NMiles"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Miles + 1).Value = "Miles"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Km + 1).Value = "Km"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Knots + 1).Value = "Knots"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Mph + 1).Value = "Mph"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Kmph + 1).Value = "Kmh"
        oSheet.Cells(WorksheetRows.ResultsHeader, BoatHeadings.Points + 1).Value = "Points"

        'Format headings
        Dim headingRange As Excel.Range = oSheet.Range("A" + Convert.ToString(WorksheetRows.ResultsHeader), "N" + Convert.ToString(WorksheetRows.ResultsHeader))
        headingRange.Font.Name = "Arial"
        headingRange.Font.Size = 10
        headingRange.Font.Bold = True
        headingRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
        headingRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
        headingRange.Borders.Weight = Excel.XlBorderWeight.xlThin

        '**************
        ' Results Data
        '**************

        'Pos, Boat No, Boat Name, Driver, Navigator, Laps, Time, NMiles, Miles, Km, Knots, Mph, Kmph, Points

        'Load boat data
        Dim boat As Integer
        Dim datarow As DataRow
        For boat = 0 To RaceResultDataTable.Rows.Count - 1

            'BoatHeadings.Pos           '"Position", "Pos"                  
            'BoatHeadings.BoatNo        '"BoatNumberText", "Number"         
            'BoatHeadings.BoatName      '"BoatName", "Boat"                 
            'BoatHeadings.Driver        '"Driver", "Driver"                 
            'BoatHeadings.Navigator     '"Navigator", "Navigator"           
            'BoatHeadings.Laps          '"TotalLapsCompleted", "Laps"       
            'BoatHeadings.Time          '"RaceElapsedTimeHHMMSS", "Time"    
            'BoatHeadings.NMiles        '"CompletedNm", "NMiles"            
            'BoatHeadings.Miles         '"CompletedMiles", "Miles"          
            'BoatHeadings.Km            '"CompletedKm", "Km"                
            'BoatHeadings.Knots         '"RaceSpeedKnots", "Knots"          
            'BoatHeadings.Mph           '"RaceSpeedMph", "Mph"              
            'BoatHeadings.Kmph          '"RaceSpeedKmh", "Kmh"              
            'BoatHeadings.Points        '"Points", "Points"                 

            datarow = RaceResultDataTable.Rows(boat)

            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Pos + 1).Value = datarow("Position")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.BoatNo + 1).Value = datarow("BoatNumberText")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.BoatName + 1).Value = datarow("BoatName")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Driver + 1).Value = datarow("Driver")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Navigator + 1).Value = datarow("Navigator")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Laps + 1).Value = datarow("TotalLapsCompleted")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Time + 1).Value = datarow("RaceElapsedTimeHHMMSS")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.NMiles + 1).Value = datarow("CompletedNm")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Miles + 1).Value = datarow("CompletedMiles")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Km + 1).Value = datarow("CompletedKm")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Knots + 1).Value = datarow("RaceSpeedKnots")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Mph + 1).Value = datarow("RaceSpeedMph")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Kmph + 1).Value = datarow("RaceSpeedKmh")
            oSheet.Cells(WorksheetRows.ResultsData + boat, BoatHeadings.Points + 1).Value = datarow("Points")

        Next

        'Format columns
        Dim range As Excel.Range
        'Time
        range = oSheet.Range("G" + Convert.ToString(WorksheetRows.ResultsData), "G" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "hh:mm:ss.00"
        'NMiles
        range = oSheet.Range("H" + Convert.ToString(WorksheetRows.ResultsData), "H" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "0.00"
        'Miles
        range = oSheet.Range("I" + Convert.ToString(WorksheetRows.ResultsData), "I" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "0.00"
        'Km
        range = oSheet.Range("J" + Convert.ToString(WorksheetRows.ResultsData), "J" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "0.00"
        'Knots
        range = oSheet.Range("K" + Convert.ToString(WorksheetRows.ResultsData), "J" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "0.00"
        'Mph
        range = oSheet.Range("L" + Convert.ToString(WorksheetRows.ResultsData), "L" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "0.00"
        'Kmph
        range = oSheet.Range("M" + Convert.ToString(WorksheetRows.ResultsData), "M" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.NumberFormat = "0.00"

        'Format results data
        Dim resultsRange As Excel.Range = oSheet.Range("A" + Convert.ToString(WorksheetRows.ResultsData), "N" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        resultsRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
        resultsRange.Borders.Weight = Excel.XlBorderWeight.xlHairline
        resultsRange.EntireColumn.AutoFit()

        'Points
        range = oSheet.Range("N" + Convert.ToString(WorksheetRows.ResultsData), "N" + Convert.ToString(Convert.ToString(WorksheetRows.ResultsData + RaceResultDataTable.Rows.Count - 1)))
        range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
        range.Borders.Weight = Excel.XlBorderWeight.xlThin

        '********
        ' Titles
        '********

        'Add titles last so that AutoFit format is not affected

        'RaceId, RaceYear, RaceNumber, RaceName, RaceDate, RaceClass, LapNMiles, LapMiles, LapKm, RaceLaps, RaceStartDateTime, RaceEndDateTime

        'EventTitle
        oSheet.Cells(WorksheetRows.EventTitle, 1).Value = Convert.ToString(RaceDataTable.Rows(0)("EventTitle"))
        Dim racenameRange As Excel.Range = oSheet.Range("A" + Convert.ToString(WorksheetRows.EventTitle), "N" + Convert.ToString(WorksheetRows.EventTitle))
        racenameRange.Font.Name = "Arial"
        racenameRange.Font.Size = 12
        racenameRange.Font.Bold = True
        racenameRange.Merge()
        racenameRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        'RaceTitle
        oSheet.Cells(WorksheetRows.RaceTitle, 1).Value = Convert.ToString(RaceDataTable.Rows(0)("RaceTitle"))
        Dim classnameRange As Excel.Range = oSheet.Range("A" + Convert.ToString(WorksheetRows.RaceTitle), "N" + Convert.ToString(WorksheetRows.RaceTitle))
        classnameRange.Font.Name = "Arial"
        classnameRange.Font.Size = 12
        classnameRange.Font.Bold = True
        classnameRange.Merge()
        classnameRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        'Date
        oSheet.Cells(WorksheetRows.RaceDate, 1).Value = "'" + Convert.ToDateTime(RaceDataTable.Rows(0)("RaceDate")).ToString("dd MMM yyyy ddd")
        Dim racedateRange As Excel.Range = oSheet.Range("A" + Convert.ToString(WorksheetRows.RaceDate), "A" + Convert.ToString(WorksheetRows.RaceDate))
        racedateRange.Font.Name = "Arial"
        racedateRange.Font.Size = 12
        racedateRange.Font.Bold = True
        racedateRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft

        'Laps
        Dim totalLaps As Integer = NullInt(RaceDataTable.Rows(0)("StartLaps")) + NullInt(RaceDataTable.Rows(0)("RaceLaps")) + NullInt(RaceDataTable.Rows(0)("PitLaps"))
        oSheet.Cells(WorksheetRows.RaceDate, 14).Value = Convert.ToString(totalLaps) + " Laps"
        Dim racelapsRange As Excel.Range = oSheet.Range("N" + Convert.ToString(WorksheetRows.RaceDate), "N" + Convert.ToString(WorksheetRows.RaceDate))
        racelapsRange.Font.Name = "Arial"
        racelapsRange.Font.Size = 12
        racelapsRange.Font.Bold = True
        racelapsRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

        '**********
        ' Clean Up
        '**********

        'Make Excel visible and give the user control of Excel's lifetime
        oXL.Visible = True
        oXL.UserControl = True

        'Save
        Dim dateText As String = Convert.ToDateTime(RaceDataTable.Rows(0)("RaceDate")).ToString("yyyy-MM-dd")
        Dim dayText As String = Left(Convert.ToDateTime(RaceDataTable.Rows(0)("RaceDate")).DayOfWeek.ToString(), 3)
        Dim eventText As String = RaceDataTable.Rows(0)("EventTitle").ToString()
        Dim raceText As String = RaceDataTable.Rows(0)("RaceTitle").ToString()
        Dim filename = String.Format("{0} {1} {2} {3} {4}", dateText, dayText, eventText, raceText, _ReportName)
        Try
            oWB.SaveAs(filename:=filename)
        Catch ex As Exception
            MessageBox.Show("Failed to save report", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
        End Try

        'Release object references
        oRng = Nothing
        oSheet = Nothing
        oWB = Nothing
        'oXL.Quit()
        oXL = Nothing
        GC.Collect()

    End Sub

End Class
share|improve this answer
2  
how does this answer the question?! –  me how Jul 9 '13 at 14:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.