Count the number of sheets in a excel file

[WalidChoukri]

Hello everyone,

I'm looking for a way to calculate the number of sheets of an excel file in the script. 

Thank you.

[chrismarlow]

Hi,

Did you try this;

Solved: how to count no.of sheets in execl file - Qlik Community - 320445

Cheers,

Chris.

[WalidChoukri]

Actually yes but it didn't work, and i couldn't figure out which variable return the number of sheets.

Can you please explain that solution as i didn't know what i did wrong.

Thanks.

[chrismarlow]

Hi,

So you tried a minimal;

ODBC CONNECT TO [Excel Files;DBQ=C:\yourpath\yourfile.xlsx];

tables:
SQLTables;
DISCONNECT;
let var=NoOfRows('tables');

Did it not work at all? What error did you get when the script ?

I do get an error on this ... I think Excel Files as an ODBC driver is not set up how things were back in the day ... so I had to create an ODBC connection first;

Maybe same as your error?

Cheers,

Chris.

[WalidChoukri]

Hi,

I tried the ODBC just like in the solution, in a test file, it couldn't connect to the table. thought it was a connectivity problem,  I tried with my already loaded excel file, the script runs correctly, but I don't get the result, when I check the variables var contains the number of rows as it's supposed to and i  is irrelative, so I don't know how can you get the number of sheets using this formula. in which variable it's supposed to be stored, and if there's another way to do it.

Thanks for your help anyway Chris.

[chrismarlow]

Hi,

So doing NowOfRows('xxx') will return the number of rows in table named xxx. So when you point this at the loaded table that you loaded 'normally' you get what you are saying, this normal load however does not load anything like a count of the sheets in a workbook.

The ODBC is different in that it does not load the Excel file as such, it loads the file meta data from SQLTables into 'tables', counting those rows gives you the count of sheets.

So if you can't fix the ODBC issue, you can't do this (please someone else shout if there is a different way).

Depending on your set up you may of may not be able to add/change ODBC settings (I am on my own laptop, had I been on one of my old work laptops and got the same issue, suspect I would have been talking to IT/finding something I was allowed to install that gave me the drivers).

Cheers,

Chris.

[WalidChoukri]

Thanks for your answer, at least now I understand the logic of that solution, hopefully someone we'll help to solve this issue.