02-21-2013 07:44 AM
I use the code below to access lots of Excel files that we receive from our subsidiaries.
libname myxls oledb access=readonly provider="Microsoft.ACE.OLEDB.12.0"
properties=("data source"="&fullname" "Mode"="Read")
Excel files are in .xlsb format.
This code works fine.
But for some files i get the error:
ERROR: Error trying to establish connection: Unable to Initialize: External table is not in the expected format.
ERROR: Error in the LIBNAME statement.
These problem Excel files are opening in Excel without any errors.
I tried to open file in Excel and save it as another copy. But it does not help.
The only way i found :
1. Open file in Excel
2. Copy and paste only values (paste special) to another file.
3. Save this new file and then import.
I hope someone knows how to solve this problem. May be i missed some option in the libname statement.
Files are hundreds and it is very difficult manually copy/paste data.
Thanks in advance.
02-22-2013 02:00 AM
I find out that error files have protected structure enabled with a password.
I googled some information about this protection. Programmers (non-SAS) say that it is not possible to make an OLEDB access to protected Excel files.
I can not use DDE unfortunatly. I am running EG.
02-22-2013 09:51 AM
I'm not familiar with using the OLDB engine but I use the Excel engine quite a bit. I don't work with password-protected spreadsheets and I don't work with .xlsb files but I _think_ it has an option that allows you to pass a password to Excel.
Check the documentation on the Excel libname engine. You'll likely have to rework your code to use the Excel engine but it might be a solution.
Thanks for the tip about password-protected files throwing this error.