Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Birmingham, UK
    Posts
    19

    Question Unanswered: Currency values and export to Excel

    Hi,

    I have a Crystal report that displays currency correctly but when exporting the report to Excel the currency is displayed in $ and not . Excel shows the format of the relevent cells as custom?

    The currency of the report is set to system default which is . Crystal is V8.5 and I have tried exporting in all the Excel formats avaliable.

    Does anyone know if there is there a workaround in Crystal or if I am making some basic mistake?

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    Hi

    The default currency format in Excel is $ and thats why you get $ instead of
    Madhivanan

    Failing to plan is Planning to fail

  3. #3
    Join Date
    Jun 2004
    Location
    Birmingham, UK
    Posts
    19
    Do you mean that a Crystal report (even v8) exported to Excel will always display as $ as Excel will choose to show $ until the currency is specificaly formated as .

    Users are saying that this did not happen previously, and have given some evidence that the report used to export as !? The regional settings on the machines are correct, can there be any other cause?

    They also report blank rows now appeare between data rows. This seems to be due to a slight formating change in a new version of the report.
    Last edited by Maara; 03-08-05 at 07:19.

  4. #4
    Join Date
    Jun 2004
    Location
    Birmingham, UK
    Posts
    19

    Exclamation

    Crystal reports are attached to an MS Access97 database, there are several fields that calculate monetary values and correctly display them as . When this report is exported to Excel the values are shown in $

    A quick test on another mdb database and report downloaded from business objects site. If I take a non currency field and set it to currency it displays and exports with the correct symbol, but fields that pull currency data from the db only display correctly in the report and are exported to xls as $,

    What is going on with this? I must be doing something very wrong? database and rpt are attached
    Attached Files Attached Files
    Last edited by Maara; 03-08-05 at 11:42.

  5. #5
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    I opened up your report in Crystal 10 and exported to Excel ... it worked fine ( not $).

    I have a server running Crystal 8.5. I opened up the same report in Crystal 8.5 and get the $ instead of the .
    Last edited by bcummings; 03-08-05 at 13:54. Reason: More information

  6. #6
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Lightbulb

    You can try this:


    For each field that will display a currency, create a formula that converts to a number, then to a string, and adds the currency symbol () to the left of the string.

    E.g. for the Order Amount field:

    "" + Cstr(CDbl({Orders.Order Amount}))

    Use this formula in place of the OrderAmount column on your report.

    When you export to Excel, it sees this a general field format, not a currency field, and the remains.

    Alternatively, upgrade to a later version of Crystal if you can.


    Does that help?

    Best regards,

    Barry

Posting Permissions

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