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)'); File: LOAD * FROM [$(vFile)] $(vFileType); Next vFile Next vExt For Each vDir In DirList (Root & '\*') // search in subdirectories Call DoDir (vDir) Next vDir End Sub 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...
Hope that helps, anyway.
You could create a custom function for use in the script.
We must remember that a file could exist as BOTH an .xls and an .xlsx in the same folder and that could cause problems!
In the script you could create a variable that will hold the extension name for the name of the file (without extension) passed to the custom function like this:
LET vExtn = WhichExtn('C:\mypath\filename');
which you could test for a valid value being returned by the function and if OK then use the (substitution) variable in the load statement.
The function would look like this:
Function WhichExtn (fileName)
WhichExtn = ""
set fso = CreateObject("Scripting.FileSystemObject")
on error resume next
set fileExists = fso.GetFile(fileName & ".xls")
if err = 0 then ' file exists as xls
WhichExtn = WhichExtn & "xls"
set fileExists = fso.GetFile(fileName & ".xlsx"
if err = 0 then ' file exists as xlsx
WhichExtn = WhichExtn & "xlsx"
on error goto 0
set fso = Nothing ' release memory
This will trap the file not existing with either extension (vExtn = "") or if the file exists in both formats (vExtn = "xlsxlsx"
Hope this helps.