Using Excel with file names to select files and create new folder

Here is an AppleScript solution to a problem defined as:

1. Excel spreadsheet with first column containing just filenames, not paths to those filenames
2. Want to copy files in [1] from a non-hierarchical source directory to a destination directory.

What the script does:

1. Prompts the user for the source directory and destination directories. User single-clicks the source directory in the folder chooser, presses the cmd+key, and then selects the destination folder. Order is critical.
2. Prompts the user to choose the Excel spreadsheet to process.
3. Copies the files in the first column of the spreadsheet to an AppleScript list, closes the spreadsheet and quits Excel.
4. Since Excel creates a list of lists, these must be reduced to a single list of filenames. For performance, this list is made a list of object enumerators.
5. Repeat the file copy from source folder to destination folder for each filename. Again, for performance reasons, this does not involve the Finder in this copy process.
6. Produced a final dialog of destination folder name and number of files copied.

Copy and paste the following AppleScript source into the Apple Script Editor. Click the compile (hammer icon) button, and then click the Run button.

Tested: macOS 14.2.1, Excel v16.80.

Code:

(*
 Excel_values_list.applescript
 
 Read a first column of filenames from an Excel spreadsheet into
 an AppleScript list, select a source, destination folder, and
 the Excel spreadsheet. Without using Finder, copy source files
 to the destination folder.
 
 Tested: Sonoma 14.2.1
 Author: vikingosx, 2023-12-19, Apple Support Communities, No warranties
*)

use scripting additions
use framework "Foundation"

property NSString : a reference to current application's NSString
property NSURL : a reference to current application's NSURL
property NSArray : a reference to current application's NSArray
property NSFileManager : a reference to current application's NSFileManager
property cnt : 0 as integer

set fm to NSFileManager's defaultManager()

set colval to {} as list

set folder_choice to (choose folder with multiple selections allowed) as list
-- convert AS alias paths to NSURL paths in the Array
set ufolder to (NSArray's arrayWithArray:folder_choice)'s allObjects()'s valueForKey:"absoluteURL"
set srcFolder to ufolder's firstObject()
set dstFolder to ufolder's lastObject()

set excelss to (choose file of type {"org.openxmlformats.spreadsheetml.sheet"})

tell application "Microsoft Excel"
	activate
	open excelss
	tell first column of used range of active sheet
		-- put every value in Column 1 into array colval
		set colval to its value
	end tell
	close every workbook saving no
end tell
tell application "Microsoft Excel" to if it is running then quit

-- this flattens the list of lists that Excel generates in colval
-- use ObjectEnumerators to speed later selection
set delisted to ((NSArray's arrayWithArray:colval)'s valueForKeyPath:"@unionOfArrays.self")'s allObjects()'s objectEnumerator()

-- copy all selected files from source dir to dest dir without the Finder
repeat (count of colval) times
	set theFile to delisted's nextObject()
	set infile to srcFolder's URLByAppendingPathComponent:theFile
	set outfile to dstFolder's URLByAppendingPathComponent:theFile
	-- this will abort if destination file already exists
	try
		fm's copyItemAtURL:infile toURL:outfile |error|:(reference)
		set cnt to cnt + 1
	end try
end repeat
set outFolder to (dstFolder's lastPathComponent()) as text
display dialog "Files copied to folder " & outFolder & " =  " & (cnt as text) ¬
	with title "Folder Copy Operation Complete"
return

Some questions and observation.

A single Excel column?

Column header?

Column data is filename.ext or a path/filename.ext?

Will need to select the parent folder and destination folder as input to solution. The code I have right now gets the used range of the first column in an Excel Spreadsheet and returns that into a Zsh shell array. Zsh only because it is significantly faster processing and moving files than Finder for larger data sets.