Hello
I have a spreadsheet which has a dynamic named range defined in it, i.e.
rngDef.RefersTo = "=OFFSET('Data Entry'!$B$5,1,0,COUNTA('Data Entry'!$B:$B)-4,'Data Entry'!setNumCols)".
I would like to read the contents of this range into a SAS dataset, and I tried the following code:
libname returnWB EXCEL "&path.\&adhocref.\excel\XXXXXX.xls" USEDATE=NO ;
data sasdata.STData;
set returnWB.rngDef;
run;
libname returnWB clear;
The libname is assigned OK, but reading the range causes the following error to appear:
16 data sasdata.STData;
17 set returnWB.rngDef;
ERROR: File RETURNWB.rngDef.DATA does not exist.
18 run;
If I redefine the name as static, i.e. as a rectangular range $B$6:$L$278, the error still occurs. There is protection applied at both the workbook and sheet level, but the error still occurs after removing this. The name is defined at the workbook level.
The one option I have which does appear to work is to copy the data in the range into a new range in a new workbook using VBA and read from there using the libname engine, however this is not really satisfactory long-term - as I expect to process many of these files each month I'd like to try and make this as painless as possible, so without having an extra step in there. Does anyone have any ideas?
Thanks
Geoff
