Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
jmvilaplanap
Valued Contributor

Load al the excel sheets

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

1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor

Re: Load al the excel sheets

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

9 Replies
Frank_Hartmann
Honored Contributor

Re: Load al the excel sheets

Maybe like attached

hope this helps

Frank_Hartmann
Honored Contributor

Re: Load al the excel sheets

this example might also be interesting

hope this helps

jmvilaplanap
Valued Contributor

Re: Load al the excel sheets

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
Valued Contributor

Re: Load al the excel sheets

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
Honored Contributor

Re: Load al the excel sheets

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
Honored Contributor

Re: Load al the excel sheets

jmvilaplanap
Valued Contributor

Re: Load al the excel sheets

Hi

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

Thanks

jmvilaplanap
Valued Contributor

Re: Load al the excel sheets

Hi,

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

Regards

Frank_Hartmann
Honored Contributor

Re: Load al the excel sheets

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