Hey Panos,
That's not a bad idea.
I've written this task in AppleScript again, but I've used only vanilla AppleScript and the Keyboard Maestro Engine's AppleScript functions.
You need to adjust these two lines:
set mealsRootPath to "~/test_directory/INSYS/Meals/"
set excelFile to "~/Downloads/yyyy-mm - Meals.xlsx"
Do not touch this part of the Excel file path:
/yyyy-mm - Meals.xlsx
Run the AppleScript from the Script Editor.app to see how it works.
Run it from an Execute an AppleScript action in Keyboard Maestro.
The script gets the same data the others do, but it omits the week. Use Excel's =WEEKNUM(cellRef) function to populate your WEEK column.
The script finds the correct insertion point in Excel and pastes automatically, and that should function more organically than brute-forcing the UI as we did with Keyboard Maestro. This should also prevent the timing issues you were having with the Keyboard Maestro macro.
When you run the script the currently existing files of the current month will be processed and marked with a green label. Subsequent runs of the script will ignore already labeled files.
I'm using Excel from Office 2011, so I can't guarantee it'll work with Office 2016 – but chances are that it will.
-Chris
----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/04/04 05:19
# dMod: 2018/04/04 05:46
# Appl: Finder, Keyboard Maestro Engine, Microsoft Excel, System Events
# Task: Extract Meal Data from file names in Finder and paste them into an Excel spreadsheet.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Finder, @Keyboard_Maestro_Engine, @Microsoft_Excel, @System_Events, @Extract, @Meal, @Data, @File, @Names, @Paste, @Excel, @Spreadsheet
# Vers: 1.00
----------------------------------------------------------------
set mealsRootPath to "~/test_directory/INSYS/Meals/"
set excelFile to "~/Downloads/yyyy-mm - Meals.xlsx"
set yearMonth to text 1 thru 7 of ((current date) as «class isot» as string)
set excelFile to kmReplace("yyyy-mm", yearMonth, excelFile, false, false, false)
set yearMonth to kmReplace("-", "/", yearMonth, false, false, false)
set mealsMonthPath to mealsRootPath & yearMonth
tell application "System Events"
set excelFile to (path of file excelFile) as alias
set excelFileName to name of excelFile
set fileList to path of files of folder mealsMonthPath whose visible is true
end tell
tell application "Finder"
repeat with theFile in fileList
set fileRef to theFile as alias
if fileRef's label index = 6 then
set contents of theFile to 0
else
set fileRef's label index to 6
set contents of theFile to name of fileRef
end if
end repeat
end tell
set fileList to text of fileList
set AppleScript's text item delimiters to linefeed
set fileList to fileList as text
set fileList to kmReplace(" - | (?=\\d)", tab, fileList, true, false, false)
set fileList to kmReplace("(?m)\\.pdf$", "", fileList, true, false, false)
set the clipboard to fileList
if fileList ≠ "" then
tell application "Microsoft Excel"
if not running then
run
delay 5
end if
activate
if document excelFileName exists then
activate object window excelFileName
else
open excelFile
end if
tell active sheet of document excelFileName
set lastfilledcell to get end (range "B65536") direction toward the top
set firstBlankCell to get offset lastfilledcell row offset 1
select firstBlankCell
paste
end tell
end tell
end if
----------------------------------------------------------------
--» HANDLERS
----------------------------------------------------------------
on kmReplace(findPattern, replacePattern, dataStr, regExBool, caseBool, tokensBool)
tell application "Keyboard Maestro Engine"
set foundDataList to search dataStr for findPattern replace replacePattern ¬
regex regExBool case sensitive caseBool process tokens tokensBool
end tell
end kmReplace
----------------------------------------------------------------
consider my mind blown…
