Hello, and welcome back its that time again. Time for some more Excel VBA Macro coding fun and today I want to show you how to quickly break links that are in your Excel workbook. Links that are no longer needed or refer to now redundant links. I mainly come across links to a users desktop or Temp file that no longer now exists. These really can be a drag.
Links in workbooks can be tricky. They can not only be part of Formulas, but they can also be part of data validations and named ranges. Great when you need them. Pain when you do not. It is really tiring when the Links Error appears every time you open that Excel workbook.
To break them is also time consuming and repetitive. That sounds just a job for an Excel Macro, doesn’t it?.
There are a few ways to find all of the links in your Excel workbook. You can use the Excel Macro in this #MacroMonday blog post below. Follow the steps, copy the code and you will soon have a list of all of the links displayed on a new worksheet.
Macro Monday – List All Links In An Excel Workbook With Simple VBA Code
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?
Preparing To Write 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 BreakLinks. 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 BreakLinks() End Sub [/stextbox]
Declaring Variables.
We need to declare a number of variables. This ensures that Excel creates a memory container for these values and allocates memory for them. In this example, we need to declare the following. This ensures the user selected range is stored in the Excel memory.
[stextbox id=’info’] Dim ExternalLinks As Variant, wb As Workbook, x As Long [/stextbox]
We declare the External Links as a variant. A Variant is a special data type that can contain any kind of data except fixed-length This covers any type of name that the links have. By declaring the x as Long Excel can expect an integer number.
Set Excel To Use The Current Workbook
By setting the wb = ActiveWorkbook we instruct Excel Explicitly to break the links from the Active Excel Workbook only. This prevents any other open workbook’s links being accidentally broken. Very Important.
[stextbox id=’info’] Set wb = ActiveWorkbook [/stextbox]
Create An Array Of Workbook Links.
This piece of code creates an array of all of the links in the Active Workbook. We use the Workbook LinksSources Method in Excel VBA to find all external links.
[stextbox id=’info’] ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks) [/stextbox]
Delete The Excel Workbook Links.
Excel now will use the Workbook.BreakLink method which Converts formulas linked to other Microsoft Excel sources or OLE sources to values. Excel does this using the For…Next looping method to loop through all of the links and convert them to values thereby breaking the link.
[stextbox id=’info’] For x = 1 To UBound(ExternalLinks) wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks Next x [/stextbox]
Ending The Excel Macro.
The routine then ends with the End Sub 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]
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.
