I am trying to load multiple sheets from an Excel 2010 file with the following script - this script seems to work for many people in the community.
The script windows shows that all the lines have been fetched. However, after that I get an error message "Execution of Script Failed. Reload Old Data?"
I checked and I do have read / write access to the folder.
I closed all existing QV.exe from the task manager (even restarted the machine a few times!)
I created a new .qvw file, inserted the script and received the same error.
LET vFilePath = 'C:\Qview\ALM_ALCO_PACK\trial';
FOR EACH file in FileList('$(vFilePath)\*.xlsm'); // Loops each excel file in the given Folder
//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];
SheetNames:
SQLtables; // Loads all sheet names in the Excel file.
DISCONNECT;
FOR index = 0 to NoOfRows('SheetNames')-1 // Loops for each sheet in the Excel file.
LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));
TableName:
Load * ,
FileBaseName()as File,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT index
DROP TABLE SheetNames;
NEXT
I am also attaching the excel file i was trying to load.
Please help.
Thanks,
Anand
