I have been asked to look at the contents of a specific folder and copy the file names into one column of a spreadsheet and the last edited date in a second column. The macro can run once a day or so to look for new files and just keep appending the file names to the spreadsheet. The spreadsheet will most likely be a Google S, but could be Excel or Numbers.
I am a fairly new KM user and I'm not sure this is the right tool for the job - but it seems like it might be able to handle the task with some Apple script or something. I might just be over thinking it. If anybody has any suggestions they would appreciated.
Thanks for looking at this… Hopefully I can answer with enough detail that this is helpful.
What does your spreadsheet look like when it has no data?
Does it have headers for your columns?
Is there data other than the file name and the mod date in the sheet? The spreadsheet would have four columns with headers - Date, Filename, Description, and a Printed header. We are only responsible to enter the date (from the file date) and the filename (whatever the file is called). The other columns will be filled out manually.
The format of the spreadsheet is fairly flexible. If eliminating some of the columns would make this easier, we could make some changes to the Excel file.
Probably the best tools for the job are going to be AppleScript and Microsoft Excel. I am OK with Excel. I have limited experience with AppleScript…but am excited to learn!
How are you handling your watch-folder?
Are you just accumulating files? If so how many files are likely to accumulate?
Are there any nested folders?
What is the mechanism by which files are added to the watch-folder? Today, the watch folder is a folder on a NAS unit. Files are copied there or saved there as they are generated. At this point, there are folders nested below the watch folder, but we do not need to do anything with those folders. The files are just accumulating. At this point, there is probably 200 files or so - and 5-10 get added weekly. I would expect this script to run 1-2x per week to add any new files to the spreadsheet that have been added to the folder.
I’m generating this system for my wife and her side business. I am open to some scope changes if they make the tas significantly easier to accomplish. This isn’t a situation where I have 10 people i have to make happy with the system…it’s really only one decision maker.
-------------------------------------------------------------------------
set watchFolder to alias ((path to home folder as text) & "test_directory:Watch_Folder_Test:")
set excelList to {}
set fileNameList to {}
set modDateList to {}
# Retrieve Name & Modification Date of the files in the watch folder.
tell application "Finder"
set fileAliasList to (files of watchFolder whose label index is not 6) as alias list
if fileAliasList = {} then error "No new files!"
set AppleScript's text item delimiters to " "
repeat with i in fileAliasList
set end of fileNameList to name of i
set end of modDateList to (get {short date string, time string} of (get modification date of i)) as text
set label index of i to 6
end repeat
end tell
# Convert the list of items to a list of lists.
repeat with i from 1 to (length of fileNameList)
set end of excelList to {item i of fileNameList, item i of modDateList}
end repeat
# Append data to columns A & B of the active worksheet.
tell application "Microsoft Excel"
tell active sheet of active workbook
set lastFilledCell to get end (range "A65536") direction toward the top
if value of lastFilledCell = "" then
set firstBlankCell to lastFilledCell
else
set firstBlankCell to get offset lastFilledCell row offset 1
end if
set cellAdrsLocal to get address local firstBlankCell
set AppleScript's text item delimiters to "$"
set lastRow to last text item of cellAdrsLocal
set newRange to cellAdrsLocal & ":$B$" & (lastRow + (length of fileNameList) - 1)
set value of range newRange to excelList
end tell
end tell
-------------------------------------------------------------------------
Hey @ccstone: Don't know if you are still active, but if you are, I have a question! Is it possible to have the script browse through folders at the given location and get the file info from all the files located in the different folders.....?
