#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    Change field names in query export to Excel


    I am trying to output an Access query into an Excel format for a user feedback survey where users rate certain aspects of their experience with numerical values. I would like to export the entire table (within date parameters entered by the user). The problem I am having is that I need the field names on the Excel report to display as the full questions. Currently, the field names are named A, B, C, etc because the full questions exceed alias character limitations. So for example, I would rather my data look like this..

    User R
    user How do you rate the overall experience... Rate the ease of use of the blah blah... Rate the yada, yada, yada...
    John 1 3 2
    Sue 2 4 3

    ...than this...

    User A B C
    John 1 3 2
    Sue 2 4 3


    The output must be in Excel because the reports will be distributed to an audience that may want to do their own analysis with the data.

    Help please! And thanks in advance.
  2. #2
  3. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Sorry for the random floating "User R". Ignore that.
  4. #3
  5. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    I think then your only option is to modify the Excel file after the data is exported.

    Can be manual or coded modification.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    I was afraid of that. The current process is pasting in the correct column headings once the data is exported to Excel, but my manager would like to eliminate that step.
  8. #5
  9. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    are your questions saved in another table? You could use a union query to add a row containing the questions. you'd still get the letter column headings, but then at least maybe you'd be able to get the actual questions too.
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    That might just work! Thanks for the suggestion! I'll try it out and see how it looks.
  12. #7
  13. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Wow, brilliant, merati!

    trishk, are you using TransferSpreadsheet method for the export? I just tested with UNION query and it did work.
  14. #8
  15. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2011
    Posts
    37
    Rep Power
    4
    With the report in Design View, Right click on the fields and select properties. Select the "Other" Tab and in the Field Name type in the heading you wish to appear on yoru excel report.
    That is what I do to one of my reports and it seems to work Ok

Similar Threads

  1. Export to Excel with File Name created by Field Name
    By Griggsy in forum Microsoft Access Help
    Replies: 2
    Last Post: October 6th, 2011, 02:26 AM
  2. Export query to excel
    By coolhandphil in forum Microsoft Access Help
    Replies: 2
    Last Post: October 13th, 2009, 03:37 AM
  3. Export a simple query result to an excel file
    By wop_nuno in forum Microsoft Access Help
    Replies: 13
    Last Post: March 12th, 2009, 04:21 AM
  4. Export to Excel, update two fields in a query question
    By bdhtexas in forum Microsoft Access Help
    Replies: 3
    Last Post: June 10th, 2005, 11:47 AM
  5. Export query to Excel
    By wmgrfri in forum Microsoft Access Help
    Replies: 18
    Last Post: April 19th, 2005, 04:43 PM

IMN logo majestic logo threadwatch logo seochat tools logo