Here is some follow up. Yes, I do plan to pass it along to Tech Support, as I think that the documentation on the XLSX engine should include a note about it. The only thing relevant that I found is this in the EXIST documentation, "If you use a sequential library, then the results of the EXIST function are undefined.". However, a) we don't get a blank or missing back from the function - we get 1, and b) I suppose that the XLSX engine could be considered sort of sequential, in that you cannot update or append to a tab, but you can jump around from tab to tab, so it is not completely sequential either. And if that is the argument for the function not working correctly, then again, it should be documented. As far as other techniques for verifying the existence of a tab on the spreadsheet, one more thing to note is that if the spreadsheet is open somewhere else in read/write mode, the libname assignment will be successful, but errors will result if you actually try to read from the spreadsheet (even with the access=readonly option specified). The OPEN function also fails. In addition, when this happens, the libname and memnames do not exist in dictionary.tables. So I have gone to a 2-step verification process. /* spreadsheet level error checking */ %let lib_available = 0; %let mytab_exist = 0; proc sql noprint; /* be sure spreadsheet can be read - it may be open */ select count(*) into :lib_available from dictionary.tables where libname eq "MYLIB" ; /* see if MYTAB tab exists */ select count(*) into :mytab_exist from dictionary.tables where libname eq "MYLIB" and memname eq "MYTAB " ; quit; %if &lib_available eq 0 %then %do; /* spreadsheet is not available - probably is open */ %put ERROR: spreadsheetname..xlsx cannot be read. It may be open. Macro will stop.; %goto _stop0_; %end; %else %if &mytab_exist eq 1 %then %do; /* whatever I want to do */ It's longer, but it works. Thanks for all the input. dd
... View more
