how to count no.of sheets in execl file

[rohit214]

hi all

i have execl fine named A..in A there are n no of sheets.

so i want to count that sheets.

is there any function to count no of sheets in execl file data?

regards

rohit

[#2]

Hi Rohit,

An ODBC connection to the excel file can give you the number of sheets. It should look like something below.

  ODBC CONNECT TO [Excel Files;DBQ=c:\temp.xls];
  tables:
  SQLTables;
  DISCONNECT;

  NoOfRows('tables') will give you the number of sheets.

Regards,

Sajeevan

[rohit214]

hi Saajeen

thanks for your reply ...

i am not getting u properly will you explain in detail.

in fact i want to load all sheets at a time ,

thanks rohit

[SunilChauhan]

ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\sunil\test.xlsx];

tables:

  SQLTables;

change here path of excel it wiill load all sheets from xls.

[#5]

Hi Rohit,

Let us assume that you have an excel file called "c:\temp\abc.xls" which contains multiple sheets. Try the below to load data from all sheets in one go.

  ODBC CONNECT TO [Excel Files;DBQ=c:\temp\abc.xls];

  tables:

  SQLTables;

  DISCONNECT;

  FOR i = 0 to NoOfRows('tables')-1

     LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

     Data:

     LOAD *

     FROM c:\temp\abc.xls (biff, embedded labels, table is $(sheetName)) where right('$(sheetName)',1) = '$';

  NEXT

  DROP Table tables;

Let me know whether this works.

Regards,

Sajeevan

[SunilChauhan]

please use below code

ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\sunil\test.xls];

  tables:

  SQLTables;
  let var=NoOfRows('tables');
FOR i = 0 to $(var)-1;

next i

see the attached files also

[rohit214]

hi

sanjeev

its is not working,here i am attached my exel file

plz find the attachment

[rohit214]

hi sunil

in my execl file fiels are diffrent and i want to reload all sheets data at a time..i.e. all sheets

thanks

rohit

[#9]

Hi Rohit,

The code I have given you is for ".xls" format not for ".xlsx" file. I will try with the file you sent and let you know. For the time being you can open your ".xlsx" file and save as ".xls" file then my code should work.

".xls" files are BIFF format but ".xlsx" file are XML format hence need to rework on the code. I don't have office2007.

Thanks,

Sajeevan

[rohit214]

hu sajeevar

very very thank you for helping me..

thanks rohit