Hello SAS-Users.
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")
provider_string="Excel 12.0;ReadOnly=True;HDR=no;IMEX=1;"
;
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.
Oleg.
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.
Oleg.
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.
Good Luck.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.