Results 1 to 5 of 5
  1. #1
    Rick West is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    90

    Access export to Excel

    General question - when exporting the results of an Access query to Excel a part number in Access as 13-0011 turns up in Excel as 40495.



    Is there a reasonable explanation for why this happens?

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    sanity check your field type in Access (probably is Text) vs the cell type in Excel....

    do a test and force that excel cell type to text; one way is to put some dummy records in the top of your Access table that are clearly text i.e lkjlkjlkjlkjlkjlkj

  3. #3
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    I've tried this in Access 07 and Excel 07. Attempted using different ways to troubleshoot and continue to get the CORRECT (i.e. 13-001) result in Excel every time. Not sure why Excel is converting your cells to a number.
    What version of Excel are you using?
    Right click the number cell in Excel and check the Format type. Does it show General type?
    When using Access to Output the table or query to Excel, what is the Output Format you are using? i.e. xls, xlsx

    -RC

  4. #4
    Rick West is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    90
    I am using both Access and Excel '03, maybe in '07 this was fixed?

    I know it's frustrating when I query the data but the user's want to see the results in Excel. I have warned them to proof the data because I know Excel likes to drop leading zeros but this is the first time I've ever seen it change a number too.

    If I copy/paste from Access to Excel - it works like it's supposed too, no pn changes, but when I export from the Access query to Excel - bad news.

    Thanks for the ides guys. Keep up the good work. We in user-ville appreciate it.

  5. #5
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    Sorry Rick,
    The reason I was troubleshooting in Access 07 and Excel 07 was because your screen name says you are using Access 2007 version 12.0.
    I know you previously marked this thread as solved, but consider NTC's previous recommendation by adding a dummy record into your table. Only thing with going this route though, is that you will need to remove the dummy record every time from your exported Excel doc.
    Or you can test on a dummy table, and add an alpha character to all your part numbers. May not be feasible depending on the development stage of your database though.
    Regards,

    -RC

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

Similar Threads

  1. command button to export to excel
    By jains in forum Forms
    Replies: 5
    Last Post: 06-30-2015, 06:27 PM
  2. Export Access Pivot Table to Excel
    By graviz in forum Programming
    Replies: 1
    Last Post: 11-13-2009, 07:30 AM
  3. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09:22 AM
  4. Replies: 1
    Last Post: 05-28-2009, 05:08 AM
  5. Export to Excel
    By vaikz in forum Import/Export Data
    Replies: 3
    Last Post: 03-25-2009, 09:37 AM

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