If I understand correct you load each sheet to a QVD with a separate QVW.
If that is the case then you can use this script to load all sheets to 1 QVD:
(change names of course to match your data source)
for each file in FileList('Your_data.xlsx')
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
tables:
SQLtables;
DISCONNECT; // Don't need ODBC connection anymore
/*
One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.
We will loop through this set of sheet names.
*/
FOR i = 0 to NoOfRows('tables')-1
/*
Assign the TABLE_NAME to the variable "sheetName".
TABLE_NAMEs that contain spaces will be enclosed in single quotes.
The purgeChar function will remove any quotes AND dollar signs.
*/
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
// Optional filtering logic to select certain sheets
IF IsNum('$(sheetName)') THEN // Only sheetNames that contain Week Numbers
Your_data:
// Now that we have a sheet name, a standard biff (Excel) load can be used.
LOAD *,
'$(sheetName)' as Sheet // Optionally, the sheetName value may be loaded as a field
FROM $(file) (ooxml, embedded labels, table is [$(sheetName)]);
END IF // End of optional sheet filtering
NEXT
DROP TABLE tables; // The table list is no longer needed
Store Your_data into Your_data.qvd (qvd);
