Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trap "Can't find project or library" error messages
I was looking for answer to the subject question, but didn't find
exactly what I was looking for. I have included a solution to the problem: How to trap the "Can't find project or library" error when an addin referenced by a workbook does not exist on the users computer. If there is a MISSING: reference, Excel throws an error that can not be trapped in code. Do not reference the addin via Tools/References in the VBE. Rather set the reference via when workbook opens. Remove the reference to the addin when workbook closes so if it is passed to someone else without the addin it will not cause an error. I am using Excel 2003/ WinXP. 'Workbook Project Assumptions: 'Have reference for: 'Microsoft Scripting Runtime 'Microsoft Visual Basic for Applications Extensibility 5.x 'Addin Assumptions: 'The name of the Addin is "My Referenced Addin.xla" 'The Addin is stored in the "UserLibraryPath" folder 'The VBProject Name for the Addin is "My_Ref_Addin" 'There is a Sub in Addin called "SomeSubroutineInAddin" 'The following code is in the ThisWorkbook module of workbook referencing Addin Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) RemoveReference End Sub Private Sub Workbook_Open() SetAddinReference End Sub '================================================= ============================================== 'The following code is in any standard module of workbook referencing Addin Option Explicit Private Const mcsADDIN_FILE As String = "My Referenced Addin.xla" Private Const mcsADDIN_VBP_NAME As String = "My_Ref_Addin" Public Sub SetAddinReference() Const csMSG As String = "The My Referenced Addin is not available," _ & vbNewLine & _ "therfore the features of this workbook are not functional." Dim sAddinPath As String sAddinPath = GetAddinFolder & mcsADDIN_FILE 'Check to see if addin is on computer If AddinExists(sAddinPath) Then 'If so, see if it is already referenced If Not ReferenceExists(mcsADDIN_VBP_NAME) Then 'If not, Add a reference so code in addin can be called using project code name AddReference sAddinPath End If Else 'Let user know that the addin is not avaliable MsgBox csMSG, vbExclamation End If End Sub Public Function AddinExists(ByVal Path As String) As Boolean 'Use File System Object to see if Addin exists Dim oFSO As Scripting.FileSystemObject Set oFSO = New Scripting.FileSystemObject AddinExists = oFSO.FileExists(Path) Set oFSO = Nothing End Function Private Sub AddReference(ByVal Path As String) 'Add reference to Addin using full file path ThisWorkbook.VBProject.References.AddFromFile (Path) End Sub Private Function GetAddinFolder() As String 'Use Application properties to get addin folder. Could be anywhere With Application GetAddinFolder = .UserLibraryPath & .PathSeparator End With End Function Private Function ReferenceExists(ByVal ProjectName As String) As Boolean ReferenceExists = Not (GetReference(ProjectName) Is Nothing) End Function Private Function GetReference(ByVal Name As String) As Object 'Need reference to VBA Extensibility for this On Error Resume Next Set GetReference = ThisWorkbook.VBProject.References.Item(Name) On Error GoTo 0 End Function Private Sub RemoveReference() 'This removes the reference prior to saving so problem of '"MISSING:" reference will not occur on other computer Dim xObject As Object Set xObject = GetReference(mcsADDIN_VBP_NAME) If Not xObject Is Nothing Then ThisWorkbook.VBProject.References.Remove xObject End If End Sub '================================================= ============================================== 'The following code is in any standard module of workbook referencing Addin Option Explicit 'WHEN THIS IS CALLED IT WILL MAKE CALL TO ADDIN Private Sub DoSomethingInAddin() 'SomeSutroutineInAddin is a Sub in the referenced Addin My_Ref_Addin.SomeSubroutineInAddin End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell error "Can't find Project or Library" Project VBAProject | Excel Worksheet Functions | |||
indirect.ext - "can't find project or library" error | Excel Worksheet Functions | |||
"Compile Error Can't find project or library." | Excel Programming | |||
Trim: Excel 97 gives error "Can't find project or library" | Excel Programming | |||
macro error "can't find project or library" | Excel Programming |