none
Excel Workbook loose some cell formating when opened and saved with late binding RRS feed

  • Question

  • Hi,

    when I open and save a workbook using late binding, the workbook loose some cell formating. The cell format of "accounting" changes for example to "custom". This does not happen, if I open the workbook with Excel directly or via windows explorer.

    Any idea how to avoid this?

    The code can be used to reproduce the behaviour.

    Tested with Excel 2016, Excel 2013

    Dim filename As String = "D:\weg\_tmp\Mappe1.xlsx"
    Dim excelObj As Object = CreateObject("EXCEL.APPLICATION")
    
    excelObj.Workbooks.Open(FileName:=filename, ReadOnly:=False)
    excelObj.Visible = True
    excelObj.ActiveWorkbook.Activate()
    excelObj.ActiveWorkbook.save
    excelObj.ActiveWorkbook.close
    excelObj.quit

    Thanks,

    Daniel

    Friday, November 24, 2017 4:38 PM

All replies

  • Daniel,
    re: funny acting code

    What code language are you using?
    The code does not compile in XL2010 with VBA.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Saturday, November 25, 2017 5:04 AM
  • Hello Daniel,

    I run the code in 15.0.4981.1001 and Version 1711 Build 8730.2090. The cell format is not changed. 

    Do you get the same result if you use early binding? Do you test the code with a new created document?

    Please update your Office to latest version, create a new file and test again. Please unload all add-ins when test.  If the issue only occurs for one document, please share the document here. 

    Regards,

    Celeste


    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.

    Saturday, November 25, 2017 5:38 AM
    Moderator
  • Hi Celeste,

    It does not matter if early or late binding. The document is completly new created.

    I installed a completly new Excel (in this case Microsoft Professional Office Plus 2013) on a completly new Windows 7 and could make this happen.

    Steps for reproduction:

    1. Create empty Workbook

    2. Fill first column with 1, 2, 3,4 each row a value

    3. Select accounting for this column as format (in the cell formating dialog of the column)

    4. save the workbook as xlsx

    5. run the code above

    6. open the workbook with excel

    7. now you see that row format of the column changed from "accounting" to "custom" (you have to open the cell formating dialog for that, because it looks ok without open the dialog)

    I use a german version of excel, that might be for interest.

    Tuesday, December 5, 2017 4:21 PM
  • It is Vb.NET.
    Tuesday, December 5, 2017 4:22 PM
  • Hello,

    I could not reproduce your issue. 

    I test with Excel 2013 15.0.4981.1001 64bit C2R version. I also install German language package, the format is still "accounting" . What is your version number? 

    >>you have to open the cell formating dialog for that, because it looks ok without open the dialog

    Is the value of Format Cells dialog same as the value in Home tab -> Number group?

    Do you change currency symbol? 

    Please go to control panel -> Change date, time, or number formats -> Formats -> Additional Settings -> Currency to check the currency symbol. 

    If the currency symbol in your sheet is different from system settings, cell format would be "Custom". 

     

    Regards,

    Celeste


    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.

    • Marked as answer by schlumpfger_1 Friday, December 8, 2017 11:53 AM
    • Unmarked as answer by schlumpfger_1 Friday, December 8, 2017 11:53 AM
    Wednesday, December 6, 2017 2:38 AM
    Moderator
  • Dear Celeste,

    please look at the full mht file http://hecht33.de/problem.zip.

    The currency symbol was not changed and is the same in sheet and system settings.

    Thanks,

    Daniel

    Friday, December 8, 2017 12:06 PM
  • Hello,

    You are using an old version of Office. According to Update history for Office 2013,  The most current version of Office 2013 is 15.0.4981.1001, which was released on November 14, 2017.

    I suggest you install Office 2013 Service Pack1 and then install Office updates with Windows Update. Please visit 

    Description of Microsoft Office 2013 Service Pack 1 (SP1)

    Update Office with Microsoft Update

    Regards,

    Celeste


    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.

    Monday, December 11, 2017 2:07 AM
    Moderator
  • Celeste,

    thanks for reply.

    I updated everything and took a new try. The behaviour did not change.

    Please look at the new full mht file http://hecht33.de/problem.zip

    Any other idea?

    Best regards,

    Daniel

    Thursday, December 14, 2017 12:56 PM
  • Hello,

    The mht file is old one. Could you please create three files and share it here? I want to check Open XML formats of them.  

    1.Please Create three new workbooks A,B,C and change cell format of column A into "Accounting" for them and save.

    2.Run your project on file B and C.

    3.Open file B in Excel to check if the cell format has been changed and do not open file C in your Excel.

    4.Upload the three files into OneDrive and share the link here.

    Regards,

    Celeste


    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.

    Friday, December 15, 2017 1:50 AM
    Moderator
  • Hi,

    everything done.

    https://1drv.ms/f/s!AotqLpHLGNdSgRGhwGpqJ_1iWLtf

    Thanks,

    Daniel

    Friday, December 15, 2017 11:11 AM
  • Hello Daniel,

    It seems your application would change the format code

    _-* #,##0.00 "€"_-;-* #,##0.00 "€"_-;_-* "-"?? "€"_-;_-@_-

    into

    _("€"* #,##0.00_);_("€"* (#,##0.00);_("€"* "-"??_);_(@_)

    I test with your file and i still could not reproduce the issue.  

    You may comment your code line by line to check which method causes the issue.  Besides, I suggest you contact Microsoft professional support so that the engineers can work closely with you to troubleshoot this issue. 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. 

    Regards,

    Celeste


    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.

    Monday, December 18, 2017 8:12 AM
    Moderator
  • Hello Celeste,

    thanks for you reply. Below you see the complete code of my Application. As you see, nothing happens in there. The funny thing, is I can reproduce the problem on each machine I run the program below. 

    You find the full project here: https://1drv.ms/u/s!AotqLpHLGNdSgR-H_VB47S1xuL6X

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            If Not String.IsNullOrWhiteSpace(TextBoxFile.Text) Then
    
                Dim filename As String = TextBoxFile.Text
                'Dim excelObj As Object = CreateObject("EXCEL.APPLICATION")
                Dim excelObj As New Excel.Application
                excelObj.Workbooks.Open(Filename:=filename, ReadOnly:=False)
                excelObj.Visible = True
                excelObj.ActiveWorkbook.Activate()
                excelObj.ActiveWorkbook.Save()
                excelObj.ActiveWorkbook.Close()
                excelObj.Quit()
    
                MsgBox("Done")
    
            End If
    
    
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            Dim ofd As New OpenFileDialog
            If ofd.ShowDialog = DialogResult.OK Then
                TextBoxFile.Text = ofd.FileName
            End If
    
        End Sub
    End Class

    Maybe someone has an idea.

    Thanks,

    Daniel

    Monday, December 18, 2017 11:08 AM
  • Hello,

    Could you get the correct number format if you open the workbook using the code above?

    You may reset the number format programmactially.

    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Columns
    c.NumberFormat = c.NumberFormat
    Next

    Regards,

    Celeste


    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.

    Wednesday, December 20, 2017 2:47 AM
    Moderator