I'm trying to figure out what file extension a file has before load statement. The file is provided by users and sometimes they use .xls and other times .xlsx, so I need to alter loading depending on extension.
Right now the closest to a good solution is two load scripts and supress errors with
But I would like a more flexible solution, perhaps an IF-statement? Check filelist?
Based on this code, I'd do something like the following
SET vFileIsBiff = '(biff, embedded labels, table is [Sheet1$])';
SET vFileIsOOXML = '(ooxml, embedded labels, table is Sheet1);';
Sub DoDir (Root)
For Each vExt In 'xls', 'xlsx' // filetype to search for in current directory
For Each vFile In FileList (Root & '\Test.' & vExt)
LET vFileType = If('$(vExt)' = 'xls', '$(vFileIsBiff)', '$(vFileIsOOXML)');
For Each vDir In DirList (Root & '\*') // search in subdirectories
Call DoDir (vDir)
Call DoDir ('C:\Temp'); // Starting point
So depending on the extension you load each format. The code still seems to me too complex, and some directory recursion might not be needed, or even just an If() will do, since you only want to load one file and there is no need to use a For loop...