XL: Field Instead of Column Name in MSQUERY Returned to Excel

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Query for Windows, version 1.0
• Microsoft Query for Windows 95, version 2.0
• Microsoft Query 97 for Windows

SYMPTOMS

In Microsoft Excel, if you return data from Microsoft Query by using the Get External Data command, or the PivotTable Wizard (using an external data source), any column heading that you have renamed in Microsoft Query displays with the original field name, or as EXPR_x (where x is any number) when you return the data to Microsoft Excel.

CAUSE

This behavior is by design of Microsoft Excel. When you return data from a query in Microsoft Query to a worksheet, or to the PivotTable Wizard in Microsoft Excel, the actual names of the columns in your query are used, instead of the column headings. This is true whether the field heading is based on a text value or an expression.

For example, if one of the columns is the sum of a field called Sales, the column heading in Microsoft Query is Sum(Sales). If you change this heading to a more descriptive name such as "Sales Totals", the heading displays with the original name "Sum(Sales)" when you return the data to Microsoft Excel.

If you build a query and return the results to the Pivot Table Wizard, and a field name contains an expression, such as CUSTOMER + SALES, then the field name is returned in the format EXPR_x, where x is any number. For example, if two different columns in your query have names that contain an expression, the first column name is returned as EXPR_1 and the second as EXPR_2.

WORKAROUNDS

To work around this behavior, use one of the following methods:

Modifying SQL Statement

Note that you can use this workaround to return data either to a worksheet, or to the PivotTable wizard.

Instead of using the Edit Column dialog box to rename a column, you can modify the SQL SELECT statement using the AS clause which allows you to return data to Microsoft Excel with any custom field heading you want.

This method creates a non-graphical query, meaning that only the data set is displayed in Microsoft Query. When you run the SQL statement using the AS clause, you receive the following error message in Microsoft Query.

   SQL Query can't be represented graphically. Continue anyway?

   SELECT <field name> AS "<new field heading text>" FROM
   <table name>

1. In Microsoft Query, create the query that you want to return to Microsoft Excel.

2. On the View menu, click SQL.

3. In the SQL Statement box, modify the SELECT statement using the syntax provided above. For example, the following SQL statement selects all of the records from the Orders table and displays the order ID and sum of sales for each unique Order_Id:

         SELECT Order_Id AS "ID", Sum(Sales) as "Sales Totals" FROM orders
   

   4. Click OK to run the SQL statement.

Worksheet

To work around this behavior when you return data in Microsoft Query to a Microsoft Excel worksheet, do not include the field names when you return the data to the worksheet. Then you can enter your own field names directly on the worksheet. To avoid including field name, clear the Include Field Names check box in the Get External Data dialog box that appears when you return the data to Microsoft Excel.

Pivot Table

To rename a field in a Pivot Table, do the following:

1. In the PivotTable Wizard - Step 3 of 4 dialog box, or in the pivot table on your worksheet, double-click the field name to display the PivotTable Field dialog box.

2. In the Name box, enter the new name for field, and choose OK.

SQLExecQuery() Function in Visual Basic Module

Sub UseAlias()
   Dim chan As Variant
   Dim result As Variant

   chan = sqlopen("Dsn=NWind")
   result = _
   SQLExecQuery(chan, "SELECT LAST_NAME as ""Last Name"" FROM

   If IsError(result) Then
      MsgBox SQLError()(3)
   End If

   SQLRetrieve chan, ActiveCell, , , True
   SQLClose chan

End Sub

   http://www.microsoft.com/supportnet/refguide/

REFERENCES

For more information about The Setup You Need To Retrieve Data With Microsoft Query, choose the Search button in Help and type:

   Query