Opening Excel 2007 Binary Files (.xlsb) via OleDb ACE

Discussion in 'Excel Errors' started by Rafael, Dec 23, 2008.

  1. Rafael

    Rafael Guest

    Hi Fellows,

    I found the Excel 2007 Binary format (with extension .xlsb) perfectly
    suitable for my needs, since it's fast to load and very compact. I deliver a
    bunch of reports in Excel that carries a lot of data, and those reports are
    actually being loaded with a IS package.

    So I assumed the conversion to this very format, read documentation on
    Access Ole DB Provider 12, where it stands that xlsb is supported by this
    provider, I converted the files in Excel, and then when trying to change then
    in the packages (just by changing the path of the file by adding the "b"
    suffix) I got the following error message:


    "Test connection failed because of an error in initializing provider. This
    file was created in a previous beta version of Excel 2007. Open the file
    with Excel 2007 to save it to the most recent version of the Excel 2007 file
    format before opening the file in Access 2007."

    Well, I thought that it could be some problem with the conversion of the
    file, so I picked up a brand new and filled with some information. Tried
    again, same error!!

    Then I tried the approach with ".udl" files, same error!
    Then I tried to test in different machines:
    - My own (Windows Vista SP1)
    - My colleagues (Windows XP SP2)
    - The IS Server (Windows Server 2003 x64 SP2)

    And the error persist. I found it so easy to reproduce the error, that I'm
    bit surprised that I didn't found anything about it by googleing around!

    Can somebody help me?

    Thanks in advance,
    Rafael
     
    Rafael, Dec 23, 2008
    #1
    1. Advertisements

  2. Rafael

    Allan D Guest

    Hi Rafael.

    I get (i think) the same error:


    A first chance exception of type 'System.Data.OleDb.OleDbException' occurred
    in System.Data.dll
    The Message in the Exception is:

    "This file was created in a previous beta version of Excel 2007. Open the
    file with Excel 2007 to save it to the most recent version of the Excel 2007
    file format before opening the file in Access 2007."

    I happens when i try to open the connection

    Using conn As OleDbConnection = New OleDbConnection(connStr)
    conn.Open()
    ...

    My connection string is:
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Myfile.xlsb;Extended
    Properties="Excel 12.0;HDR=NO;IMEX=1";'


    I compile to .NET Framework 3.5 with MS Visual Studio 2008 SP1
    I have never had any Beate versions of Office 2007 installed. Only Office
    2007 Pro Sp1 plus all what comes from Windows Update.
    Earlier i had MS Office 2000 Pro but then updated it to Office 2007 Pro.

    Best Regards
    Allan D
     
    Allan D, Dec 31, 2008
    #2
    1. Advertisements

  3. Rafael

    Allan D Guest

    Hi again,

    I have now tried it on another Windows XP Pro machine.

    A PC totally witout any MS Office.
    I got the message: Der 'Microsoft.ACE.OLEDB.12.0'-Provider ist nicht auf
    dem lokalen Computer registriert.
    (German Windows)
    Then I installed the AccessDatabaseEngine.exe v 12.0.4518.1014 from
    http://www.microsoft.com/downloads

    Quick Details :
    File Name: AccessDatabaseEngine.exe
    Version: 2
    Date Published: 10/3/2008
    Language: English
    Download Size: 25.3 MB

    And then i got the following error message when tring to open the OleDB
    Connection to the xslb file:

    "This file was created in a previous beta version of Excel 2007. Open the
    file with Excel 2007 to save it to the most recent version of the Excel 2007
    file format before opening the file in Access 2007."


    I have also tried to writen the xslb file totally from new on a 3th PC with
    MS Office Pro 2007. - Same result.

    Can anybody help?

    Best Regards,
    Allan D
     
    Allan D, Jan 3, 2009
    #3
  4. Rafael

    Rafael Guest

    Hi Allan,

    I'm glad you've replied. This looks like a bug.
    Are you using Office 2007 with SP1?

    I don't see how a problem so easy to be reproduced doesn't have any reply on
    specialists part. Can anybody help?

    Thanks,
    Rafa
     
    Rafael, Jan 6, 2009
    #4
  5. Rafael

    Allan D Guest

    Hi Rafa,

    Yes i am using MS Office Pro 2007 with SP1 and a long row of updates from
    Microsoft Update.

    So i am pretty sure you are right: Our Problem is a bug i MS Office 2007 Pro
    with SP1.

    And yes it would be good with a specialist answer .... Please help

    Best Regards,
    Allan D
     
    Allan D, Jan 6, 2009
    #5
  6. Hi Allan,

    Please try if it does work when you save the file as any other Excel 2007 type
    (xlsm, xlsx or even xls). xlsb sometimes causes a problem.

    Regards,

    Jan Karel Pieterse
    Excel MVP
    http://www.jkp-ads.com
    Member of:
    Professional Office Developer Association
    www.proofficedev.com
     
    Jan Karel Pieterse, Jan 7, 2009
    #6
  7. Rafael

    Allan D Guest

    Hi Jan,

    My job was to make an light-weight solution to read xls,xlsx,xlsm and xlsb
    files.

    It works with xls, xlsx, xlsm, but not xlsb. The error-description is that
    i use a Beta-Version of Excel 2007, but i am actually using the most
    updateded version of Excel 2007 with SP1. ... What can i do?

    Best Regards,
    Allan
     
    Allan D, Jan 7, 2009
    #7
  8. Hi Allan,

    Exactly what do you do when you write the Excel files? Just changing the
    extension does not convince Excel to write an xlsb file, you need to specify
    the file type explicitly.

    Regards,

    Jan Karel Pieterse
    Excel MVP
    http://www.jkp-ads.com
    Member of:
    Professional Office Developer Association
    www.proofficedev.com
     
    Jan Karel Pieterse, Jan 7, 2009
    #8
  9. Rafael

    Allan D Guest

    Hi Jan,

    Thank you for answering on this thread.

    It was Rafael who wrote.
    But Rafael and I get the same error-message.

    When i save an Excel file i use the mail-menu item called Save as... and
    then submenu item "Excel Binary Workbook".
    Then the Save as type, will be preselected to "Excel Binary Workbook
    (*.xlsb), i choose a filename, and press the button "Save"

    The same and other machines containing MS Office Excel 2007 can then read
    the .xslb file without any trouble.

    But not via the OleDbConnection. I get the Exception with only a few lines
    of code:


    Imports System.Data.OleDb

    Public Sub TestXlsb()
    Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=D:\Myfile.xlsb;" & _
    "Extended Properties=""Excel
    12.0;HDR=NO;IMEX=1"";"

    Using conn As OleDbConnection = New OleDbConnection(connStr)
    conn.Open() 'this line throw an exception.
    End Using
    End Sub


    ...... to me this looks like a well-defined MS Office 2007 Bug. Don't you
    think so?


    Best Regards,
    Allan
     
    Allan D, Jan 7, 2009
    #9
  10. Rafael

    Allan D Guest

    sorry, not "the mail-menu ", but "the main-menu".
    Allan
     
    Allan D, Jan 7, 2009
    #10
  11. Hi Allan,
    Sounds like it indeed.

    Regards,

    Jan Karel Pieterse
    Excel MVP
    http://www.jkp-ads.com
    Member of:
    Professional Office Developer Association
    www.proofficedev.com
     
    Jan Karel Pieterse, Jan 7, 2009
    #11
  12. Rafael

    Jeff Overton Guest

    I am experiencing similar symptoms, which occur when adding a workbook
    connection to an Excel file of any of the following formats: .xlsb, .xlsm, or
    ..xlsx. I receive the same message as described in this thread. I am using
    Excel 2007 (12.0.6331.5000 SP1 MSO 12.0.633.5000) and using the Microsoft
    Office 12.0 Access Database Engine OLE DB Provider.

    I am able to successfully establish a workbook connection to Excel files of
    format .xls. However, I cannot successfully use this data because it hangs
    when defining a pivotable that uses this connection ( connection is to a
    table defined as $a$7:$y$39252). So i thought I could avoid this by saving as
    ..xlsb. However, I cannot successfully establish a connection to the other
    file when it is any any other format. I tried .XLSX, .XLSB and .XLSM.
     
    Jeff Overton, Jan 7, 2009
    #12
  13. Hi Jeff,
    What steps do you follow to make the connection (I want to duplicate this)?

    Regards,

    Jan Karel Pieterse
    Excel MVP
    http://www.jkp-ads.com
    Member of:
    Professional Office Developer Association
    www.proofficedev.com
     
    Jan Karel Pieterse, Jan 8, 2009
    #13
  14. Rafael

    Jeff Overton Guest

    1. Open a new worksheet using Excel 2007 (12.0.6331.5000 SP1 MSO 12.0.633.5000)

    2. Setup connection -- Data --> Connections --> Add

    3. In window entitled "Existing Connections" select button "Brows for More".
    Navigate to location of the data source Excel file .xlsx (Microsoft Office
    Excel Worksheet). Window entitled "Data Link Properties" appears (this is the
    first indication that the connection will not work. Successfull connections
    do not display this window. Rather they show a list of the tables which are
    available.


    4. The following are the values on the tabs for the "Data Link Properties"
    dialogue
    Provider tab -- Microsoft Office 12.0 Access Database Engine OLE DB Provider
    is highlighted

    Connection tab
    Under "1. Enter the data source and/or location of the data:"
    Data Source: C:\Documents and Settings\myname\My
    Documents\data\December_Data.xlsx
    Location (grey'd out)

    Under "2.0 Enter information to log on to the server:"
    Use Windows NT Integrated security (Grey'd out)
    Use a specif user name and password:
    User name = Admin
    Password = Grey'd out
    Blank password (checked) Allow saving password (checked)

    When I test connection i get one of two messages. For this test i receive
    the following message:
    "Test Connection failed because an error in initializing provider. External
    table is not in the expected format".

    Advanced Tab
    Network settings (grey'd out)
    Outher
    Connection time out seconds (grey'd out)
    Access permissions = Share Deny Write checked

    All tab
    Name = value

    Jet OLEDB:Global Bulk Transactions = 1
    Jet OLEDB:Global Partial Bulk Ops = 2
    Jet OLEDB:New Database Password = empty
    Jet OLEDB:Registry Path = empty
    Jet OLEDB:SFP = False
    Jet OLEDB:Support Complex Data = False
    Jet OLEDB:System database = empty
    Locale Identifier = 1033
    Mode = Share Deny Write
    Password = empty
    Persist Security Info = True
    User ID = Admin


    5. Upon selecting "OK" for the Data Link Properties dialogue, a brief pause
    ensues and a new dialogue entitled "Please Enter Microsoft Access Database
    Engine OLE DB Initialization Information".

    The valid responses are to modify any of the following fields, which I do
    not and either select "OK" or "Cancel". Here are the values pre-populated in
    the dialogue

    Data Source:
    User Name: Admin
    Password:
    Provider String: HDR=YES;
    Open Mode: DB_MODE_READWRITE

    6. I select "OK". After a few seconds the dialogue disappears and the
    connection fails to be created in the "Workbook Connections" dialogue.

    7. This is the end of the process.
     
    Jeff Overton, Jan 8, 2009
    #14
  15. Hi Jeff,
    Looks like the issue is fixed with SP2 (available in a couple of months).

    Regards,

    Jan Karel Pieterse
    Excel MVP
    http://www.jkp-ads.com
    Member of:
    Professional Office Developer Association
    www.proofficedev.com
     
    Jan Karel Pieterse, Jan 8, 2009
    #15
  16. Rafael

    Jeff Overton Guest

    Any chance there is a patch or pre-release version available? If not, do you
    know the month when SP2 will be available?

    Thank you for your help with this!
     
    Jeff Overton, Jan 8, 2009
    #16
  17. Hi Jeff,
    Office SP2 is not a public beta, I'm sorry.

    The most exact information on a release date says mid 2009, so that is of little
    help too I'm afraid.

    Regards,

    Jan Karel Pieterse
    Excel MVP
    http://www.jkp-ads.com
    Member of:
    Professional Office Developer Association
    www.proofficedev.com
     
    Jan Karel Pieterse, Jan 9, 2009
    #17
  18. This easily repeated just save an excel spreadsheet in a xlsm, xlsx, or xlsb
    and try to open the file with Access 2007.

    I thought the same when 2007 came out and I have many programs that read
    excel spreadsheets exclusively with xlsb. Now because of a fix they do not
    work. Now I can't wait two months for this fix, I will have to change all my
    code back to xls.
     
    HigherSpirits, Jan 14, 2009
    #18
  19. Rafael

    Ambuj Mondal

    Joined:
    Jan 30, 2018
    Messages:
    1
    Likes Received:
    0
    I was using Office 2017 but still got this issue suddenly. After lot of research, I could not find any justified solution. So I saved my xlsb file in compatibility mode and it worked.
     
    Ambuj Mondal, Jan 30, 2018
    #19
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.