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.


            • Dowload to Excel - Numerics becoming Text

              Thanks Vlad,

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




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





                • 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.