none
ACE.OLEDB.12.0 sets file property 'ShareDoc' = 'No' in xlsx file RRS feed

  • Question

  • I am using Microsoft.ACE.OLEDB.12.0 to read/write xlsx files

    ... the excel files are read OK and written/created OK ... BUT, when I write to the file after closing I cannot re-open using Excel 2013.

    I get the error ' ... cannot open file because the file format or file extension is not valid'.

    I know this error message is incorrect! the extension and format are valid; the problem is that the property 'ShareDoc' is set to No. If I open the excel document using some other program ... like LibreOffice and change the property 'ShareDoc' to 'Yes', then Excel 2013 will open the document!

    The question is ... why is this property being set? and how can I prevent this?

    It is useless to create a document that cannot be opened!

    or is there a setting in Excel 2013 that will at least allow me to open documents with ShareDoc = 'No' ?


    Tracey

    Wednesday, March 22, 2017 4:40 PM

Answers

  • I think I found the issue ... I do not believe it is the ShareDoc property ...

    I updated the connection string from:

    Extended Properties=Excel 12.0;

    to

    Extended Properties=Excel 12.0 Xml;

    Best guess is that the file was being save in a binary format?


    Tracey

    • Marked as answer by Tracey Macias Monday, March 27, 2017 2:21 PM
    Wednesday, March 22, 2017 8:34 PM
  • Hello Tracey,

    >>the file was being save in a binary format?

    Yes.  For more information, please visit https://www.connectionstrings.com/excel-2013/ and you would find:

    Xlsx files

    Connect to Excel 2007 (and later) files with the Xlsx file extension. That is the Office Open XML format with macros disabled.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";

    Xlsb files

    Connect to Excel 2007 (and later) files with the Xlsb file extension. That is the Office Open XML format saved in a binary format. I e the structure is similar but it's not saved in a text readable format as the Xlsx files and can improve performance if the file contains a lot of data.

    Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;
    Extended Properties="Excel 12.0;HDR=YES";

    You can also use this connection string to connect to older 97-2003 Excel workbooks.

    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.

    Thursday, March 23, 2017 5:42 AM
    Moderator

All replies

  • I think I found the issue ... I do not believe it is the ShareDoc property ...

    I updated the connection string from:

    Extended Properties=Excel 12.0;

    to

    Extended Properties=Excel 12.0 Xml;

    Best guess is that the file was being save in a binary format?


    Tracey

    • Marked as answer by Tracey Macias Monday, March 27, 2017 2:21 PM
    Wednesday, March 22, 2017 8:34 PM
  • Hello Tracey,

    >>the file was being save in a binary format?

    Yes.  For more information, please visit https://www.connectionstrings.com/excel-2013/ and you would find:

    Xlsx files

    Connect to Excel 2007 (and later) files with the Xlsx file extension. That is the Office Open XML format with macros disabled.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";

    Xlsb files

    Connect to Excel 2007 (and later) files with the Xlsb file extension. That is the Office Open XML format saved in a binary format. I e the structure is similar but it's not saved in a text readable format as the Xlsx files and can improve performance if the file contains a lot of data.

    Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;
    Extended Properties="Excel 12.0;HDR=YES";

    You can also use this connection string to connect to older 97-2003 Excel workbooks.

    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.

    Thursday, March 23, 2017 5:42 AM
    Moderator