Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
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 |
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,
Many Thanks , Has worked now!