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