Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Access Query Headers in Excel Export (2003)

    I may not be searching the database correctly, so if this has been asked and answered, please accept my apology.

    I have some tables that I am "reusing" for a different project. The table structures are perfect for what I want to do, except for the "Captions" on the fields. I need to export data from the tables into Excel for a user. I use a query like the following, expecting to have the headers show the "AS [whatever]" as the headers. Instead it uses the "captions" from the tables themselves.
    SELECT MyTable.Cmpny Nme AS Company, MyTable.Hier1 AS Region, MyTable.Hier3 AS [Profit Center], MyTable.Hier5 AS [Branch/Location], MyTable.PrsnID AS [Claimant ID], MyTable.IncDte AS [Loss Date]
    FROM MyTable LEFT JOIN tblIncIns ON MyTable.CseNmbr = tblIncIns .fkCseNmbr;

    Does anyone have a suggestion of how to export the data to excel and use the "AS" names instead of the table captions?

    Thanks in advance for your assistance.

    kwvh

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access Query Headers in Excel Export (2003)

    If you do the Export with DoCmd.OutputTo acOutputQuery , queryname, acFormatXLS, Excelfilename

    it will preserve formatting of the query, including the "AS" values.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access Query Headers in Excel Export (2003)

    John,

    Thanks for the response. I was using the following:
    DoCmd.TransferSpreadsheet acExport, 8, "sqRprt4WR", "C:TempWRExprt.xls", True, ""

    Using your example presented the same spreadsheet. It is still using the "caption" info from the fields in the tables, not the "AS" labels.

    Thanks again for your suggestion.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Access Query Headers in Excel Export (2003)

    You are right.

    Docmd.Outputto does use the As if there is no Caption, but with a Caption it is that that is used.


    Sorry. I did test this but not carefully enough.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access Query Headers in Excel Export (2003)

    Does anyone else have any ideas?

    thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Query Headers in Excel Export (2003)

    I'm using Office XP.

    If I use Tools | Office Links | Analyze with Microsoft Excel, I get the captions as defined in the table, unless I specifically set field captions in the field properties in the query. The aliases defined in the SQL statement (AS ...) are ignored. Similar if I use DoCmd.OutputTo.

    If I use File | Export, the aliases defined in the SQL statement are used. The field captions defined in the table or in the query are ignored. Similar if I use DoCmd.TransferSpreadsheet.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Access Query Headers in Excel Export (2003)

    Hans,

    I am using Access 2003. I initally exported the query to an Excel spreadsheet without "AS'ing" any columns. I then used the AS to change the headers and exported the query, with the same results. After reading your response, I deleted the original Excel spreadsheet and re-exported the spreadsheet. Viola', now the header row in the spreadsheet reflects the changes I wanted.

    I don't know why it was so difficult.

    Thanks for your assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •