Hello Excellers and welcome back for another blog post in my #macromonday series. Today’s small but very useful Excel macro will list any links that you have in your Excel worksheet. I recently wrote this code to use in the past few weeks as due to some changes at work. Multiple old links in Excel worksheets were now displaying errors when trying to update links. Multiple workbooks contained multiple links. This sounded like a job for some VBA code.
Want To Use A Manual Method Of Displaying Links?.
Before we get started on an automated solution to this problem, I want to point out that you can of course view all of your links in your workbook using the worksheet menus. Just follow the steps below.
- Select Data Tab
- Connections Group
- Edit Links
Excel will display a dialog box and list your external data sources.
Writing A Macro To Display Excel Workbook Links
I find that if there are a large number of links in a workbook, the manual method is hard to read and work with. By generating a list on a new worksheet with a small bit of VBA code allows me to analyse the links much easier and trace the source. This is especially important if the links are generating an error when the workbook is opened. If your links are broken are have issues then Excel will display the dialog box below.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
As you can see this macro will be useful to reuse in any workbook with links that require analysis. Therefore I will create and save this macro for future use in my Personal Macro Workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply Sort_Worksheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]
Sub WorkbookLinks()
End Sub
[/stextbox]
Declaring Variables
The next step in the code is to declare a variable. This ensures that Excel creates a memory container for each of them. We have declared that AllLinks will be the data type of Variant. I have used this as I am not specifically determining the type of data the lin will be. While this type of variable takes up more memory, it seems most applicable to my situation when extracting all of the names of links in the Excel workbook.
[stextbox id=’info’]
Dim AllLinks As Variant
[/stextbox]
Using The Workbook LinkSources Method
We use the Workbook LinkSources method which returns an array of names of linked documents. This sounds exactly what we need in this case. This method has the following sytnax.
Workbook.LinkSources(Type) where Type is one of the following. In this case we are using the xLLinks type.
| Type | Optional | One of the constants of XlLink which specifies the type of link to return.Return all types if Type is omitted.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
[stextbox id=’info’]
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
[/stextbox]
Adding A NewExcel Worksheet If There Are Links In The Workbook
We now use the If statement along with Not ISEmpty statement to add a new worksheet if the aLinks if not empty. The Microsoft Excel ISEMPTY function can be used to check for blank cells or uninitialized variables.
[stextbox id=’info’]
If Not IsEmpty(aLinks)
Then Sheets.Add
[/stextbox]
Using The For Loop To Extract All Links
We use the For loop statement. The code between the For and Next will be executed until all links (aLinks) have been listed. The Looping then stops.
[stextbox id=’info’]
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
[/stextbox]
Ending The Excel Macro
The routine then ends with the End Sub price of code. This was already entered into the module for us when started the type the name of the macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Copy All Of The Code Together
[stextbox id=’info’]
Sub WorkbookLinks()
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
End Sub
[/stextbox]
Now all you need to do is test your Macro. Of course, this is always my favourite part of the process. Have you used any other methods to get a list of all links in your Excel workbooks?. Please share in the comments below.
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
