none
Setting en-us Numberformat via OLE/COM Automation. RRS feed

  • Question

  • Hello,

    I want to ask and set the numberformat of a range via OLE/COM Automation.

    I know already that

    _variant_t vformat = this->xRange->NumberFormat;
    this->xRange->PutNumberFormat(format);

    Unfortunatly there's a big Problem.

    The Attribut NumberFormat Returns and expect the numberformat in NLV Version and not as I hoped in en-us Format.

    So on my German System I get "#.##0,00 _€;[Rot]-#.##0,00 _€" instead of "#,##0.00_);[RAd](#,##0.00)"

    In C# with Excel Interop I do

    string en_format = "#,##0.00_);[Red](#,##0.00)";
    
    CultureInfo originalCulture = Thread.CurrentThread.CurrentCulture;
    CultureInfo originalUICulture = Thread.CurrentThread.CurrentUICulture;
    
    Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
    Thread.CurrentThread.CurrenUItCulture = new CultureInfo("en-US");
    
    MyRange.NumberFormat = en_format;
    
    Thread.CurrentThread.CurrentCulture = originalCulture;
    Thread.CurrentThread.CurrenUItCulture = originalUICulture
    


    So I thought I do something similar in C/C++ like this

    SetThreadLocale(1033);
    SetThreadUILanguage(1033);
    LCID curlcid = GetThreadLocale();
    LANGID curuilcid = GetThreadUILanguage();
    _variant_t vformat = this->xRange->NumberFormat;
    

    But although curlcid and uruilcid  has the value 1033 ( = en_us) Numberformat still returns the german Format.

    What must I do so, that  Numberformat  retrurns and accepts en_us Format ?

    Tia

      Hendrik Schmieder

    Tuesday, December 12, 2017 3:38 PM

All replies

  • Hello,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day! 

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 10:02 AM
  • Hi Hendrick,

    Thanks for posting this issue.

    This issue appears to be complex and due to its complexity your question falls into the paid support category which requires a more in-depth level of support. If the support  engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. 

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Regards,

    Ajay

    Thursday, January 4, 2018 10:35 PM
  • Sorry for the delay,

    it looks like this has nothing to do with the locales of the thread.

    In VBA I would use something like

    Dim nf As Variant
    Dim nfl As Variant
       
    nf = Application.ActiveSheet.Range("C7").NumberFormat
    nfl = Application.ActiveSheet.Range("C7").NumberFormatLocal
    


    nf gets the value "General" and nfl gets the value "Standard"

    For OLE Com automation this is unfortunatly different:

    #include <windows.h>
    
    //"libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52"
    #import "tlb\office\14\mso.dll" \
    	rename("RGB", "MSORGB") \
    	rename("DocumentProperties","MSODocumentProperties") \
    	rename("xlStackScale", "MSOxlStackScale")  \
    	rename("xlStack", "MSOxlStack") \
    	rename("xlStretch", "MSOxlStretch") \
    	rename("IAccessible", "MSOIAccessible")
    
    using namespace Office;
    
    //"libid:0002E157-0000-0000-C000-000000000046"
    #import "tlb\office\14\VBE6EXT.OLB"
    
    using namespace VBIDE;
    
    //"libid:00020813-0000-0000-C000-000000000046"
    #import "tlb\office\14\Excel.exe" \
    	rename("DialogBox", "ExcelDialogBox") \
    	rename("RGB", "ExcelRGB") \
    	rename("CopyFile", "ExcelCopyFile") \
    	rename("ReplaceText", "ExcelReplaceText") \
    	rename("xlStackScale", "ExcelxlStackScale")  \
    	rename("xlStack", "ExcelxlStack") \
    	rename("xlStretch", "ExcelxlStretch") \
    	rename("xlCommand", "ExcelxlCommand") \
    	rename("xlFunction", "ExcelxlFunction") \
    	rename("xlNotXLM", "ExcelxlNotXLM") \
    	rename("xlHierarchy", "ExcelxlHierarchy") \
    	rename("xlMeasure", "ExcelxlMeasure") \
    	rename("xlSet", "ExcelxlSet") \
    	rename("xlPrompt", "ExcelxlPrompt") \
    	rename("xlConstant", "ExcelxlConstant") \
    	rename("xlRange", "ExcelxlRange") \
    	no_auto_exclude
    _variant_t vformat = this->xRange->NumberFormat;
    _variant_t vformat_local = this->xRange->NumberFormatLocal;

    this->xRange is an Excel::RangePtr and this code is compiled with VS 2015.

    Here

    vformat and vformat_local has both the value "Standard" .

    I also tried with the files of an en_us Office 2010 installation.

    with best regards

      Hendrik Schmieder

     

    Monday, February 26, 2018 1:39 PM