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

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.

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.

For more information about external data in Excel workbooks, click the following article number to view the article in the Microsoft Knowledge Base: 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: For more information about removing links, click the following article number to view the article in the Microsoft Knowledge Base: