Load tables from different Excel sheets with different sheet names?

[#1]

Hi,

Here is the problem I am facing:

I have to load multiple excel files from the same folder. So I am using the " *.xlsx " function in my script -

SALES:

LOAD [Sales Order number],

     [Excise Invoice No],

     [Excise Date],

     [Quantity Delivered],

     [Gross Sales Amount],

FROM

(ooxml, embedded labels, header is 1 lines, table is [Sales Drytech 2013-2014]);

The problem is that the table name (ie the sheet name) of all the excel files is different.

So, i have to manually change the sheet in each of the excel files individually.

Note: The sheet name is automatically named as name of the Excel file.

Is there any way where the different table name doesnt need to be changed?

[Gysbert_Wassenaar]

See this discussion: Loading from multiple Excel files and multiple sheets

[#3]

I Dont  know how to respond  to you  using my user name. U helped me with this expression a Month ago and it wroked fine

=Count({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=Max(CloseAdmin_Year)'},[Line of Business]={'HCS','PSL'}>} DISTINCT [Occurence No])

But ir doesnt work for Max(Year)-1. Any Suggestion.

Please reply to (Ray HDKJSH) with any suggestions how can I make it work for Previous Year

[#4]

    use this in Edit Script , you may get the solution

For i = 0 To NoOfRow('Sheets')

          Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

          ... do something with the file and sheet name, eg

          LOAD ....

          From [$(vFile)]

          (ooxml, no labels, table is [$(zSheet)]);