4 Replies Latest reply: Feb 8, 2011 6:27 AM by Jeff Turnbull RSS

    Dowload to Excel - Numerics becoming Text

      Can anyone assist with the issue of excel downloads where numerics are converted to text . I know that I can correct by error checking in Excel but surely there must be a way to ensure that downloaded QV data comes across correctly. The fields in error are calculated dimensions within a chart.

        • Dowload to Excel - Numerics becoming Text
          Vlad Gutkovsky

          If you want to make sure your numbers remain number on an export to Excel, wrap it in a num() function. Conversely, if you want it to be text, wrap it in a text() function.

          Regards,

            • Dowload to Excel - Numerics becoming Text

              Thanks Vlad,

              I had already used the above ( here is the example)

               

               

              =num(stockm.physical_qty*stockm.current_cost,'#,##0.##')

              The strange thing is whole numbers are OK . Its when its not i.e. 150.24 that it throws out.

              Rgds

              Jeff



               



               

                • Dowload to Excel - Numerics becoming Text
                  Vlad Gutkovsky

                  Ah, I understand. I've seen this problem before and was baffled by it too. The way I ended up fixing it was disabling the default Send-to-Excel on the chart and instead creating a macro that sent it to Excel and was triggered by a button. I then applied the formatting directly in Excel. I actually like this type of export better because it gives you more granular control over the output formatting. For example:

                   


                  SET XLApp = CreateObject("Excel.Application")
                  XLApp.Visible = False
                  SET XLDoc = XLApp.Workbooks.Add
                  SET XLSheet = XLDoc.Worksheets(1)
                  ActiveDocument.GetSheetObject("EXP1A").CopyTableToClipboard true
                  XLSheet.Paste XLSheet.Range("A1")
                  XLSheet.Range("B1:B5").NumberFormat = "$#,##0.00;($#,##0.00)"
                  XLSheet.Range("C1:C5").NumberFormat = "#,##0"
                  XLApp.Visible = True


                  The problem is that I doubt this will work over AJAX so if that's a requirement, this solution won't help you.

                  Cheers,