If there are multiple worksheets for each month, How to select the worksheet based on the year month in the filename.
For instance, my input file National Cornerstone 2009 09.xls,has worksheets from Jan to sep with naming convention as Jan 2009 to Sep 2009.How to select Sep 2009 worksheet?
and for National Cornerstone 2009 09.xls, how to select Oct 2009 worksheet?
To give heads up I use the following code:
connect to excel (path="&inpt.\National Cornerstone 2009 09.xls");
create table tabnaes as
select table_name from connection to excel (jet::tables);
The table tabnaes will have all the worksheet names.The only way is to hardcode the woksheet names to delete as shown below.
delete from tabnaes where table_name like '%FEIBA%' or table_name like '%$%';
select table_name into:wksht from tabnaes;
%let wksht = %trim(&wksht);
because both papers have good information about naming conventions.
I do not know whether the other methods (JET/ ODBC/OLE-DB) use the same sheetname methods as the LIBNAME engine. This might be a question for Tech Support, if you do not get any other suggestions from forum participants.