
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
loop thru excel worksheet with specific name
Hi Everyone
I am finding lot of threads that shows how to loop thru all worksheets in a workbook. However If i have a workbook with 15 sheets but I only want to loop thru all the sheets whose name end in *mod. how can I do that.
please share example.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
Set vPath = C:\Test;
FOR Each File in FileList('$(vPath)\*.xls')
ODBC CONNECT32 TO [Excel Files;DBQ=$(File)];
TabTmp:
SQLTABLES;
Tab:
LOAD
RowNo() as Line,
TABLE_CAT as X,
If(Right(TABLE_CAT, 3) like 'xls', TABLE_NAME, If(Right(TABLE_NAME, 1) like '$', Left(TABLE_NAME, Len(TABLE_NAME)-1), TABLE_NAME)) as Y,
If(Right(TABLE_CAT, 3) like 'xls', 'biff', 'ooxml') as Z
Resident TabTmp
Where Right(PurgeChar(TABLE_NAME, '$'), 3) like 'mod';
DROP Table TabTmp;
NEXT File
For vLine = 0 to Peek('Line', -1, 'Tab')-1 step 1
vX = Peek('X', $(vLine), 'Tab');
vY = Peek('Y', $(vLine), 'Tab');
vZ = '('&Peek('Z', $(vLine), 'Tab')&', embedded labels, table is $(vY))';
[$(vY)]:
LOAD *
FROM
[$(vX)]
$(vZ);
NEXT vLine
DROP Table Tab;

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As you say there are many examples of looping through Excel sheets. You just need to find one that works for you and add two lines, to skip non 'mod' ones.
if lower(right(vTabName)) = 'mod' then
[... load sheet ...]
end if
Personally when I am loading from multiple tabs I prefer to add a metadata tab to the start of the spreadsheet listing the names of all tabs that should be loaded. This can then be enumerated around with a PEEK statement. This means if someone adds a random sheet it doesn't get pulled in by mistake.
A step I would add to Felipe's code is after retrieving File check for the ~ character. This is a system file created when a user has a spreadsheet open, and this can be picked up by a *.xls. Simply add this check after retrieving each file name:
if index(FILE, '~') = 0 then
This will then skip any files that are system generated.
Hope that helps,
Steve
