Looping through Excel Sheets - Only want to load where the Tab "ALL" Exits

[bwisealiahmad]

Hi,

So this is my current scripting:

ErrorMode = 0 ; // Continue running on errors

[$(FileName)]:

LOAD

*,

FileSize() as FileSize

FROM ['$(File)']

(biff, embedded labels, header is 3 lines, table is ALL$);

Store '$(FileName)' Into lib://1_Extract/4_Doosan_MaskinData\$(FileName)_MaskinData_ALL.qvd (qvd);

Drop Table '$(FileName)';

ErrorMode = 1 ; // Restore deafult error mode

This is part of a for each loop and what I am doing is looping through Excel sheets and loading data from a tab named "ALL$". Now the issue is that on holidays and sundays this tab is not generated in the document.

I've thought about doing a FileSize check and see if the file is larger than 8kb and then load because every file that is 8kb does'nt contain data.

Any other suggestiosn to get around this? When I get to a file without the tab "ALL$" it crashes. I've used errormode to work around it, but would like  to either check if the file has any rows before I load it or maybe filesize?

Thanks.

[NZFei]

On holidays and Sundays, do you have that file exist? In that file, do you have the ALL$ sheet? In that sheet, do you have nothing there, or you have headers but no data?

When I try to load data from a completed empty sheet, the reload failed and the table in Sense will not be created at all. If there is any header in the sheet, the table will be created in Sense.

Fei

[bwisealiahmad]

The sheet does not exist on those days.

Thanks for responding!

Få Outlook for Android<https://aka.ms/ghei36>