Excel VBA LinkInfo Method

This Excel VBA tutorial explains VBA LinkInfo Method to check status of linked workbook.

You may also want to read:

Excel find all external links and broken links in workbook

Excel VBA LinkInfo Method

Workbook LinkInfo Method is usually used to check status of linked workbook, so that we know whether the workbook link is broken or normal.

Usually this Method is used with LinkSources Method.

Syntax of LinkInfo

Workbook.LinkInfo(Name, LinkInfo, Type, EditionRef)
Name Required/Optional Data Type Description
Name Required String The name of the link.
LinkInfo Required XlLinkInfo The type of information to be returned.

Name Value Description
xlEditionDate 2 Applies only to editions in the Macintosh operating system.
xlLinkInfoStatus 3 Returns the link status.
xlUpdateState 1 Specifies whether the link updates automatically or manually.
Type Optional Variant One of the constants of XlLinkInfoType specifying the type of link to return.

Name Value Description
xlLinkInfoOLELinks 2 OLE or DDE server
xlLinkInfoPublishers 5 Publisher
xlLinkInfoSubscribers 6 Subscriber
EditionRef Optional Variant If the link is an edition, this argument specifies the edition reference as a string in R1C1 style. This argument is required if there’s more than one publisher or subscriber with the same name in the workbook.

If you choose to return xlLinkInfoStatus, a value from 0 to 10 will return (or you can use XlLinkInfoStatus to represent the number)

Value XlLinkInfoStatus Description
0 xlLinkStatusOK No errors.
1 xlLinkStatusMissingFile File missing.
2 xlLinkStatusMissingSheet Sheet missing.
3 xlLinkStatusOld Status may be out of date.
4 xlLinkStatusSourceNotCalculated Not yet calculated.
5 xlLinkStatusIndeterminate Unable to determine status.
6 xlLinkStatusNotStarted Not started.
7 xlLinkStatusInvalidName Invalid name.
8 xlLinkStatusSourceNotOpen Not open.
9 xlLinkStatusSourceOpen Source document is open.
10 xlLinkStatusCopiedValues Copied values.

Example of LinkInfo

Assume that the active workbook contains vlookup formula to lookup another workbook called “workbook2” and it is currently open.

Run the below code

Public Sub test()
    MsgBox (ActiveWorkbook.LinkInfo("workbook2.xls", xlLinkInfoStatus))
End Sub

A message box will pop up and return number “9” (xlLinkStatusSourceOpen)

Convert xlLinkInfoStatus to Description

The returned xlLinkInfoStatus is a number, it is better to convert it to a text description.

Public Function linkStatusDescr(statusCode)
           Select Case statusCode
                Case xlLinkStatusCopiedValues
                    linkStatusDescr = "Copied values"
                Case xlLinkStatusIndeterminate
                    linkStatusDescr = "Unable to determine status"
                Case xlLinkStatusInvalidName
                    linkStatusDescr = "Invalid name"
                Case xlLinkStatusMissingFile
                    linkStatusDescr = "File missing"
                Case xlLinkStatusMissingSheet
                    linkStatusDescr = "Sheet missing"
                Case xlLinkStatusNotStarted
                    linkStatusDescr = "Not started"
                Case xlLinkStatusOK
                    linkStatusDescr = "No errors"
                Case xlLinkStatusOld
                    linkStatusDescr = "Status may be out of date"
                Case xlLinkStatusSourceNotCalculated
                    linkStatusDescr = "Source not calculated yet"
                Case xlLinkStatusSourceNotOpen
                    linkStatusDescr = "Source not open"
                Case xlLinkStatusSourceOpen
                    linkStatusDescr = "Source open"
                Case Else
                    linkStatusDescr = "Unknown status"
            End Select
End Function

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff192971.aspx

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *