Load list of Excel files

Share icon

Hi all,

I'm trying to load a list of Excel files into waves in code. Previously I asked here for help with loading a list of txt files: https://www.wavemetrics.com/forum/general/integer-division-convertation-double-integer-loading-list-waves-point-corresponding

I took the function https://www.wavemetrics.com/code-snippet/load-all-files-folder-assumes-each-file-contains-one-column-numbers

Now I slightly modified this function and below you can see the current view of it:

Function LoadAllFilesFromFolder(pathName, extension)
    String pathName             // Name of an Igor symbolic path or "" to get a dialog
    String extension                    // File name extension - e.g., "txt" or ".dat"
 
    if (strlen(pathName) == 0)
        NewPath/O/M="Choose a folder containing data files" DataFilesPath
        if (V_flag != 0)
            return -1               // User cancelled
        endif
        pathName = "DataFilesPath"
    endif
   
    Variable numFilesLoaded = 0
 
    String fileName
    Variable index
 
    index = 0
    do
        fileName = IndexedFile($pathName, index, extension)
        print "fileName=",fileName
        if (strlen(fileName) == 0)
            break           // No more files.
        endif
 
        String wName = ParseFilePath(3, fileName, "\\", 0, 0)    // Remove extensions
        print "wName=",wName
        //wName = CleanupName(wName, 0)                   // Make legal standard Igor wave name
        //Variable existsCode = exists(wName)
        //if (existsCode!=0 && existsCode!=1)
            // wName conflicts with some name other than a wave name so make it unique
        //    wName = UniqueName(wName, 1, 0)
        //endif
 
        //String columnInfoStr
        //sprintf columnInfoStr, "N='%s';", wName
 
        //LoadWave/G/D/P=$pathName/A/O/B=columnInfoStr/Q fileName
        XLLoadWave/S="Sheet1"/R=(A1,B15)/D/Q fileName
        //Wave w = $wName         // Create a wave reference
        // Do something with w if you wish
        Wave ColumnA, ColumnB
        Duplicate/O ColumnA, $wName+"_Time"
        Duplicate/O ColumnB, $wName+"_Int"
        KillWaves/Z ColumnA, ColumnB
       
        numFilesLoaded += 1
        index += 1
    while (1)
   
    Printf "Loaded %d files\r", numFilesLoaded
End

macro loadFilesFromFolder()
    LoadAllFilesFromFolder("", ".xlsx")
end

The function works not properly. The first problem - when I run macro loadFilesFromFolder(), it requests for selecting data folder, I select data folder, press 'Open', and then it again requests for selecting folder. I again select folder, and now I see Excel files in that folder. I manually select each Excel file, one by one, each time dialog window appears. Finally, the program loads all selected files. But of course I'd like to load all files at once, without dialog windows. Could you please tell me, how can I solve these problems? Please find attached three test Excel files. Each file contains two columns of numbers.

 Excel files (16.85 KB)