Traverse file system of Excel WORKBOOK, and manipulate each WORKSHEET using Apple Script! (MacOS)

Question

I am working on Excel. We have a little over 25,000 excel WORKBOOKS that need 2 columns deleted from each of their sheets within. (Meaning if all 25k had 1 sheet, we should be deleting 50k columns.) But most of them have multiple sheets in them.

So far this is what I have...

set sourcefilepath to (choose folder) as alias
tell application "Finder" to set sourcefiles to ¬
    (every file in the folder sourcefilepath whose ¬
        name ends with ".xlsx") as alias list

tell application "Finder"
    set filecount to count files in the entire contents of sourcefilepath
    tell application "Microsoft Excel" to open the sourcefiles
    repeat filecount times
        set myValues to "Prod Alpha, Warehouse No"
        tell application "Microsoft Excel"
            activate
            set mysheets to every sheet of active workbook
            repeat with asheet in mysheets
                tell asheet
                    set columnCount to (((count of columns of used range of active sheet)) + 1)
                    repeat with i from columnCount to 1 by -1
                        set cellValue to value of row 1 of column i
                        if cellValue is in myValues then
                            delete column i
                        end if
                    end repeat
                end tell
            end repeat
            close active workbook
        end tell
    end repeat
end tell

This is now working. It opens all the files (Very slow for obvious reasons). Deletes the two columns I need gone in one, closes it and moves to the next. Problem is... about 30% of the way done, it will just stop. Leaving a ton of files open, and thus a very unresponsive computer. I have to force quit excel.

I know I could break them up into smaller folders and do it that way, but I want to learn how to make this solution better so in the future, I can work towards a solution in a better manner.

My thoughts are that I need to not open all the files at once. Open them separately. Fix it, Save it, Close it, then move to the next.

If programming in any other language, I would accomplish this using a for loop and traversing an array, but for the life of me cannot figure out how to do it with apple script. (Im super new to this apple script stuff).

Any help would be appreciated!

Thanks, K

Answer 1

This is what I ended up with. I thank you @Ted Wrigley for your response. I don't understand the POSIX thing yet!. I added it to my list of stuff to study this week.

I am going to look into the built in excel functions, such as the find one you mentioned. I will post again if I am able to update it!

tell application "Finder" to set sourcefiles to ¬
    (every file of (choose folder) whose ¬
        name extension is "xlsx") as alias list


set filecount to count sourcefiles
repeat with each_file from 1 to filecount
    
    
    tell application "Microsoft Excel"
        activate
        open item each_file of sourcefiles
        set myValues to {"Prod Alpha", "Warehouse No"}
        set mysheets to every sheet of active workbook
        repeat with asheet in mysheets
            tell asheet
                set columnCount to (((count of columns of used range of active sheet)) + 1)
                set delete_count to 0
                repeat with i from columnCount to 1 by -1
                    set cellValue to value of row 1 of column i
                    if cellValue is in myValues then
                        delete column i
                        set delete_count to delete_count + 1
                    end if
                    if delete_count = 2 then
                        set delete_count to 0
                        exit repeat
                    end if
                end repeat
            end tell
        end repeat
        close active workbook with saving
    end tell
end repeat

Answer 2

Generally speaking, don't script the Finder unless you absolutely have to; In particular, don't ever use the entire contents property. Use System Events and iteration instead. The Finder is an old and very busy app. It tends to freeze up if it gets overworked by a script, and entire contents almost always overworks it.

I've reworked your script with that in mind:

• Used System Events for the file system tasks
• Used Excel's built-in find function find the correct cells, not a repeat loop
• Used iterative function calls to drill down through the folders

I doubt this will be all that fast (for the obvious reasons, but I don't see any way to speed the process up at the moment. I'll think on it...

set sourcefilepath to (choose folder) as alias
my iterateFolder(POSIX path of sourcefilepath)

on iterateFolder(aFolderPath)
    tell application "System Events"
        set fileList to every file of folder aFolderPath whose name extension is "xlsx"
        repeat with anExcelFile in fileList
            my processFile(POSIX path of anExcelFile)
        end repeat
        set folderList to every folder of folder aFolderPath
        repeat with aFolder in folderList
            my iterateFolder(POSIX path of aFolder)
        end repeat
    end tell
end iterateFolder

on processFile(aFilePath)
    set myValues to {"Prod Alpha", "Warehouse No"}
    tell application "Microsoft Excel"
        -- you don't actually need to activate the workbook, not unless you want to watch it work
        activate
        open aFilePath
        tell workbook 1
            set sheetList to its sheets
            repeat with aSheet in sheetList
                repeat with aColumnName in myValues
                    set deletableHeaderRange to find (first row of used range of aSheet) what aColumnName look at whole
                    set deletableColumnRange to entire column of deletableHeaderRange
                    delete deletableColumnRange
                end repeat
            end repeat
        end tell
        close active workbook
    end tell
end processFile