Libname xls for xlm: import the range

[SASdevAnneMarie]

Hello Experts,
Is it possible to access the range in xlsm file using Libname xls? 

My code is working:

options validvarname=any;

libname xl XLSX "\\XXX\TousIsin.xlsm";

data isin;
set xl.'VALEURS$B2:50'n;
run;

BUT when I replace B2:50 by range NAME:

data isin;
set xl.'VALEURS$test'n;
run;

My code doesn'y work. 😞

Thank you for your help!

[Oligolas]

Hi,

Seems like you've tested it.

AFAIK you'll need the libname excel

libname xlfile excel "your xlsm file" access=read;
proc print data=sashelp.vtable (keep=libname memname memtype);
where libname eq 'XLFILE';
run;

[SASdevAnneMarie]

Thank you,
It doesn't work:
ERROR: Connect: Classe non enregistrée
ERROR: Error in the LIBNAME statement.

I would like to import the range from xlsm file.

[Kurt_Bremser]

Did LIBNAME XLS (which you previously mentioned) actually work? This looks as if you do not have ACCESS to PC Files licensed or installed, or are running SAS on a non-Windows platform.

[SASdevAnneMarie]

Hello Kurt,

This code is working:



options validvarname=any;


libname xl XLSX "\\XXX\TousIsin.xlsm";

data isin;
set xl.'VALEURS$B2:50'n;
run;


BUT when I replace B2:50 by range NAME:



data isin;
set xl.'VALEURS$test'n;
run;



My code doesn'y work. 😞

[Kurt_Bremser]

Run this:

libname xl XLSX "\\XXX\TousIsin.xlsm";

proc sql;
select memname from dictionary.tables where libname = "XL";
quit;

and look at the dataset names you get in the library.

[SASdevAnneMarie]

Thank you,
I have the datasets with page name (the data by page), not by range.

[SASdevAnneMarie]

I mean that my ranges are named, I didn’t se tables by range.

Thank you

[Kurt_Bremser]

A little research revealed that the XLSX engine does not support named ranges, so you have (AFAIK) three options:

• use LIBNAME EXCEL. Works only on Windows, and SAS and Office (installed on the SAS host) must be of the same bitness (32/64)
• LIBNAME XLS, with the same restrictions regarding bitness (AFAIK)
• use the PC Files Server and the EXCELCS engine.

[Kurt_Bremser · Accepted solution]

I'll recap what we know so far:

• from the path, your SAS runs on Windows
• you have ACCESS to PC Files licensed, as LIBNAME XLSX works
• LIBNAME EXCEL or XLS gets the "class not registered" error

You either have a the necessary ACE or JET engine not available, or a bitness problem. See this SAS Note for possible remedies: https://support.sas.com/kb/63/750.html 

[Oligolas]

Hi,

it looks like you haven't SAS/ACCESS licensed.

You could try with proc Import but I think it won't work neither since it listed in the SAS/ACCESS Interface to PC Files but I would give it a try.

PROC IMPORT OUT= WORK.want 
            DATAFILE= "C:\fileWithNamedRanges.xlsm" 
            DBMS=EXCEL REPLACE;
            RANGE="range1"; 
            GETNAMES=YES;
            MIXED=YES;
            SCANTEXT=YES;
            USEDATE=YES;
            SCANTIME=YES;
RUN;

If none of the proposals work, maybe you can use some vba code to export a named ranges to a new xlsx workbook and Import the data.

I wasn't aware that what you posted before actually works, so thanks for this 🙂

libname xl XLSX "\\XXX\TousIsin.xlsm";
data isin;
set xl.'VALEURS$B2:50'n;
run;