Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
Anybody got some sample script I could use???
Cheers Vlad... but sample script would be appreciated
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,
Thanks Vlad
Worked like a charm
I didn't know about the errormode setting so that got me over the problem
Thanks again
Dave
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;
Are the worksheets in your workbook named Sheet1, Sheet2, etc?
Yes, Sheet1 , Sheet2 .....
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
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.