You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q121551
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In Microsoft Excel, if you return data from Microsoft Query by using theGet External Data command, or the PivotTable Wizard (using an externaldata source), any column heading that you have renamed in Microsoft Querydisplays with the original field name, or as EXPR_x (where x is anynumber) when you return the data to Microsoft Excel.
This behavior is by design of Microsoft Excel. When you return data from aquery in Microsoft Query to a worksheet, or to the PivotTable Wizard inMicrosoft Excel, the actual names of the columns in your query are used,instead of the column headings. This is true whether the field heading isbased on a text value or an expression.

For example, if one of the columns is the sum of a field called Sales, thecolumn heading in Microsoft Query is Sum(Sales). If you change thisheading to a more descriptive name such as "Sales Totals", the headingdisplays with the original name "Sum(Sales)" when you return the data toMicrosoft 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, thenthe 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 thatcontain an expression, the first column name is returned as EXPR_1 andthe second as EXPR_2.
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 aworksheet, or to the PivotTable wizard.

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

This method creates a non-graphical query, meaning that only the data setis displayed in Microsoft Query. When you run the SQL statement using theAS clause, you receive the following error message in Microsoft Query.
SQL Query can't be represented graphically. Continue anyway?
Use the following syntax for this type of SELECT statement:
   SELECT <field name> AS "<new field heading text>" FROM   <table name>				
The following example uses the Sales field example discussed earlier.
  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 earlier. 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						
    Note that in the above example, the new field name "ID" does not require quotation marks because it is a single word, however, it is recommended that you place quotation marks around the new field heading to provide consistent results.
  4. Click OK to run the SQL statement.
In this example, the field headings are now displayed as ID, and SalesTotals, instead of as Order_Id and Sum(Sales).


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

Pivot Table

To rename a field in a Pivot Table, follow these steps:
  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 click 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      employee")   If IsError(result) Then      MsgBox SQLError()(3)   End If   SQLRetrieve chan, ActiveCell, , , True   SQLClose chanEnd Sub				
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.
For more information about the setup you need to retrieve data withMicrosoft Query, click the Search button in Help and type:
5.00c 8.00 97 alias wrong

Article ID: 121551 - Last Review: 08/15/2005 22:35:55 - Revision: 2.1

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Query 2000
  • kbualink97 KB121551