FACTS:
Ideal outcome- I run the automator / applescript and point it to the folder containing the .pdfs and the excel sheet containing the information. It runs and renames all the .pdfs in the folder that are found in the excel. The renamed files would consist of the test#, a space, and the original name. The row in the excel sheet would be highlighted / flagged in some way, showing it was found.
I have searched a few different forums as well as this one and have tried to piece together code to try and get something to work. I won't paste this code as I haven't gotten any of it to work in my scenario.
The following code I merged copied from another help forum and tried to tweak it for my scenario. It sort of works for the first 6 items then just stops (I think because the 7th item is not in the folder). Also, to get it to work I had to manually create the original file names and new files names within the excel. I did this because I wasn't able to get it to work outside of excel.
Any and all help, suggestions, links to possible solutions are welcome. I have A LOT of test results to go through on many different excel sheets and this would save me a ton of time. I lost today trying to figure this out myself. I am either overthinking this or in over my head. Thank you!
set KeyFileName to choose file with prompt "Choose the Excel file"
set WorkingDirectory to (choose folder with prompt "Please locate the folder with the processed .pdf's.") as string
set KeyFilePath to WorkingDirectory & KeyFileName
set FileNamesKey to my GetFileNamesKey(KeyFilePath)
set theResult to ChangeFileNames(WorkingDirectory, FileNamesKey)
return theResult
end run
to GetFileNamesKey(KeyFilePath)
tell application "Microsoft Excel"
set ResultsRange to used range of active sheet of active workbook
set FileNames to {}
repeat with i from 1 to count of rows in ResultsRange
set NewFileName to value of third column of row i of ResultsRange
set OldFileName to value of second column of row i of ResultsRange
set FileNames to FileNames & {{OldFileName, NewFileName}}
end repeat
return FileNames
end tell
end GetFileNamesKey
to ChangeFileNames(WorkingDirectory, FileNamesKey)
tell application "Finder"
repeat with aFile in FileNamesKey
set OldFileName to item 1 of aFile
set NewFileName to item 2 of aFile
set PathToFile to WorkingDirectory & OldFileName
try
set FileExtension to name extension of file (WorkingDirectory & (item 1 of aFile))
set name of file PathToFile to NewFileName & "." & FileExtension
on error
exit repeat
return false
end try
end repeat
end tell
return true
end ChangeFileNames
return input
end run
If I well understand, you have an excel file with col.A= #test, col.B= oldFileName (xx-yyy.pdf), col.C= NewName (empty?), Col.D= PartA (xx), Col.E= PartB (yyy)
You want to loop through all rows of that file, search if file from Col.B exists in a selected folder and if so, you want to change file's name (=colA + " " + old name) and make it visible in Excel row.
If OK, then script below does what you want. In case file is found in selected folder, I marked the Excel row in 2 ways: set new file name in column C (not sure if it was empty before ?) and I set its color to red. I added many comments to make it clear (I hope !) :
set KeyFileName to choose file with prompt "Choose the Excel file"
set WorkingDirectory to (choose folder with prompt "Please locate the folder with the processed .pdf's.") as string
tell application "Microsoft Excel"
open KeyFileName
set ResultsRange to used range of active sheet of active workbook
-- Data bloc starts row 2 with headers and then row 3 to x
-- col.A= #test, col.B= oldFileName (xx-yyy.pdf), col.C= NewName (empty), Col.D= PartA (xx), Col.E= PartB (yyy)
repeat with i from 2 to count of rows in ResultsRange
-- loop through each Excel row only starting row 2 of the range to skip hearders
set OldFileName to value of second column of row i of ResultsRange
set NewName to (value of the first column of row i of ResultsRange) & " " & OldFileName
set theFile to WorkingDirectory & OldFileName
tell application "Finder" to set FileExist to (file theFile exists) --check file exists
if FileExist then
tell application "Finder" to set name of file theFile to NewName --change file name in the folder
set (value of third column of row i of ResultsRange) to NewName -- set new name in Excel col.C
set (color index of interior object of third column of row i of ResultsRange) to 3 -- = Red !
end if
end repeat --- next Excel row
end tell
The line "Open KeyFileName" can be skipped is Excel file is already open. Also the script does not save the Excel file after the changes !