XL5: Records Lost Adding Field Names with MSQuery
ID: Q121816
|
The information in this article applies to:
-
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:
- Select a cell in the data range or highlight the entire data range.
- Select Get External Data from the Data menu.
- Select the Edit Query button. Microsoft Query will be
opened with the current query and updated result set.
- Select Return Data to Microsoft Excel from the File menu.
- Select the Include Field Names and/or Include Row
Numbers check boxes, and choose the OK button.
Microsoft Excel will clear the old data range and create a new data range
with the updated query results.
Additional query words:
Keywords : xlquery
Version : 5.00
Platform : WINDOWS
Issue type :