查找使用连接的位置Excel VBA [英] Find locations where connections are used Excel VBA

问题描述

我有一堆Excel 2013工作簿，我必须对其进行完善，每个工作簿都有多个工作表和多个数据连接，我正在寻找一种列出的快速方法：

I have a raft of Excel 2013 workbooks that I have to refine, each with multiple sheets and multiple data connections and I am looking for a quick way to list:

• 连接名称


• 连接字符串


• 使用连接的位置（工作表名称或范围会很有用）

我可以在连接对话框中看到所有这些信息，但是无法以编程方式跟踪它们。我想一次创建一个文件，所以不必担心在所有文件上运行代码，而只是在我开始处理相关文件时可以将其放入模块中。到目前为止，我已经在此站点上找到了此

I can see all this information in the connections dialogs but am having trouble tracking them down programmatically. I want to do this one file at a time so am not worried about running code across all the files, just something I can drop in a module as I start work on the file concerned. So far I have found this on this site:

Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
Debug.Print conn.Name
Next conn

但我找不到与此相关的位置信息。

but I can't find the location information to go alongside this. Any pointers would be very gratefully received.

欢呼声

凯尔

推荐答案

预期输出：

(Connection's Name): Sheet'sName|1st Cell of Range
Connection's ODBC Command Text
(Connection's Name): Sheet'sName|1st Cell of 1st Range // Sheet'sName|1st Cell of 2nd Range

在这里您要去：

Private Sub List_Connections_Ranges()

Dim wC As WorkbookConnection
Dim rG As Range
Dim TpStr As String

For Each wC In ActiveWorkbook.Connections
    TpStr = "(" & wC.Name & ") found on : "
    For Each rG In wC.Ranges
        TpStr = TpStr & rG.Parent.Name & "|" & rG.Cells(1, 1).Address(0, 0) & " // "
    Next rG
    Debug.Print Left(TpStr, Len(TpStr) - 4)
    Debug.Print wC.ODBCConnection.CommandText
Next wC

End Sub

这篇关于查找使用连接的位置Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！