Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Format error on export to Excel (2002)

    I have a query that is exported to Excel by the user so they can further manipulate it. On "some" computers it gives an error "File error, some number formats may be lost". Why would this occur on some computers and not others? I'm assuming its a setting but am not sure which one.

    Thanks,
    Leesha

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

    Re: Format error on export to Excel (2002)

    That error message usually means that the maximum number of custom number formats in the workbook has been exceeded, but it's not clear to me why that would occur in an export from Access, and only for some users.
    What happens if you export to a text file (for example .csv) or to HTML, and import that into Excel?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format error on export to Excel (2002)

    Hi Hans,

    How would I export it to a txt file? Presently they are using the following code to export:

    DoCmd.OutputTo acQuery, "qryStoreDemographicsExcelExportInitial", "MicrosoftExcelBiff8(*.xls)", "", True, "", 0
    Thanks!
    Leesha

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

    Re: Format error on export to Excel (2002)

    To export to text format:

    DoCmd.OutputTo acQuery, "qryStoreDemographicsExcelExportInitial", acFormatTXT, , True

    You could also use DoCmd.TransferSpreadsheet instead of DoCmd.OutputTo to export to Excel, but then you'd have specify the file name explicictly, or prompt for the file name before using DoCmd.TransferSpreadsheet.

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

    Re: Format error on export to Excel (2002)

    acFormatRTF exports to an .rtf file, a format compatible with Microsoft Word. That is not very suitable for use in Excel.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format error on export to Excel (2002)

    <P ID="edit" class=small>(Edited by Rudi on 25-Jan-08 14:46. Edited to add the drive letter.)</P>The Access Help file gives this example:
    DoCmd.OutputTo acOutputTable, "Employees", _
    acFormatRTF, "Employee.rtf", True

    Change yours to:
    DoCmd.OutputTo acQuery, "qryStoreDemographicsExcelExportInitial", acFormatRTF, , True
    To prompt for path
    or
    DoCmd.OutputTo acQuery, "qryStoreDemographicsExcelExportInitial", acFormatRTF, "C:StoreDemographicsExcelExportInitial.rtf", True
    To save to specific path. Change path in argument.
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format error on export to Excel (2002)

    TX for the correction.

    Other formats that can be used:

    AcFormat can be one of these AcFormat constants:
    acFormatASP
    acFormatDAP
    acFormatHTML
    acFormatIIS
    acFormatRTF
    acFormatSNP
    acFormatTXT
    acFormatXLS
    Regards,
    Rudi

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format error on export to Excel (2002)

    Hi Hans,

    When imported from a txt file it wasn't in a usable format in that it wasn't all in columns. However I tried taking out a few fields and then sending to excel again and it went fine.

    Thanks!
    Leesha

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

    Re: Format error on export to Excel (2002)

    When you import a text file into Excel, the Text Import Wizard offers various options. You should experiment with those to get things right.

Posting Permissions

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