SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Excel libname engine unable to read some range names

Reply
Occasional Contributor
Posts: 10

Excel libname engine unable to read some range names

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Excel libname engine unable to read some range names

Suggest you contact SAS technical support and open a track/issue with your challenge for guidance.

Scott Barry
SBBWorks, Inc.
Occasional Contributor
Posts: 10

Re: Excel libname engine unable to read some range names

Thanks Scott, I have submitted the issue now - I'll update this thread with any suggestions that come back.
Post a Question
Discussion Stats
  • 2 replies
  • 147 views
  • 0 likes
  • 2 in conversation