Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bismart
Creator
Creator

Load all sheets in all Excel documents

I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.

The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2

while Doc2 has Sheet1/Sheet2/Sheet3 etc.

I need to create a table of Doc name and Sheets belonging to that Doc.

Then I want to load the sheets from their associated docs in a loop statement.

Anyone any ideas?

Thanks

1 Solution

Accepted Solutions
vgutkovsky
Master II
Master II

You might be able to do this is with a script macro. But a more elegant way would be to turn off errors by setting ErrorMode=0 and then doing a loop with the assumption that all workbooks have the number of sheets that the largest workbook has. With error reporting turned off, the script should be able to just proceed to the next load statement if a sheet is missing.

Regards,

View solution in original post

66 Replies
vgutkovsky
Master II
Master II

You might be able to do this is with a script macro. But a more elegant way would be to turn off errors by setting ErrorMode=0 and then doing a loop with the assumption that all workbooks have the number of sheets that the largest workbook has. With error reporting turned off, the script should be able to just proceed to the next load statement if a sheet is missing.

Regards,

bismart
Creator
Creator
Author

Anybody got some sample script I could use???

Cheers Vlad... but sample script would be appreciated

vgutkovsky
Master II
Master II

Sure, it would look something like this:


SET ErrorMode = 0;
for a=1 to 3 //in this example, 3 is the number of sheets your maximum workbook has
LOAD * FROM
*.xls (biff, embedded labels, table is Sheet$(a)$);
next
SET ErrorMode = 1;


You can then define different table structures, etc. Or if the fields are identical across all sheets, it will autoconcatenate for you.

Cheers,

bismart
Creator
Creator
Author

Thanks Vlad

Worked like a charm

I didn't know about the errormode setting so that got me over the problem

Thanks again

Dave

Not applicable

Hi Vlad,

           I have tried your above condition in my application but its not working.

Below condition i used in my script :

SET ErrorMode = 0;

for a=1 to 3

LOAD * FROM

C:\Users\napo\Desktop\Excel Data\*.xlsx (ooxml, embedded labels, table is Sheet$(a)$);

next

SET ErrorMode = 1;

vgutkovsky
Master II
Master II

Are the worksheets in your workbook named Sheet1, Sheet2, etc?

Not applicable

Yes, Sheet1 , Sheet2 .....

rohit214
Creator III
Creator III

hi antony

try this

SET ErrorMode = 0;

for a=1 to 3

LOAD * FROM

C:\Users\napo\Desktop\Excel Data\*.xlsx (ooxml, embedded labels, table is Sheet$(a));

next

SET ErrorMode = 1;

may it helps you

thanks

rohit

Not applicable

Hi Rohit,

Thanks,

             Its working. Suppose if i am updating some data on sheet4 , how do i get Sheet4 data without touching in script part.

Regards,

Antony.