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

[Rafael]

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

 

[Allan D]

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]

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

 

[Rafael]

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

 

[Allan D]

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]

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]

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]

sorry, not "the mail-menu ", but "the main-menu".
Allan

 

[Jeff Overton]

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]

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]

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!

 

[HigherSpirits]

Hi Jeff,


Looks like the issue is fixed with SP2 (available in a couple of months).

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.

 

[Ambuj Mondal]

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.