How to determine whether your workbook contains links to external data in Excel 2002 and Excel 2003 (330383)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
This article was previously published under Q330383

SUMMARY

Excel workbooks can contain external data ranges that retrieve data from the following data sources:
  • Microsoft SQL Server OLAP Services (OLAP provider)
  • Microsoft Access
  • dBASE
  • Microsoft FoxPro
  • Microsoft Excel
  • Oracle
  • Paradox
  • SQL Server
  • Text-file databases
  • Third-party providers
You can also use ODBC drivers or data source drivers from other manufacturers to obtain information from data sources that are not listed here, including other types of OLAP databases. You can use this external data source in a data range or query table in a worksheet or in a PivotTable.

You may want to be able to determine if a worksheet that is sent to you contains links to external data sources. This can help you to decide whether or not to trust the sources and whether or not you want the data to be updated regularly or upon opening the workbook. You can also delete the links to external data and keep the current values instead.

This article describes:
  • How to list all external data sources in a whole workbook.
  • How to select all external data ranges in a single worksheet.
  • How to determine if an external data range in a workbook updates automatically.
  • How to use the Edit Links dialog box.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

How to list all external data sources in a whole workbook

To list all external data ranges in a Microsoft Excel workbook:
  1. Start Excel, and then open the workbook that you want to search for external data sources.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  3. In the Visual Basic Editor, click Module on the Insert menu.
  4. In the module window, type the following code:
    Sub ListWebQueryPivotTableLinks()
    Dim wbA As Workbook, wsN As Worksheet, ws As Worksheet
    Dim pt As PivotTable, qt As QueryTable, R As Long, i As Long
    Dim vLnkSrc As Variant
    Const PROGCREATE As String = "This external " & _
                                "data range was created " & _
                                "programmatically and cannot be edited"
    On Error GoTo errHandler
    Set wbA = ActiveWorkbook
    Set wsN = Workbooks.Add(xlWorksheet).Worksheets(1)
    wsN.Name = wbA.Name
    wsN.Range("A1:E1").Value = Array("Name", "Location", _
            "Type", "Connection", "CommandText")
    wsN.Range("A1:E1").Font.Bold = True
    R = 1
    For Each ws In wbA.Worksheets
        For Each pt In ws.PivotTables
            R = R + 1
            With pt.PivotCache
                wsN.Cells(R, 1).Value = pt.Name
                wsN.Cells(R, 2).Value = ws.Name & "!" & _
                        pt.TableRange2.Address(False, False)
                Select Case .SourceType
                Case xlConsolidation
                    R = R - 1
                    For i = 1 To UBound(.SourceData)
                        R = R + 1
                        wsN.Cells(R, 1).Value = pt.Name
                        wsN.Cells(R, 2).Value = ws.Name & "!" & _
                                pt.TableRange2.Address(False, False)
                        wsN.Cells(R, 3).Value = _
                                "PivotTable - Consolidation Range"
                        wsN.Cells(R, 4).Value = "'" & _
                                .SourceData(i, 1)
                        wsN.Cells(R, 5).Value = "n/a"
                    Next
                Case xlDatabase
                    wsN.Cells(R, 3).Value = "PivotTable - Excel List"
                    wsN.Cells(R, 4).Value = "'" & .SourceData
                    wsN.Cells(R, 5).Value = "n/a"
                Case xlExternal
                    If .OLAP Then
                        wsN.Cells(R, 3).Value = "PivotTable - OLAP"
                        wsN.Cells(R, 4).Value = "'" & .Connection
                        wsN.Cells(R, 5).Value = .CommandText
                    ElseIf .QueryType = xlADORecordset Then
                        wsN.Cells(R, 3).Value = _
                                "PivotTable - ADO Recordset"
                        wsN.Cells(R, 4).Value = PROGCREATE
                        wsN.Cells(R, 5).Value = "'" & .Recordset.Source
                    Else
                        wsN.Cells(R, 3).Value = _
                                "PivotTable - External Data"
                        wsN.Cells(R, 4).Value = "'" & .Connection
                        wsN.Cells(R, 5).Value = .CommandText
                    End If
                Case xlScenario
                    wsN.Cells(R, 3).Value = "PivotTable - Scenario"
                    wsN.Cells(R, 4).Value = "Based upon a Scenario " & _
                            "in this workbook"
                    wsN.Cells(R, 5).Value = "n/a"
                End Select
            End With
        Next
        For Each qt In ws.QueryTables
            R = R + 1
            wsN.Cells(R, 1).Value = qt.Name
            wsN.Cells(R, 2).Value = ws.Name & "!" & _
                    qt.ResultRange.Address(False, False)
            Select Case qt.QueryType
            Case xlTextImport
                wsN.Cells(R, 3).Value = "Text Import"
                wsN.Cells(R, 4).Value = "'" & qt.Connection
                wsN.Cells(R, 5).Value = "n/a"
            Case xlOLEDBQuery
                wsN.Cells(R, 3).Value = "Query Table - OLEDB Query"
                wsN.Cells(R, 4).Value = "'" & qt.Connection
                wsN.Cells(R, 5).Value = "'" & qt.CommandText
            Case xlWebQuery
                wsN.Cells(R, 3).Value = "Web Query Table"
                wsN.Cells(R, 4).Value = "'" & qt.Connection
                wsN.Cells(R, 5).Value = "n/a"
            Case xlADORecordset
                wsN.Cells(R, 3).Value = "Query Table - ADO Recordset"
                wsN.Cells(R, 4).Value = PROGCREATE
                wsN.Cells(R, 5).Value = "'" & qt.Recordset.Source
            Case xlDAORecordset
                wsN.Cells(R, 3).Value = "Query Table - DAO Recordset"
                On Error Resume Next
                wsN.Cells(R, 4).Value = "'" & qt.Recordset.Parent.Name
                If Err.Number <> 0 Then
                    wsN.Cells(R, 4).Value = PROGCREATE
                    Err.Clear
                End If
                wsN.Cells(R, 5).Value = "'" & qt.Recordset.Name
                If Err.Number <> 0 Then
                    wsN.Cells(R, 5).Value = PROGCREATE
                    Err.Clear
                End If
                On Error GoTo errHandler
            Case xlODBCQuery
                wsN.Cells(R, 3).Value = "Query Table"
                wsN.Cells(R, 4).Value = "'" & qt.Connection
                wsN.Cells(R, 5).Value = qt.CommandText
            End Select

        Next
    Next
    vLnkSrc = wbA.LinkSources
    If Not IsEmpty(vLnkSrc) Then
        For i = 1 To UBound(vLnkSrc)
            R = R + 1
            wsN.Cells(R, 1).Value = "n/a"
            wsN.Cells(R, 2).Value = "n/a"
            wsN.Cells(R, 3).Value = "Link Source (Edit | Links)"
            wsN.Cells(R, 4).Value = vLnkSrc(i)
        Next
    End If
    wsN.Cells.WrapText = False
    wsN.Columns.AutoFit
    wsN.UsedRange.AutoFilter
    Exit Sub
errHandler:
    MsgBox "An error has occurred." & vbCr & Err.Number & _
            vbCr & Err.Description
    Resume Next
End Sub
  5. On the File menu, click Close and Return to Microsoft Excel.
  6. On the Tools menu, point to Macro, and then click Macros.
  7. In the list of macros, click ListWebQueryPivotTableLinks, and then click Run.

    Any external data sources are listed in a new workbook that displays their source, location, type, and details about the connection.

How to select all external data ranges in a single worksheet

To select all external data ranges in an Excel worksheet:
  1. Start Excel, and then open the workbook that contains external data ranges that you want to select.

    Click the sheet tab of the sheet that you want to check.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  3. In the Visual Basic Editor, click Module on the Insert menu.
  4. In the module window, type the following code:
    'This Sub procedure selects all cells in the worksheet that are part of
'external data ranges
Sub SelectAllQueryTables()
    FirstCell = 1
    For Each xQuery In ActiveSheet.QueryTables
        If FirstCell = 1 Then
            Set xRange = xQuery.ResultRange
            FirstCell = 0
        Else
            Set xRange = Application.Union(xRange, xQuery.ResultRange)
        End If
    Next xQuery
    xRange.Select
End Sub
  5. On the File menu, click Close and Return to Microsoft Excel.
  6. On the Tools menu, point to Macro, and then click Macros.
  7. In the list of macros, click SelectAllQueryTables, and then click Run.

    Any external data ranges are selected, and you can move from cell to cell in the selection by pressing the TAB key. Select a different worksheet and then repeat steps 6 and 7 to select the external data ranges in that worksheet.

How to determine if an external data range in a workbook updates automatically

To determine if an external data range automatically retrieves data:
  1. Select all the external data ranges in the worksheet by using the SelectAllQueryTables macro.
  2. Right-click a cell in any selected external data range, and then click Data Range Properties.
  3. In the External Data Range Properties dialog box, view the Refresh every n minutes check box and the Refresh data on file open check box.

    If either check box is selected, the data is automatically refreshed according to the interval that is set or when the file is opened. To prevent the data from being refreshed automatically, click to clear the Refresh every n minutes check box and the Refresh data on file open check box.
  4. Right-click a cell in the same selected external data range, and then click Parameters if that command is available.
  5. Click each of the items on the left side of the dialog box.

    If the Refresh automatically when cell value changes check box is selected, the external data range can retrieve data automatically. To prevent this, click to clear the check box.
  6. Repeat steps 2 through 5 for each external data range in the workbook.

How to select all PivotTables in a worksheet

To select all PivotTables in an Excel worksheet:
  1. Start Excel and open the workbook that contains PivotTables that you want to select. Click the sheet tab of the sheet that you want to check.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  3. In the Visual Basic Editor, click Module on the Insert menu.
  4. In the module window, type the following code:
    'This Sub procedure selects all cells in the worksheet that are part of
'PivotTable reports
Sub SelectAllPivotTables()
    FirstCell = 1
    For Each xQuery In ActiveSheet.PivotTables
        If FirstCell = 1 Then
            Set xRange = xQuery. TableRange2
            FirstCell = 0
        Else
            Set xRange = Application.Union(xRange, xQuery.TableRange2)
        End If
    Next xQuery
    xRange.Select
End Sub
  5. On the File menu, click Close and Return to Microsoft Excel.
  6. On the Tools menu, point to Macro, and then click Macros.
  7. In the list of macros, click SelectAllPivotTables, and then click Run.

    All PivotTables in the worksheet are now selected. Select a different worksheet and then repeat steps 6 and 7 to select the PivotTables in that worksheet.

How to determine if a PivotTable report in a workbook updates automatically

To determine if a PivotTable automatically retrieves data:
  1. Select all the PivotTable ranges in the worksheet by using the SelectAllPivotTables macro.
  2. Right-click a cell in any PivotTable, and then click Table Options.
  3. In the PivotTable Options dialog box, view the Refresh every n minutes check box and the Refresh on open check box.

    If either check box is selected, the data is automatically refreshed according to the interval that is set or when the file is opened. To prevent the data from being automatically refreshed, click to clear the Refresh every n minutes check box and the Refresh on open check box.
  4. Repeat steps 2 and 3 for each PivotTable in the workbook.

How to use the "Edit Links" dialog box

If cells in the workbook contain formulas that refer to external Excel workbooks, you can display these links and determine how they are updated by using the Edit Links dialog box. To use this dialog box:
  1. On the Edit menu, click Links to open the Edit Links dialog box.
  2. In the Edit Links dialog box, a list of links to external workbooks is displayed.
  3. To manually update all the data, click Update Values.
  4. To change the source of any link, first select the link that you want to change, and then click Change Source.

    Select a new source, and then click OK.
  5. To open any source, first select the link that you want to open, and then click Open Source.
  6. To break the link with the external data source, click Break Link.

    This command leaves the current data in the cells, but removes the reference to the external data.
  7. To check the status of any links and determine if they are still available, click Check Status.

REFERENCES

For more information about external data in Excel workbooks, click the following article number to view the article in the Microsoft Knowledge Base:

223789 How to minimize metadata in Microsoft Excel workbooks

For more information about how to prevent Excel from automatically updating or refreshing data, click the following article number to view the article in the Microsoft Knowledge Base:

248204 You are prompted to enable automatic refresh when you open a workbook in Excel

For more information about removing links, click the following article number to view the article in the Microsoft Knowledge Base:

288853 Defined name is not removed when you break a link in Excel

Modification Type:MinorLast Reviewed:11/29/2005
Keywords:kbxlslink kbformat kbhowto KB330383
©2004 Microsoft Corporation. All rights reserved.