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?
Solved! Go to Solution.
please try this code
use your xls path
ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\Security.xlsx];
FOR i = 0 to $(var)-1
let sheetName=subfield(peek('TABLE_NAME', i,'tables'),'$',1);
(ooxml, embedded labels, table is '$(sheetName)')
DROP Table tables;
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];
NoOfRows('tables') will give you the number of sheets.
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 ,
ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\sunil\test.xlsx];
change here path of excel it wiill load all sheets from xls.
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];
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
FROM c:\temp\abc.xls (biff, embedded labels, table is $(sheetName)) where right('$(sheetName)',1) = '$';
DROP Table tables;
Let me know whether this works.
please use below code
ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\skchauhan\Desktop\sunil\test.xls];
FOR i = 0 to $(var)-1;
see the attached files also
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.