Results 1 to 2 of 2
  1. #1
    octu is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    1

    Exporting to Excel Problem

    I ran a query in Access 2007 with the following results (1st row is header row):
    Cmp S1 S2 S3


    ABC 0 48 86
    CDE 0 0 0
    EFG 94 10 53

    No problem so far, that's what I expected to see. The problem is when I export this to Excel 2007, without checking the "export data with formatting and layout" box, Excel only shows:
    Cmp S1 S2 S3
    ABC 0 48 86
    EFG 94 10 53

    Company CDE completely disappears! However, when I go back to export it to Excel again, this time checking the "export data with formatting and layout" box, low and behold the Excel data now matches the query output.

    Ok, so why in the world does company CDE, just because it has zeros in all it's value fields, get "nuked" when exporting it without formatting options? I just want the raw data dumped to Excel without formatting, yet there is no guarantee that all records will be returned without checking the "export data with formatting and layout" box? Company CDE values aren't null, and this is clearly not a leading zero issue, so completely stumped here, not even sure if it's a problem with the way Access exports or the way Excel treats the incoming data.

    Any insights would be appreciated!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what's the difference between layout and no layout? can you easily fix it in excel if importing WITH layout? i've never run into this, but maybe it is supressing the column because it is 0s. have you tried this code in the query:
    Code:
    select cstr([CDE column here]) 
    that might fix any issue of excel thinking there are simply 0s in the field and therefore ignoring it. i doubt it would ignore STRING datatypes., which is what is outputted with the above sql

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Really Need Help With Exporting to Excel
    By graviz in forum Import/Export Data
    Replies: 0
    Last Post: 09-24-2009, 08:29 AM
  2. Exporting Access queries to Excel
    By dbDamo in forum Import/Export Data
    Replies: 2
    Last Post: 09-22-2009, 01:42 AM
  3. Excel exporting in single cells
    By Paolo29011982 in forum Import/Export Data
    Replies: 0
    Last Post: 07-29-2009, 04:48 AM
  4. Exporting a Report to Excel
    By bullwinkle55423 in forum Reports
    Replies: 0
    Last Post: 12-11-2007, 10:27 AM
  5. Exporting a chart to Excel?
    By hraup in forum Access
    Replies: 0
    Last Post: 08-31-2006, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums