topic Re: Load visible excel sheet in QlikView

Load visible excel sheets

Thu, 20 Aug 2015 01:33:36 GMT

Hi Guys,

Ok this is my challenge. I have a lot of excel files to load ()

For each file I need to loop and get all the sheets.

So far it's working.

I discover, there is hidden sheets in my excels that I don't need to load.

Example : A sheet is the name "A" can be visible in File A and hidden in file B.

How can I detect in Qlikview if it's a visible or hidden sheet?

Thanks a lot.

Eva

Re: Load visible excel sheet

Thu, 20 Aug 2015 05:41:20 GMT

You could probably use RobW's approach in this post...

I've used it in the past, and managed to get it working...

https://community.qlik.com/servlet/JiveServlet/download/680888-141894/excelMultiSheet.qvw

Re: Load visible excel sheet

Fri, 21 Aug 2015 00:02:59 GMT

Hi,

Thanks for your answer. I do have this kind of script.... but it's loading invisible sheet.

Cheers

Eva

Re: Load visible excel sheet

ecolomer — Fri, 21 Aug 2015 03:52:22 GMT

See this to load all sheets in all excel ...

Re: Load visible excel sheet

Fri, 21 Aug 2015 04:27:37 GMT

Thanks,

It's really close to my script but my problem is not about loading all the sheets. It's about NOT loading the HIDDEN sheets.

Sorry for the confusion.

Eva

Re: Load visible excel sheets

marcus_sommer — Fri, 21 Aug 2015 10:55:26 GMT

If you could change those excel-files you could create a small load-table within them (maybe in a further hidden sheet) which contained all sheets and their visibility-state - automatically created/updated per an OnClose trigger - and this table is then your filter through which you looped for loading each desired sheet.

If you couldn't change those files you will need to do the same from external - maybe through a vbs-file and triggered per execute-statement directly from the qv-load-script. But easier would be to use excel itself beforehand which runs through all the excel-files and creates a kind of master-table with the available files and sheets.

Conclusion: I think the only way is to query this per macro, see: Worksheet.Visible-Eigenschaft (Excel).

- Marcus