MS Excel - existing connection strings correspond to which datatable

Question

I have an excel spreadsheet that has 8 connections. I would like to know what connection string corresponds to which datatable/pivot table in excel. A solution in 2007 or 2010 method is fine.

Is this possible?

Jon Crowell · Accepted Answer · 2012-09-10 13:26:59Z

3

It is possible using the Data tab.

Try this:

-- Click on Connections in the Data tab to bring up the Workbook Connections dialog.

Excel Data --> Connections

-- Click on a connection in the list.

List of Workbook Connections

-- Click the link: click here to see where the selected connections are used

Show where data connections are used in Workbook

answered Sep 10, 2012 at 13:26

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user999690 Over a year ago

That was it. Odd how Microsoft made that link in black text. Didn't really stand out. Thanks!

Ben · Accepted Answer · 2012-09-10 14:12:00Z

This is possible from VBA too. This query just enumerates them.

You can readily modify this example to refresh the queries using PivotCache.Refresh or QueryTable.Refresh.

Sub ShowAllQueryConnectionStrings()
    Dim oSheet As Excel.Worksheet
    For Each oSheet In Application.ActiveWorkbook.Sheets
        Dim oTable As Excel.QueryTable
        For Each oTable In oSheet.QueryTables
            Debug.Print "QueryTable " & oTable.Name & ": " & oTable.Connection
        Next
        Dim oPivot As Excel.PivotTable
        For Each oPivot In oSheet.PivotTables
            Debug.Print "PivotTable " & oPivot.Name & ": " & oPivot.PivotCache.Connection
        Next
    Next
End Sub

Collectives™ on Stack Overflow

MS Excel - existing connection strings correspond to which datatable

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related