Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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')
Take a look here: Loading Multiple Excel Sheets Dynamically along with file name and sheet name
- Marcus
I have checked that script, but here I'am not taking excel files from sql tables.
Is there any other way to do?
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