Solved Custom column names on export to excel? (1 Viewer)

[mamradzelvy]

Hi, is it possible to have my query pull data from the db and put it under new custom column names for the excel export?
I have a db with shortcuts which are not exactly pleasant to look at by anybody but me and i would like to be able to export these under diferent names without actually hard modifying the column names in the table.

 

[arnelgp]

create a Query and Alias the Column into something else.
import this query to excel, eg:

SELECT ID AS Ident, ProdID AS ItemCode, UnitPrice AS Price, etc.... FROM table1

 

[deletedT]

In Excel If you're using a Pivot table, you can change the field name:

 

[Uncle Gizmo]

without actually hard modifying the column names in the table.

I think you have the answer already, however your question reminded me of a product I built last year which extracts a forms control names, the captions from the associated labels and the underlying field name for each control. You can then use them as column names in Excel.

I'm thinking that if your table is used in a form then it is likely that you have a button on your form for running the query. You could use code to extract the actual names from the controls labels. This extra coding required would only be desirable/necessary if you were making something that you wanted to use in many different forms.

In my code, different versions of the control names are stored in a listbox and then the information is extracted from the list box and passed through to Bob's code with some code I found here:-

Insert Text array as Headers (VBA, EXCEL)

BACKGROUND I've created a VBA macro to pull in financial data from various business units. OBJECTIVE After consolidating each business unit's financial information, insert a row with column head...

stackoverflow.com

The main component for this process was some code written by Bob Larson which you can find on this website here:-

Export A Table Or Query To Excel | BTAB Development

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don't already have one. Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String) ' strTQName is the name of...

btabdevelopment.com