Define Excel Column Names Bernie 12.07.2006 00:11:02 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

RE: Define Excel Column Names Klatuu 12.07.2006 12:41:02 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. "Bernie" wrote: [Quoted Text] > 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

RE: Define Excel Column Names Bernie 12.07.2006 13:47:02 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 "Klatuu" wrote: [Quoted Text] > 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. > > "Bernie" wrote: > > > 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

Re: Define Excel Column Names "Roger Carlson" <NO-Roger.J.Carlson-SPAM[ at ]gmail.com> 12.07.2006 14:04:11 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 "Bernie" <Bernie[ at ]discussions.microsoft.com> wrote in message news:215A5E74-4B41-4099-9410-3160B0461C32[ at ]microsoft.com... [Quoted Text] > 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 > > "Klatuu" wrote: > > > 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. > > > > "Bernie" wrote: > > > > > 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

RE: Define Excel Column Names Klatuu 12.07.2006 14:14:01 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. "Bernie" wrote: [Quoted Text] > 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 > > "Klatuu" wrote: > > > 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. > > > > "Bernie" wrote: > > > > > 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