Loading dynamic sheet names

[Anonymous]

Hi Community,

I have below script which works fine to load excel files from folders and subfolders. But now i want to load sheets of all excel files which are dynamic. Any help on this?

Sub DoDir(Root)

for each ext in 'xlsx'

for each File in FileList(Root&'\*.'&ext)

Table:

load

from ['$(File)'](ooxml,no labels);

Next File

Next ext

    for Each Dir in DirList(Root&'\*')

call DoDir(Dir)

  next Dir

end sub

call DoDir('lib://pathto/Funds')

[marcus_sommer]

Take a look here: Loading Multiple Excel Sheets Dynamically along with file name and sheet name

- Marcus

[Anonymous]

I have checked that script, but here I'am not taking excel files from sql tables.

Is there any other way to do?

[marcus_sommer]

If you know the sheetnames you could just add another loop which runs through this list. Thinkable are also external macro-batches which create a table with those sheetnames and which could be used within a loop. If this isn't very practically you will the need the sql-approach from the above link which is not very complex to apply - maybe in this way:

Sub DoDir(Root)

for each ext in 'xlsx'

     for each File in FileList(Root&'\*.'&ext)

          ODBC CONNECT32 TO [Excel Files;DBQ=$(File)];
          tables: SQLtables; DISCONNECT;

          FOR i = 0 to NoOfRows('tables')-1

               LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
               Table: load from ['$(File)'] (ooxml, no labels, table is [$(sheetName)]);

          next

     Next File

Next ext

for Each Dir in DirList(Root&'\*')

     call DoDir(Dir)

next Dir

end sub

call DoDir('lib://pathto/Funds')

- Marcus