Load al the excel sheets

[jmvilaplanap]

Hi All,

I need to load all the spreadsheets of an excel file (more than 50) and I want to try to do it semi-automatic. I have seen in others questions that the best way is to use a ODBC connexion to the excel file and get all the sheet names, but this doesn´t work for my, I can't configure this ODBC connexion.

My solution (for now) is create an inline table with the names of the sheets and do a loop, but I don`t like it, I think must to have another better solution.

Could anyone help me?

Thanks

[Frank_Hartmann]

Maybe like attached

hope this helps

[Frank_Hartmann]

this example might also be interesting

hope this helps

[jmvilaplanap]

Hi,

The problem here is when the sheets have no consecutive labels. In my case, the sheets have diferent names as:

• Sales
• Budget
• Clients
• Providers
• ...

Thanks

[jmvilaplanap]

Hi,

In this example you're using a OLEDB connection, but my question is if is possible to get the sheets name directly from the file or get the data without know the name of the sheets

Regards

[Frank_Hartmann]

You could solve it using a macro.

The macro extracts the sheet names and writes it into a variable.

then you can use this variable in your loop!

see attached zip file.

You will just have to adapt the paths inside the vbs file and the QV-Script according to your environment

hope this helps

[balabhaskarqlik]

Try this:

Re: Excel with multiple sheets

[jmvilaplanap]

Hi

This solution is useful, but I looking for one solution inside qlik.

Thanks

[jmvilaplanap]

Hi,

This solution only works if the name of the sheets have a pattern, for example sheet1, sheet2, etc....

Regards

[Frank_Hartmann · Accepted solution]

Ok, what do you mean with "inside Qlik"?

would it be also acceptable to use a macro within the module of QV instead of an external vbs file?

If yes, then try attached solution.

hope this helps