Define Excel Column Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to export a query to Excel and define a header and column names.
I believe this is possible via VBA.

Thanks,
Bernie
 
It depends on how you want to do the export. The TransferSpreadsheet method
does not offer that capability. You could use COM to manipulate the
spreadsheet manually or the simplest way would be to do it in the query.
 
I should have been more clear. The column names would be named based on
field values on a form, i.e. 3Q06 4Q06 1Q08 (changing as the date range
chnaged).

I understood this might be possible via VBA.

Regards,
Bernie
 
You can use VBA to create your query programmatically using controls on the
form to create aliases for the field names. Then you can export your query
any way you want.

Without specific field, table and form names, it's difficult to give a more
complete answer.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to create queries
programmatically. It doesn't specifically address your question, but it
gives the general process

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Yes, you can do it with code. It takes some moderately advanced VBA.
One approach would be to build a template query that you will not actually
use, but will use to create the query you use to do the TransferSpreadsheet.

When you build your query, make each field where the column names will
change a calculated field with a name you can use in a Replace function to
fill in the name you really want to use. For example:

Qtr1: [FirstQtr] Qtr2: [SecondQtr]

When you want to do the Transfer, you will need to read the SQL of your
template into a string variable, replace the names in the SQL (Qtr1, Qtr2,
etc) with the values in your form's controls using the Replace function.
Then save the query using the CreateQueryDef method. Now you can run your
TransferSpreadsheet and the columns will have the names you assigned in the
query.
 
Back
Top