none
date locale in cell RRS feed

  • Question

  • Hi!

    I have a  program running in SAP that opens a Excel workbook and reads out the nurmber format of each column of the worksheet.

    This works well also with dates assuming the user uses the format belonging to the locale from the registry.

    However in Excel 2007 the user can change the locale for eacch cell separatly and thus have a format different from the registry (registry is nl-NL and user chooses a format from en-GB.

    I've searched the internet for days trying to find out how to read this setting per cell with VBA.

    Does anyone have a clue or a workaround?

    Thnaks!

    Robin

    Thursday, July 7, 2011 7:01 AM

Answers

  • Depending on the particular format you may be able to return the country ID from the first few characters of the numberformat, eg this is what I have after setting cell's local date to Azer(Latin)
    [$-42C]dd mmmm yyyy;@

    The code, which may be in integer or HEX can be looked up in this table
    http://msdn.microsoft.com/en-us/goglobal/bb964664
    (there's another table like this specifically for Excel dates which I can't find at the moment)

    However, although I don't quite follow what you are doing, I suspect a different approach will work better for you. Eg, Split your string date into individual day month year values, and write as appropriate to a cell with a known date format (that you have written). Or convert to a date value with DateSerial(). Then write the date value to user's date formatted cells. If General they will convert automatically.

    Peter Thornton

    • Marked as answer by hoefje Friday, July 8, 2011 8:41 AM
    Thursday, July 7, 2011 10:50 AM
    Moderator

All replies

  • MsgBox ActiveCell.NumberFormat & vbCr & ActiveCell.NumberFormatLocal

    Peter Thornton

    Thursday, July 7, 2011 9:05 AM
    Moderator
  • Hi!

    From SAP I am reading the NumberFormat from the Excelsheet.

    Problem is that I have defined in SAP Numberformats per Locale in order to convert the Excel date to the internal format YYYYMMDD.

    As a workaround the program throws an error when he can't find the NumberFormat that I have defined in SAP. But then I have to go thru all of the defined formats. It would be easier if I could read out the Locale of the Cell.

    Thursday, July 7, 2011 9:16 AM
  • >. It would be easier if I could read out the Locale of the Cell.

    I don't follow, doesn't the example I suggested with NumberFormatLocal return this for you?

    Peter Thornton

    Thursday, July 7, 2011 9:24 AM
    Moderator
  • No, when I take for example the locale Azeri(Latin, Azerbaijan), which is very unlikely but okay it will return for me:

    NumberFormat            dd.mm.yyyy;@

    NumberFormatLocat   dd.mm.jjjj;@

    which can fit for more then one Locale, for example Dutch.

    Thursday, July 7, 2011 9:33 AM
  • Depending on the particular format you may be able to return the country ID from the first few characters of the numberformat, eg this is what I have after setting cell's local date to Azer(Latin)
    [$-42C]dd mmmm yyyy;@

    The code, which may be in integer or HEX can be looked up in this table
    http://msdn.microsoft.com/en-us/goglobal/bb964664
    (there's another table like this specifically for Excel dates which I can't find at the moment)

    However, although I don't quite follow what you are doing, I suspect a different approach will work better for you. Eg, Split your string date into individual day month year values, and write as appropriate to a cell with a known date format (that you have written). Or convert to a date value with DateSerial(). Then write the date value to user's date formatted cells. If General they will convert automatically.

    Peter Thornton

    • Marked as answer by hoefje Friday, July 8, 2011 8:41 AM
    Thursday, July 7, 2011 10:50 AM
    Moderator