get the Sheetname without OBDC Connection in script

[Mosy94]

Hello everyone, 

i'm trying to get the Sheetname and number of sheets of the excel file in the script without any Connection to any where. i only have the file path. after that i want to check if the name is what i think it should be and if not then error. i've tried foreach and filepath() but unfortunately it won't give me the excel sheetname. 

Sub NoOfFiles(myPath, myNoOfFiles)
// Count the number of files, meeting creteria, in a path
  Let tNoOfFiles = 0;
For Each File In Filelist ('$(myPath)')
// Count +1 for each file in the path
   Let tNoOfFiles = tNoOfFiles + 1;   
   let vTableName = SubField('$(File)', '/',-1);
   

    If WILDMATCH(vTableName, '*Sach*') then 
    						Let vSachExist = 1;
                            Let vSachTableName=vTableName; 
                           Let sheet=  'Tables';
     elseif WILDMATCH(vTableName, '*Deb*','*Kredit*')  then
     let vDebExist = 1;
     Let vDebTableName=vTableName;
     ElseIf  WILDMATCH(vTableName, '*Buch*') then
     let vBuchExist = 1; 
     Let vBuchTableName=vTableName;
     EndIf
Next
Let $(myNoOfFiles) = tNoOfFiles; // Declare the variable and set the value
  Set tNoOfFiles=; // Undeclare
End Sub

when i search the internet it seems like OBDC/ODBC Connection is the only way. is there any alternative to it.

pls help i'm working on it for a week

[marcus_sommer]

To access an Excel per ODBC is the only direct way to read / count the included sheets with Qlik.

An alternatives could be to include these information within the Excel itself by storing them within an extra (hidden) sheet. The information there might be written by an appropriate OnClose macro or fetched with functions (in the old days there were Excel4-functions which were capable for such tasks - nowadays I don't know if they further exists and are working or if there are new features which might be used for).

Another method might be to loop with an external vba/vbs macro through the folder and reading with it these information.

If your Excel-files are newer xlsx they are in reality a bunch of various xml-files which were zipped with the extension of xlsx. This means if you unzip the file and accessed the xml you could also get the sheet-information (of course they doesn't come on a silver-tray else they must be picked here and there ...).

Compared with an ODBC access all alternatives are needing more efforts and are causing more complexity and may also have some restrictions and side-effects. Therefore I suggest to consider again if an ODBC solution is yet more suitable.

- Marcus