Is there a way to retreive Excel sheet name from QV?

[#1]

Hi:

I have several Excel files, each one containing several sheets, but all have exactly the same structure. I've done something like this:

for counter = 1 to 10

Load a,b,c

FROM [*.xls] (biff, embedded labels, table is @$(counter));

next

The problem is that I can only read a fixed number of sheets (in this case 10), which is not always true. So here are my questions:

1.- Is there a way to use a "for each" clause refering to "each tab in each excel file" ? Using the Filelist will loop through all the Excel files, but I haven't found a way to loop through the tabs. The number of tabs is different in each file. I thought of using a big enough number in the "for" statement, but doing so will raise an exception when it doesn't find such a tab in the Excel File. Is there a TabExists() function I can use?

2.- is there a function sort of "ExcelTabName()" that I can use in the Load list? The reason I need this is that I need to know which period the data belongs to (this is the tab name, example, 20090401).

Thanks.

Ruben

[Karl_Humma]

Give this a try.

/Karl

[rwunderlich]

For some additional examples of handling multiple sheets, see the QV Cookbook example "Load multiple sheets from Excel workbook". You can download the WV Cookbook from http://robwunderlich.com/Download.html.

The example deals with a single xls file. If you want to process all xls file in a directory, an interesting alternative to using filelist is using the Excel driver as discussed in the "Notes" tab in the script. The filename (minus the .xls extension) will be in the SQLTABLES TABLE_CATALOG field.

-Rob

[elizabethbamber]

Hi

The methods of getting Excel tab names using an OLE or MSDASQL connection and "SQLTables" to list tab names works OK with QlikView but fails when a reload is carried out using QVServer9 (64bit). This is because there is no 64bit OLEDB provider for Jet. Has anyone found an alternative way to determine tab names from excel?

Thanks