Hi all,
I have the below script which has worked perfectly for several months.
In summary the script is attached to a folder via Folder Actions and once a PDF is dropped into it the Script begins to interact with Excel. It essentially extracts the text in specific cells to form part of an Email body which is also part of the later sections of the script.
Now for some inexplicable reason it has began deleting the entire row 2 of the Excel doc it is interacting with every time it is run. I’ve isolated the offending block that seems to be doing it (highlighted below and placed between the comment block tags (/) below) but I cannot see where that is removing the entire row. Can anyone point to why it is (now) doing this?
on adding folder items to thisFolder after receiving DroppedFiles
– Prompt the user to enter a search string and assign it to the variable inputCode.
set inputCode to display dialog “Enter ASSET CODE” default answer “”
– Extract the text entered by the user from inputCode and assign it to the variable cAC.
set cAC to text returned of inputCode
– Activate Microsoft Excel.
tell application "Microsoft Excel”
set selection to null – deselect any cells to avoid copying issues
– Activate the active workbook and show all data on every sheet.
activate
tell every sheet of active workbook to show all data
delay 0.25
– Select the active sheet.
tell active sheet
– Set a timeout of 60 seconds for the following code to execute.
with timeout of 60 seconds
– Define a range of cells to clear and clear it.
set depositRge to "$FF$2:$FK$2”
clear range range depositRge
– Define a range of cells to search for the value in cAC and assign it to the variable cAsset.
set mCol to range "C2:C3000”
set cAsset to find mCol what cAC
– Identify the row of the first occurrence of the search string in the range of cells defined in the previous step and assign it to the variable rowRge.
set rowRge to row (first row index of cAsset)
– Define a range of columns to extract data from and assign it to the variable colRge.
set colRge to range "C:C,I:I,M:M,P:P,R:R”
– Find the intersection of the rowRge and colRge ranges and assign it to the variable ise.
set ise to intersect range1 rowRge range2 colRge
– Identify the top-left cell of the range depositRge and assign it to the variable aTemp.
set aTemp to cell 1 of range depositRge
– Copy the values from the ise range and paste them into the depositRge range starting from the top-left cell defined in the previous step.
copy range ise destination aTemp
– Activate the aTemp object and assign the current region of aTemp to the variable aTempcr.
activate object aTemp
set aTempcr to current region of aTemp
– Copy the values from aTempcr and store them in the clipboard as rich text format (RTF) using the variable clipRTF.
copy range aTempcr
set clipRTF to the clipboard
set clipRTF to (text 1 thru -1 of clipRTF)
set clipRTF to do shell script "echo " & quoted form of clipRTF & " | tr ‘[:lower:]’ '[:upper:]’”
set the clipboard to clipRTF
–select cell 13 of rowRge
set value of cell 23 of rowRge to "Ready For Approval”
– End the timeout block.
end timeout
– End the tell active sheet block.
end tell
– End the tell application "Microsoft Excel” block.
end tell
set myFile to item 1 of DroppedFiles
tell application “Finder” to set NameOfFile to name of myFile
set NameOfFile to text 1 thru ((offset of “.” in NameOfFile) - 1) of NameOfFile
try
set x to button returned of (display dialog NameOfFile & return & return ¬
& “Amend or a v1 Build?” buttons {“Amend”, “v1 Build”, “Cancel”} default button 2)
on error number -128
display notification "You Picked Cancelled”
return
end try
if x is “Amend” then
set mySubject to clipRTF & " > AMEND FOR APPROVAL”
set myContent to "
Dear all,
Please find attached artwork AMEND for approval for the below product/s;
" & clipRTF & "
Thank you”
else
set mySubject to clipRTF & " > v1 BUILD FOR APPROVAL”
set myContent to "
Dear all,
Please find attached artwork v1 BUILD for approval for the below product/s;
" & clipRTF & "
Thank you”
end if
tell application "Microsoft Outlook”
tell (make new outgoing message with properties {subject:mySubject, content:myContent})
make new attachment with properties {file:myFile}
make new recipient with properties {email address:{name:“Joe Bloggs”, address:"joebloggs@bloggs.co.uk”}}
open
end tell
activate
end tell
> -- Activate Microsoft Excel. > > (* tell application "Microsoft Excel” > > -- Activate the active workbook and show all data on every sheet. > > activate > > tell every sheet of active workbook to show all data > > delete cell "$FF$2:$FK$2” > > select cell 23 of rowRge > > set workingRange to range "W:W” > > set theStatus to get first column index of (find (workingRange) what "To Artwork”) > > autofilter range workingRange field theStatus criteria1 "To Artwork” > > end tell *) >tell application "Microsoft Outlook”
activate
end tell
– the below moves the PDF for approval to the relevant folder dependent on if a volume is mounted or not.
set mountedDiskName to “MacRAID”
set diskIsMounted to false
tell application “System Events” to set diskNames to name of every disk
if mountedDiskName is in diskNames then
set diskIsMounted to true
set createdFolder to "Macintosh HD:Volumes:MacRAID:Folders:”
tell application “Finder”
move files of entire contents of thisFolder to createdFolder with replacing
delay 1
delete files of entire contents of thisFolder
activate application “Finder”
open window of "Macintosh HD:Volumes:MacRAID:Folders:”
end tell
else
set createdFolder to "Macintosh HD:Users:MacUser:Desktop:WFH SYNC:Folders:”
tell application “Finder”
move files of entire contents of thisFolder to createdFolder with replacing
delay 1
delete files of entire contents of thisFolder
activate application “Finder”
open window of "Macintosh HD:Users:MacUser:Desktop:WFH SYNC:Folders:”
end tell
end if
end adding folder items to
end