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. ... View more

Hello SAS-users. I use the code below to import a specific range from XLS sheet. The first row of the range becomes the variables names. I want the automatic variables names (similarly option GETNAMES=NO in PROC IMPORT). Is it possible with OLEDB? libname boo oledb init_string="provider=Microsoft.ACE.OLEDB.12.0; data source=\\xxxxx.xxxx.xxx.xx\UOLib\test\SNB0_monthly_mar12.xls; extended properties=Excel 8.0"; data new; set boo.'M-7$A6:H3000'n; run; libname boo clear; Regards, Oleg. ... View more