+ Reply to Thread
Results 1 to 7 of 7

VBA Code for finding all external links in an Excel workbook

  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    VBA Code for finding all external links in an Excel workbook

    This code will report on all links in the current active workbook and open a new workbook with a report of the links. It includes flags for links to files that are not found. The workbook will named as the workbook being checked with "+LINKS" appended.

    It is intended to be run from a QAT button, so that's why it uses ActiveWorkbook. It can be adapted to open any desired workbook with a File Open dialog.

    The code below is v2 updated 9/21/2015 from the original post.
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 09-21-2015 at 09:18 PM. Reason: v2
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Code for finding all external links in an Excel workbook

    I accidentally left a couple of Debug.Print statements in there. Feel free to remove.

  3. #3
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Re: VBA Code for finding all external links in an Excel workbook

    I found that it failed at the BadFileNameReport function. The line it failed on reads " For i = LBound(FileNames) To UBound(FileNames)"

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Code for finding all external links in an Excel workbook

    It succeeded in all my tests so you must have a file that creates a failure mode that I didn't test. Can you attach the file you used?

  5. #5
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Re: VBA Code for finding all external links in an Excel workbook

    I'd like your assistance with my post of finding links. You asked for a copy of the workbook but it's got a lot of parts I'd rather not share with the general population. Can I send directly to you and ask you to look at it?

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Code for finding all external links in an Excel workbook

    There was a bug in the code. The code uses the presence of "[" to determine if an external file reference is present in a formula, but did not consider that this character is also used in table column references. The code has been updated in the original post.

    There was another bug that I attribute to a VBA bug. Part of the code does a For loop to iterate through the FormatConditions collection in a worksheet. However, if the rule type is a "Graded Color Scale" rule, then an attempt to assign that rule from the collection to a variable of type FormatCondition raises a Type Mismatch error. I cannot think of any rationale for this. So I coded around it, using a Do/Loop instead of a For and testing for the xlColorScale type before trying the assignment.

  7. #7
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: VBA Code for finding all external links in an Excel workbook

    An older post but helped me in detecting external links which are not supported in Online environment.
    Teach me Excel VBA

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Links use name of external workbook entered once?
    By leaning in forum Excel General
    Replies: 1
    Last Post: 04-19-2012, 12:27 PM
  2. Log of External Links in the workbook
    By DUKE888 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2011, 10:49 AM
  3. External workbook links
    By Lizabeta in forum Excel General
    Replies: 0
    Last Post: 03-18-2011, 12:37 PM
  4. Moving a workbook with external links
    By Gooford in forum Excel General
    Replies: 3
    Last Post: 11-23-2009, 09:00 AM
  5. Finding and deleting external links!!!
    By jonn in forum Excel General
    Replies: 3
    Last Post: 06-15-2007, 05:13 AM
  6. How to clear links to external workbook
    By Wilbur in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2006, 10:15 PM
  7. Excel: Finding external "links" in a spreasheet
    By CILeader in forum Excel General
    Replies: 3
    Last Post: 10-15-2005, 02:05 PM
  8. [SOLVED] How do I remove external links from Excel 2000 workbook?
    By mlwest in forum Excel General
    Replies: 2
    Last Post: 02-04-2005, 08:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1