Untangle Links Programmatically Part I

As a former financial analyst (before I learned more efficient ways to analyze data), I used to build elaborate virtual ecosystems of linked Excel models. Inevitably, I'd need to make some sort of change to a particular workbook, and the change would "break" dependencies I had created in other workbooks that linked to the workbook I was making the change in. Maintaining this structure was a living nightmare. One of the tools that would've been helpful in those days was one that would automatically examine the dependencies in workbooks.

Believe it or not, it's not very difficult to build such a tool. As you continue through the book, one of the utilities you'll examine will be a Link Rebuilder—a utility you can use to examine workbook links.

One of the reasons that this utility isn't very difficult is that there are some handy methods and properties associated with the Workbook object that return useful information about links. These methods and properties are listed in Table 6.2.

One critical piece of the Link Rebuilder utility is functionality that can, given a workbook, determine all of the links in the workbook (if any). From Table 6.2, you can see that the Link-Sources method performs this feat. Listing 6.3 demonstrates a procedure that prints all of the link information.

Table 6.2: Link-Oriented Members of the Workbook Object


SaveLinkValues property UpdateLinks property

BreakLink method ChangeLink method LinkInfo method LinkSources method

OpenLinks method UpdateLink method


Read/write Boolean that specifies whether Excel saves external link values with the workbook.

Read/write property that indicates a workbook's setting for updating embedded OLE links. You can check or set the value returned using the XlUpdateLink constants xlUpdateLinkAlways, xlUpdateLinksNever, and xlUpdateLinksUserSetting. OLE stands for Object Linking and Embedding, an older Microsoft moniker for the technology that enables linking.

Converts formulas linked to other sources to values. Changes a link from one document to another. Returns the link date and update status.

Returns an array of links in the workbook including linked documents, editions, or DDE or OLE servers (DDE and OLE are explained in Chapter 14). This method returns Empty if it doesn't find any links.

Opens the document to which the link refers.

Updates an Excel, DDE, or OLE link.

0 0

Post a comment