如何用VBA判断Excel连接是否关联表格及使用位置？

Excel的连接与查询时常出现断开链接或刷新失败的情况，目前缺少有效方法区分仅连接和关联了表格的连接。

我尝试了以下三种方法：

1. 使用Source属性 #

Dim sourceConnection  As Object: Set sourceConnection = ActiveWorkbook.Connections(1)
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Sheets(1).Activate

' Add code to determine the worksheet that is using the connection

If sourceConnection.OLEDBConnection.Source = "" Then
    MsgBox "The sourceConnection is connected with only a connection."
Else
    MsgBox "The sourceConnection is linked to a table somewhere on the workbook."
End If

2. 使用Tablename属性 #

Dim sourceConnection  As Object: Set sourceConnection = ActiveWorkbook.Connections(1)
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Sheets(1).Activate

' Add code to determine the worksheet that is using the connection

If sourceConnection.OLEDBConnection.Tablename = "" Then
    MsgBox "The sourceConnection is connected with only a connection."
Else
    MsgBox "The sourceConnection is linked to a table somewhere on the workbook."
End If

3. 使用CommandText属性 #

Dim sourceConnection  As Object: Set sourceConnection = ActiveWorkbook.Connections(1)
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Sheets(1).Activate

' Add code to determine the worksheet that is using the connection

If sourceConnection.CommandText = "" Then
    MsgBox "The sourceConnection is connected with only a connection."
Else
    MsgBox "The sourceConnection is linked to a table somewhere on the workbook."
End If

上述三种方法中，仅CommandText属性的代码可正常编译运行，但该属性仅能查看连接执行的命令，无法获取其关联的表格信息，无法满足需求。

内容的提问来源于stack exchange，提问作者Hunter