How Can I Update and Then Break All the Links in an Excel Spreadsheet?
Hey, Scripting Guy! How can I open an Excel spreadsheet, update all the links to other Excel spreadsheets, and then break those links?
— YH
![Spacer](https://devblogs.microsoft.com/scripting/wp-content/uploads/sites/29/2019/05/spacer.gif)
![Hey, Scripting Guy! Answer Hey, Scripting Guy! Answer](https://devblogs.microsoft.com/wp-content/uploads/sites/29/2019/02/a-for-powertip.jpg)
![Script Center Script Center](http://img.microsoft.com/library/media/1033/technet/images/scriptcenter/ad.jpg)
Hey, YH. Before we begin, we’d like to make an announcement: the Scripting Guy who writes this column gives up.
To tell you the truth, that’s not something we ever expected to say; after all, the Scripting Guy who writes this column never gives up. For example, a week or so ago this same Scripting Guy squared off against the Scripting Son in a 100-yard dash. The Scripting Son — who’s bigger, faster, and (slightly) younger than his father — won by a good 5 or 6 yards. But did the Scripting Dad give up? No sir; instead, he’s spent all his spare time since that race plotting how he could win the rematch.
Note. In case you’re interested, the only plan he’s been able to come up so far with involves buying and ingesting a bottle of super leg vitamins from the Acme Corporation. Granted, that’s not much of a plan. Nevertheless, it that worked out pretty good for Wile E. Coyote. Or at least we think it did. We never actually saw how that particular cartoon ended. |
But 100-yard dashes are easy, especially if you’re willing to cheat by taking super leg vitamins. Unfortunately, however, there’s one force even the Scripting Guy who writes this column can’t combat: the grocery store express lane.
We’re not sure how ubiquitous grocery store express lanes are in the rest of the world, but in the US larger grocery stores typically have one more express lanes, checkout stands for people who have only x number of items or less.
Totally unrelated note. Saying the phrase “rest of the world” caused the Scripting Guy who writes this column to remember a meeting he recently attended, a meeting where it was noted that “42% of users came from the US and 39% came from other countries.” If you add 42% and 39% you’ll see why we found this statistic so intriguing. |
Anyway, the idea behind the express lane is a good one: if all you need to do is pick up a loaf of bread or a carton of milk you don’t need to stand in line behind a band of survivalists buying enough groceries to last through the current millennium. Instead, you pop into the express lane, pay for your loaf of bread, and then get on with your life.That’s the idea, anyway. In practice, however, otherwise decent and law-abiding citizens have made a mockery of the express lane, sometimes pulling off elaborate schemes in an attempt to sneak through the line, other times just brazenly taking 13 items through the 10-item or less line and daring the clerk to stop them. And how have the grocery stores responded to this? Well, a few years ago the grocery store where the Scripting Guy who writes this column shops responded by raising the 10 item to 15 items. Unfortunately, that didn’t work; instead, people simply tried to sneak 18 items through the 15-item line. So what did the store do in response to that? You got it: they recently raised the express lane limit to 20 items.
Needless to say, there’s no point in ranting and raving about express lane violations any more; we give up. (Besides, it’s getting to the point where it will soon be physically impossible to cart around enough items to push you over the limit anyway.). From now on, feel free to take as many items through the express lane as you wish; we won’t say a single word about it.
What’s that? Good point: we haven’t said a single word about a script that can update and then break all the links in an Excel spreadsheet either. But that’s OK; after all, one script is worth a thousand words, right? Right:
Const xlLinkTypeExcelLinks = 1 Const xlExcelLinks = 1Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”, 3)
arrLinks = objWorkbook.LinkSources(xlLinkTypeExcelLinks)
For i = 1 to Ubound(arrLinks) objWorkbook.BreakLink arrLinks(i), xlLinkTypeExcelLinks Next
Let’s take a minute or two to talk about the script and to how it works. We start off by defining a constant named xlLinkTypeExcelLinks and setting the value to 1; we’ll use this to tell the script that we want to work with “Excel type links,” links to other cells, worksheets, or workbooks. Are there other constants we could use here? Well, there’s one: xlLinkTypeOLELinks, which has a value of 2. We’d use this constant if we wanted to work with OLE objects (for example, a Word document or PowerPoint presentation embedded within our spreadsheet. But that’s not what we want to do, so forget we even mentioned it.
We then define a second constant (xlExcelLinks) and set its value to 1; we’ll use this constant when we retrieve a collection of all the Excel type links found in the workbook. And yes, we could have simply used the same constant (xlLinkTypeExcelLinks) in both places; after all, they both have same value (1) and they both refer to the same kind of link. Nevertheless we chose to use two different constants simply because that keeps our script in synch with the official Excel documentation.
After defining the two constants we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Execl that we can see on screen. That also brings us to this line of code:
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”, 3)
As you can see, what we’re doing here is calling the Open method in order to open the workbook C:\Scripts\Test.xls. That’s fine; you’ve seen that a million times by now. But what’s that second parameter, the 3, doing tacked onto the end of the method call?
We’re glad you asked that question. The optional second parameter for the Open method determines what happens to all the links in the spreadsheet you’re about to open. We tacked on the value 3 because, upon opening, that causes all the links in the workbook to be updated. Alternatively, we could have set the second parameter to 2: that would open the file without updating any of the links. Or, because this is an optional parameter, we could leave it out altogether; in that case, each time we open the file Excel will ask us whether or not we want to update the links.
The long and short of it? Make 3 the second parameter to the Open method and you’ll get all your links updated.
And yes, that will be the case regardless of whether you have less than 20 links or more than 20 links.
Of course, updating the links is only half the battle: once the links have been updated we then need to break each of those links. Sound hard? Don’t worry; breaking things is what the Scripting Guys do best.
True story: Scripting Guy Jean Ross once ripped a door off its hinges just by turning the doorknob and pulling the door open. Ever since then whatever Scripting Guy Jean Ross wants Scripting Guy Jean Ross gets. |
As near as we can tell, links within a workbook must be broken one at a time. With that in mind, we use the following line of code to retrieve a collection of all the Excel type links found in the file, stashing that collection in an array named arrLinks:
arrLinks = objWorkbook.LinkSources(xlLinkTypeExcelLinks)
After that we’re ready to start breaking links. To break those links one-by-one we set up a For Next loop that runs from 1 to the total number of links in the collection (that is, until we reach the upper bound – Ubound – of the array arrLinks):
For i = 1 to Ubound(arrLinks)
Note. As you know, most arrays start at 0; the index number of the first item in an array is almost always 0. For some reason, however, Microsoft Office arrays usually start with 1; the first item in the array has an index number of 1 rather than an index number of 0. That’s why our For Next loop starts at 1 rather than 0. Just in case you were wondering. |
So what are we going to do inside this For Next loop? We’re going to do this:
objWorkbook.BreakLink arrLinks(i), xlLinkTypeExcelLinks
As you can see, there’s nothing very fancy going on here; we’re simply calling the BreakLink method, passing that method two parameters:
• |
arrLinks(i), which is simply a reference to an individual link in the collection. The first time through the loop we’ll be working with the first link in the collection. How do we know that? Because the counter variable i starts off being equal to 1. |
• |
xlLinkTypeExcelLinks, the constant that identifies the type of link we want to break. |
That’s pretty much all we have to do; by the time we exit the loop all the links in the workbook will have been broken.
That should do it, YH. If you have any more questions about this please let us know. Oh, if you have 20 or fewer questions, be sure and send them to the Scripting Guys Express Lane. That way you can – oh, never mind. If you have 23 or 29 or 578 questions go ahead and send them all to the Express Lane. We know when we’re beat.
0 comments