3 Replies Latest reply: Aug 13, 2009 4:47 PM by Elizabeth Bamber RSS

    Is there a way to retreive Excel sheet name from QV?

      Hi:

      I have several Excel files, each one containing several sheets, but all have exactly the same structure. I've done something like this:

      for counter = 1 to 10

      Load a,b,c

      FROM [*.xls] (biff, embedded labels, table is @$(counter));

      next

      The problem is that I can only read a fixed number of sheets (in this case 10), which is not always true. So here are my questions:

      1.- Is there a way to use a "for each" clause refering to "each tab in each excel file" ? Using the Filelist will loop through all the Excel files, but I haven't found a way to loop through the tabs. The number of tabs is different in each file. I thought of using a big enough number in the "for" statement, but doing so will raise an exception when it doesn't find such a tab in the Excel File. Is there a TabExists() function I can use?

      2.- is there a function sort of "ExcelTabName()" that I can use in the Load list? The reason I need this is that I need to know which period the data belongs to (this is the tab name, example, 20090401).

       

      Thanks.

       

      Ruben