VB and VBA Users Source Code: Listing all the External Links in an Excel Workbook
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Listing all the External Links in an Excel Workbook
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, September 06, 2005
Hits:
4069
Category:
VBA (Visual Basic for Applications)
Article:
The code below returns a collection of all the external references within a workbook. This can be handy if you are getting external link errors when opening your workbook: 'Purpose : Returns a collection of references to all the cells that contain external references, ' or a distinct list of all externally referenced workbooks. 'Inputs : [workbook] The workbook to list the external references in. ' [bReturnRefNamesOnly] If true will return a distinct list of workbook names, ' else will return any cells that contain external references. 'Outputs : Returns a collection containing either references to all the external cells, or ' a distinct list of all externally referenced workbooks. ' which have external references. 'Author : Andrew Baker 'Date : 25/03/2005 'Notes : Function GetExternalLinks(Optional Workbook As Excel.Workbook, Optional bReturnRefNamesOnly As Boolean = False) As Collection Dim oSht As Worksheet Dim oFormulaCells As Range, oCell As Range Dim sRefName As String Dim res As Collection 'Add a new sheet to list all external links. On Error GoTo ErrFailed If Workbook Is Nothing Then Set Workbook = ThisWorkbook End If Set res = New Collection 'Loop through each worksheet For Each oSht In ThisWorkbook.Worksheets 'Set "oFormulaCells" to range that has formulas On Error Resume Next Set oFormulaCells = Nothing Set oFormulaCells = oSht.Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo ErrFailed 'See if we found any formula cells If oFormulaCells Is Nothing = False Then 'Loop through each oCell in "oFormulaCells" For Each oCell In oFormulaCells 'Check if is an external link If InStr(1, oCell.Formula, "[") > 0 And InStr(1, oCell.Formula, "]") > 0 Then 'Is external If bReturnRefNamesOnly Then 'Store distinct list of workbook names sRefName = Mid$(oCell.Formula, 1, InStr(1, oCell.Formula, "]")) On Error Resume Next res.Add sRefName, sRefName On Error GoTo ErrFailed Else 'Store reference to cell res.Add oCell End If End If Next End If Next Set oFormulaCells = Nothing Set GetExternalLinks = res Exit Function ErrFailed: Debug.Print "Error in GetExternalLinks: " & Err.Description Debug.Assert False Resume Next End Function 'Demonstration routine Sub Test() Dim oExternalLinks As Collection, oExternalCell As Excel.Range Dim oItem As Variant 'Get external links Set oExternalLinks = GetExternalLinks() Debug.Print "List of Cells Containing External References" For Each oItem In oExternalLinks Set oExternalCell = oItem Debug.Print "External Reference found in Cell " + oExternalCell.Address + " (formula " + oExternalCell.Formula & ")" Next 'Get distinct list of the externally reference workbook names Set oExternalLinks = GetExternalLinks(, True) Debug.Print "Distinct List of External References" For Each oItem In oExternalLinks Debug.Print "External Reference: " + oItem Next End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
