XL5: Records Lost Adding Field Names with MSQuery

• Microsoft Excel for Windows, version 5.0

SUMMARY

When you use the Get External Data command to include field names or row numbers in a result set returned from Microsoft Query version 1.0, the last row and/or column in the list may be lost.

MORE INFORMATION

When you retrieve data from an external data source using the Get External Data command on the Data menu, you have the option of whether you want to include field names and/or row numbers in the result set. The range on the worksheet that contains the pasted data is referred to as the data range.

If you retrieve data to Microsoft Excel from Microsoft Query without the field names and/or row numbers, and you later add these to the result set, data may be lost from the worksheet. This loss of data occurs because the Microsoft Query add-in does not redefine the new data range before returning the data to Microsoft Excel. Instead, to make room for the new headings, it shifts the existing records down if you select the Include Field Names option, or it shifts data to the right, if you select the Include Row Numbers option.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

WORKAROUND

To properly display the new data set, do the following:

1. Select a cell in the data range or highlight the entire data range.

2. Select Get External Data from the Data menu.

3. Select the Edit Query button. Microsoft Query will be opened with the current query and updated result set.

4. Select Return Data to Microsoft Excel from the File menu.

5. Select the Include Field Names and/or Include Row Numbers check boxes, and choose the OK button.