Hello, Excellers, welcome back to another #macromonday blog in this series. Glad to have you back. Today let’s look at removing all data connections from an Excel workbook. Why would you want to do that?. Because I know I have inherited workbooks with old data connections that are no longer used. Similarly, these data connections just hang around like ghostly links in the background. They cause irritation and warnings by Excel that they cannot be refreshed. Well, if you want to easily get rid of all of them here is a quick bit of Excel Macro or VBA code to do the job for you. We will use the Do While Loop to do this.
How Does The Macro Work?
The macro uses the Do While Loop. This looping method executes a line of code continually while a specified condition is being met or is TRUE.
Starting The Macro.
First of all, we start this Macro by opening the Excel Visual Basic Editor. You can either do this by hitting ALT+F11 or by selecting the Developer Tab | Visual Basic. This allows us to Insert a new Module to store our VBA code. We have two choices as to where to store the code.
- If we want to only use this code in this particular workbook then select to insert a Module within this workbook.
- While, in contrast, if you want to be able to reuse the code then insert the new Module into the Personal Macro Workbook. The Excel VBA code stored in your Personal Macro workbook will be made available in any active Excel session that you open. As a result, it is really easy to keep all off your Excel VBA Macro’s together in one place.
For more information on your Personal Macro Workbook, Read My Blog Posts Below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Macro Monday – Why Is My Personal Macro Workbook Not Loading Automatically?
Name Your Macro.
Type Sub then the name of your Macro. Excel automatically inserts the End Sub code which will end the Subroutine or the Macro
[stextbox id=’info’]
Sub DeleteWorkbookConnections
Start The Do While Loop.
This is where we begin the Do While loop statement where we look to test if the statement is TRUE. We are testing if the number of workbook connections is greater than one.
[stextbox id=’info’]
Do While ActiveWorkbook.Connections.Count > 0
[/stextbox]
Delete The Active Connections.
Excel deletes any connections in the active workbook.
[stextbox id=’info’]
Do While ActiveWorkbook.Connections.Count > 0
Loop
[/stextbox]
End The Routine
The Sub Routine ends once the looping has deleted any data connections
[stextbox id=’info’]
End Sub
[/stextbox]
The last part of this process is my favourite part. I get to test my Excel Macro.
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
How To Excel At Excel – Macro Mondays Blog Posts.
Do You Need Help With An Excel or Excel Macro 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.
