locked
Number format lost when refreshing load to excel table RRS feed

  • Question

  • Hello All

    I have a query which loads to a table and exports to a CSV which then loads to the accounting system.  The query balances but when I refresh some lines get rounded and lose some significant figures.  The number in the cell is correct but the number displayed is rounded and this is what gets saved to the CSV, which means journal doesn't balance. (see attached)

    I have tried formatting the field in the query, and the table, to Currency but every time I refresh the query the formatting is lost.

    If I reformat the columns the csv works again but it feels like I am missing something obvious.  I have been trying to sort this for hours.  Any thoughts appreciated.

    Many thanks

    Monday, February 26, 2018 10:41 PM

Answers

  • Thanks Igor, I did go back and checked this (i hadn't checked it so good call) and it was set to 'preserve'.  I ended up getting around it by setting the cell format to ActiveWorkbook.Sheets("Sheetx").Columns("AM:AN").NumberFormat = "0.00#########" but this requires the columns to be static.

    Just tried to recreate the issue - working fine now - guess it was one of those gremlins in the system.

    • Marked as answer by PQUK Monday, March 5, 2018 6:05 PM
    Monday, March 5, 2018 6:04 PM

All replies

  • Maybe it is just the matter of choosing to Preserver the cell formatting in the properties of the query connection

    Tuesday, February 27, 2018 8:10 AM
  • Thanks Igor, I did go back and checked this (i hadn't checked it so good call) and it was set to 'preserve'.  I ended up getting around it by setting the cell format to ActiveWorkbook.Sheets("Sheetx").Columns("AM:AN").NumberFormat = "0.00#########" but this requires the columns to be static.

    Just tried to recreate the issue - working fine now - guess it was one of those gremlins in the system.

    • Marked as answer by PQUK Monday, March 5, 2018 6:05 PM
    Monday, March 5, 2018 6:04 PM