2 Replies Latest reply: Oct 14, 2014 10:07 AM by Colleen Cross RSS

    How to take Excel sheets names ?

    Ivan Gadzhonov

      Hi all,

       

      Could you please tell me how to take the names of excel sheets - is there any function .... ?

       

      Thanks in advance!

        • How to take Excel sheets names ?
          Gordon Savage

          Have a look at the following - it loops through all .xls in a folder and then sets a variable to the current sheetname, which should give you some ideas:

           

          SET vPath = 'C:\pathtodata\';

          FOR each vFile in filelist ('$(vPath)'&'*.xls')

          CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$(vFile)';Extended Properties="Excel 8.0;"];

          [Excel Sheets]:
          // Get description of the tables in the ODBC datasource - with Excel each sheet is a table
          SQLtables;

          DISCONNECT;

          // TABLE_NAME contains the sheet name - loop through this set
          FOR i = 0 to NoOfRows('Excel Sheets')-1

          // TABLE_NAMEs that contain spaces will be enclosed in single quotes. The purgeChar function will remove any quotes.
          LET vSheet = purgeChar(peek('TABLE_NAME', i, 'Excel Sheets'), chr(39));

          NEXT ;  // (sheet)

          DROP TABLE [Excel Sheets];

          NEXT vFile

           

          Regards,

           

          Gordon