{"id":64033,"date":"2007-09-13T01:59:00","date_gmt":"2007-09-13T01:59:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/09\/13\/how-can-i-update-and-then-break-all-the-links-in-an-excel-spreadsheet\/"},"modified":"2007-09-13T01:59:00","modified_gmt":"2007-09-13T01:59:00","slug":"how-can-i-update-and-then-break-all-the-links-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-update-and-then-break-all-the-links-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Update and Then Break All the Links in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I open an Excel spreadsheet, update all the links to other Excel spreadsheets, and then break those links?

— YH<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, YH. Before we begin, we\u2019d like to make an announcement: the Scripting Guy who writes this column gives up.<\/P>\n

To tell you the truth, that\u2019s not something we ever expected to say; after all, the Scripting Guy who writes this column never<\/I> 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\u2019s 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\u2019s spent all his spare time since that race plotting how he could win the rematch.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. In case you\u2019re interested, the only plan he\u2019s been able to come up so far with involves buying and ingesting a bottle of super leg vitamins<\/B><\/A> from the Acme Corporation. Granted, that\u2019s not much of a plan. Nevertheless, it that worked out pretty good for Wile E. Coyote.<\/P>\n

Or at least we think<\/I> it did. We never actually saw how that particular cartoon ended.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

But 100-yard dashes are easy, especially if you\u2019re willing to cheat by taking super leg vitamins. Unfortunately, however, there\u2019s one force even the Scripting Guy who writes this column can\u2019t combat: the grocery store express lane.<\/P>\n

We\u2019re 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<\/I> number of items or less. <\/P>\n\n<\/THEAD>\n\n\n
\n

Totally unrelated note.<\/B> Saying the phrase \u201crest of the world\u201d caused the Scripting Guy who writes this column to remember a meeting he recently attended, a meeting where it was noted that \u201c42% of users came from the US and 39% came from other countries.\u201d If you add 42% and 39% you\u2019ll see why we found this statistic so intriguing.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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\u2019t 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\u2019s 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<\/B><\/A> in an attempt to sneak through the line, other times just brazenly taking 13 items through the 10-item or less line and daring<\/I> 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\u2019t work; instead, people simply tried to sneak 18 items through the 15-item line. So what did the store do in response to that<\/I>? You got it: they recently raised the express lane limit to 20<\/I> items.<\/P>\n

Needless to say, there\u2019s no point in ranting and raving about express lane violations any more; we give up. (Besides, it\u2019s 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\u2019t say a single word about it.<\/P>\n

What\u2019s that? Good point: we haven\u2019t said a single word about a script that can update and then break all the links in an Excel spreadsheet either. But that\u2019s OK; after all, one script is worth a thousand words, right? Right:<\/P>

Const xlLinkTypeExcelLinks = 1\nConst xlExcelLinks = 1<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”, 3)<\/p>\n
arrLinks = objWorkbook.LinkSources(xlLinkTypeExcelLinks)<\/p>\n
For i = 1 to Ubound(arrLinks)\n    objWorkbook.BreakLink arrLinks(i), xlLinkTypeExcelLinks\nNext\n<\/PRE>\n
Let\u2019s 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\u2019ll use this to tell the script that we want to work with \u201cExcel type links,\u201d links to other cells, worksheets, or workbooks. Are there other constants we could use here? Well, there\u2019s one: xlLinkTypeOLELinks, which has a value of 2. We\u2019d 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\u2019s not<\/I> what we want to do, so forget we even mentioned it.<\/P>\n
We then define a second constant (xlExcelLinks) and set its<\/I> value to 1; we\u2019ll 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<\/B><\/A>.<\/P>\n
After defining the two constants we create an instance of the Excel.Application<\/B> object and then set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”, 3)\n<\/PRE>\n
As you can see, what we\u2019re doing here is calling the Open<\/B> method in order to open the workbook C:\\Scripts\\Test.xls. That\u2019s fine; you\u2019ve seen that a million times by now. But what\u2019s that second parameter, the 3<\/B>, doing tacked onto the end of the method call?<\/P>\n
We\u2019re glad you asked that question. The optional second parameter for the Open method determines what happens to all the links in the spreadsheet you\u2019re 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<\/I> updating any of the links. Or, because this is<\/I> 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.<\/P>\n
The long and short of it? Make 3<\/B> the second parameter to the Open method and you\u2019ll get all your links updated.<\/P>\n
And yes, that will be the case regardless of whether you have less than 20 links or more than 20 links.<\/P>\n
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\u2019t worry; breaking things is what the Scripting Guys do best.<\/P>\n\n<\/THEAD>\n\n\n
\n
True story<\/B>: 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.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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: <\/P>
arrLinks = objWorkbook.LinkSources(xlLinkTypeExcelLinks) \n<\/PRE>\n
After that we\u2019re 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 \u2013 Ubound<\/B> \u2013 of the array arrLinks):<\/P>
For i = 1 to Ubound(arrLinks)\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. 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\u2019s why our For Next loop starts at 1 rather than 0.<\/P>\n
Just in case you were wondering.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
So what are we going to do inside this For Next loop? We\u2019re going to do this:<\/P>
objWorkbook.BreakLink arrLinks(i), xlLinkTypeExcelLinks\n<\/PRE>\n
As you can see, there\u2019s nothing very fancy going on here; we\u2019re simply calling the BreakLink<\/B> method, passing that method two parameters:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
arrLinks(i)<\/B>, which is simply a reference to an individual link in the collection. The first time through the loop we\u2019ll 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.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
xlLinkTypeExcelLinks<\/B>, the constant that identifies the type of link we want to break.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
That\u2019s pretty much all we have to do; by the time we exit the loop all the links in the workbook will have been broken.<\/P>\n
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 \u2013 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\u2019re beat.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I open an Excel spreadsheet, update all the links to other Excel spreadsheets, and then break those links?— YH Hey, YH. Before we begin, we\u2019d like to make an announcement: the Scripting Guy who writes this column gives up. To tell you the truth, that\u2019s not something we ever expected […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-64033","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I open an Excel spreadsheet, update all the links to other Excel spreadsheets, and then break those links?— YH Hey, YH. Before we begin, we\u2019d like to make an announcement: the Scripting Guy who writes this column gives up. To tell you the truth, that\u2019s not something we ever expected […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64033"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64033\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}