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

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 Moduleon 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 Moduleon 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 Propertiesdialog 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 minutescheck 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 Moduleon 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

Properties

Article ID: 330383 - Last Review: Apr 29, 2008 - Revision: 1

Feedback