Microsoft KB Archive/330383

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

Article ID: 330383

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



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:  Start Excel, and then open the workbook that you want to search for external data sources. On the Tools menu, point to Macro, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu.  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 = &quot;This external &quot; & _ &quot;data range was created &quot; & _ &quot;programmatically and cannot be edited&quot; On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsN = Workbooks.Add(xlWorksheet).Worksheets(1) wsN.Name = wbA.Name wsN.Range(&quot;A1:E1&quot;).Value = Array(&quot;Name&quot;, &quot;Location&quot;, _           &quot;Type&quot;, &quot;Connection&quot;, &quot;CommandText&quot;) wsN.Range(&quot;A1:E1&quot;).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 & &quot;!&quot; & _ 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 & &quot;!&quot; & _ pt.TableRange2.Address(False, False) wsN.Cells(R, 3).Value = _ &quot;PivotTable - Consolidation Range&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & _ .SourceData(i, 1) wsN.Cells(R, 5).Value = &quot;n/a&quot; Next Case xlDatabase wsN.Cells(R, 3).Value = &quot;PivotTable - Excel List&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & .SourceData wsN.Cells(R, 5).Value = &quot;n/a&quot; Case xlExternal If .OLAP Then wsN.Cells(R, 3).Value = &quot;PivotTable - OLAP&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & .Connection wsN.Cells(R, 5).Value = .CommandText ElseIf .QueryType = xlADORecordset Then wsN.Cells(R, 3).Value = _ &quot;PivotTable - ADO Recordset&quot; wsN.Cells(R, 4).Value = PROGCREATE wsN.Cells(R, 5).Value = &quot;'&quot; & .Recordset.Source Else wsN.Cells(R, 3).Value = _ &quot;PivotTable - External Data&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & .Connection wsN.Cells(R, 5).Value = .CommandText End If               Case xlScenario wsN.Cells(R, 3).Value = &quot;PivotTable - Scenario&quot; wsN.Cells(R, 4).Value = &quot;Based upon a Scenario &quot; & _ &quot;in this workbook&quot; wsN.Cells(R, 5).Value = &quot;n/a&quot; 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 & &quot;!&quot; & _ qt.ResultRange.Address(False, False) Select Case qt.QueryType Case xlTextImport wsN.Cells(R, 3).Value = &quot;Text Import&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & qt.Connection wsN.Cells(R, 5).Value = &quot;n/a&quot; Case xlOLEDBQuery wsN.Cells(R, 3).Value = &quot;Query Table - OLEDB Query&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & qt.Connection wsN.Cells(R, 5).Value = &quot;'&quot; & qt.CommandText Case xlWebQuery wsN.Cells(R, 3).Value = &quot;Web Query Table&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & qt.Connection wsN.Cells(R, 5).Value = &quot;n/a&quot; Case xlADORecordset wsN.Cells(R, 3).Value = &quot;Query Table - ADO Recordset&quot; wsN.Cells(R, 4).Value = PROGCREATE wsN.Cells(R, 5).Value = &quot;'&quot; & qt.Recordset.Source Case xlDAORecordset wsN.Cells(R, 3).Value = &quot;Query Table - DAO Recordset&quot; On Error Resume Next wsN.Cells(R, 4).Value = &quot;'&quot; & qt.Recordset.Parent.Name If Err.Number <> 0 Then wsN.Cells(R, 4).Value = PROGCREATE Err.Clear End If               wsN.Cells(R, 5).Value = &quot;'&quot; & 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 = &quot;Query Table&quot; wsN.Cells(R, 4).Value = &quot;'&quot; & 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 = &quot;n/a&quot; wsN.Cells(R, 2).Value = &quot;n/a&quot; wsN.Cells(R, 3).Value = &quot;Link Source (Edit | Links)&quot; wsN.Cells(R, 4).Value = vLnkSrc(i) Next End If   wsN.Cells.WrapText = False wsN.Columns.AutoFit wsN.UsedRange.AutoFilter Exit Sub errHandler: MsgBox &quot;An error has occurred.&quot; & vbCr & Err.Number & _ vbCr & Err.Description Resume Next End Sub  On the File menu, click Close and Return to Microsoft Excel. On the Tools menu, point to Macro, and then click Macros. 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.</li></ol>

How to select all external data ranges in a single worksheet
To select all external data ranges in an Excel worksheet: <ol> 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.</li> On the Tools menu, point to Macro, and then click Visual Basic Editor.</li> In the Visual Basic Editor, click Module on the Insert menu.</li>  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 </li> On the File menu, click Close and Return to Microsoft Excel.</li> On the Tools menu, point to Macro, and then click Macros.</li> 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.</li></ol>

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  minutes check box and the Refresh data on file open check box.
 * 1) Right-click a cell in the same selected external data range, and then click Parameters if that command is available.
 * 2) 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.
 * 1) 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: <ol> 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.</li> On the Tools menu, point to Macro, and then click Visual Basic Editor.</li> In the Visual Basic Editor, click Module on the Insert menu.</li>  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 </li> On the File menu, click Close and Return to Microsoft Excel.</li> On the Tools menu, point to Macro, and then click Macros.</li> 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.</li></ol>

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   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  minutes check box and the Refresh on open check box.
 * 1) Repeat steps 2 and 3 for each PivotTable in the workbook.

How to use the &quot;Edit Links&quot; 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.
 * 1) To open any source, first select the link that you want to open, and then click Open Source.
 * 2) 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.
 * 1) To check the status of any links and determine if they are still available, click Check Status.

<div class="references_section">