Hi, I am trying to Copy data from worksheets “From” to “TransferTo” which cud be in a different workbook. (TransferTo is main workbook, and get new info comes in “From”, sometimes data is duplicate, hence filter the “From” worksheet based on date data in column1
Both worksheets Column1 has date formatted as dd-mm-yyyy
“TransferTo” date is sorted Oldest to newest. Script sorts “From” column1 oldest to newest, then delete matching dates of the 2 worksheets in “From”
(In worksheet “From”, I want to select rows with date > then the newest date in “TransferTo”), then paste at first empty cell in column A of “TransferTo”
Not working as commented in the script, any suggestion wud be great
tell application "Microsoft Excel"
activate object worksheet "TransferTo"
set LastRow to (first row index of (get end (last cell of column 1) direction toward the top))
set LastCell to ("A" & LastRow) as text -- in Column 1 or A
-- Get latest date
set recentDate to value of range LastCell
set FirstEmptyCell to ("A" & (LastRow + 1)) as text
activate object worksheet "From"
-- Sort column1 date, oldest to newest
tell worksheet "From" to sort used range key1 column 1 order1 sort ascending header header yes
set usedRows to every row of (used range of active sheet) whose value is not missing value
-- I cud not think a way to select only rows with dates > then recentDate, hence the hack to delete rows, and then select the left data, this also fails intermittant, dont know why :(
repeat with aRow in usedRows
if (value of row (first row index of aRow) of column (first column index of aRow) is less than or equal to recentDate) then
delete aRow
end if
end repeat
set copyData to value of used range of worksheet "From"
--set dataRowCount to count of rows -- this gives a big number ? not being a clean worksheet
set dataRowCount to (first row index of (get end (last cell of column 1) direction toward the top))
activate object worksheet "TransferTo"
tell used range
-- get info about used range of worksheet
set lastRowIndex to (count of rows)
if lastRowIndex = 1 then set lastRowIndex to 0
-- Struggling to get Column reference to set up range to paste, want it a generic solution where no of columns cud vary when processing different files
set cour to columns of used range
set LastCol to last item of cour
end tell
if dataRowCount > 0 then
-- this does not compose !! want it like A20:J22
set insertPoint to range ("A" & lastRowIndex + 1 & ": LastCol" & dataRowCount + lastRowIndex)
set value of insertPoint to copyData
end if
end tell
You don’t include any data layout so I’m forced to guess but the following script will work with spreadsheets that look like these below. Note that they should be sorted on the date column, and dates should not contain time components (that resolve to anything but 0).
range “A1:B4” of sheet “Transferto”
[format]19-Oct to data
20-Oct to data
24-Oct to data
29-Oct to data[/format]
range “A1:B4” of sheet “From”
[format]28-Oct from data
29-Oct from data
31-Oct from data
01-Nov from data[/format]
Based on the above sample, expect ‘From’ range “A3:B4” to be copied to range “A5:B6” of sheet 'TransferTo, resulting in this:
range “A1:B6” of sheet “Transferto”
[format]19-Oct to data
20-Oct to data
24-Oct to data
29-Oct to data
31-Oct from data
01-Nov from data[/format]
tell application "Microsoft Excel"
with timeout of 20 seconds
tell workbook 1
activate object worksheet "TransferTo"
tell sheet "Transferto"
set ursaTo to used range -- of sheet 'To"
set cour to column 1 of ursaTo -- date column
set name of cour to "nCourTo" -- named range for cells of date column
-- set destination range
set desRange to range ("A" & (first row index of (last cell of column 1 of ursaTo)) + 1)
--> cell "A5" of sheet "Transferto" of workbook 1 of application "Microsoft Excel"
-- get largest To date
set lrgTo to (evaluate name "=MAX(nCourTo)") as integer
--> 44498
end tell
end tell
activate object worksheet "From"
tell sheet "From"
set ursaFr to used range -- of sheet 'From'
set cour to column 1 of ursaFr -- date column
set name of cour to "nCourFr" -- named range for cells of date column
-- in sheet From, get first From row with greater date
set firstRow to (evaluate name "=match(max(ncourto),ncourfr,1)+1") as integer
--> 3 (i.e. row 3 of From > last row of To)
-- get largest From date
set lrgFr to (evaluate name "=MAX(nCourFr)") as integer
--> 44501
-- set source range
set bRow to count of rows of ursaFr
if lrgFr is greater than lrgTo then -- i.e. new data exists
set cRows to firstRow & ":" & bRow as text
set srcRange to intersect range1 ursaFr range2 cRows
--> range "From!$A$3:$B$5"
set crFlag to 1
else -- i.e. no new data
tell me to display dialog "No new data"
set crFlag to 0
end if
end tell
activate object worksheet "TransferTo"
tell sheet "Transferto"
if crFlag is 1 then -- new data exists
copy range srcRange destination desRange
end if
end tell
end timeout
end tell
Basically, it identifies the max value in sheet ‘TransferTo’ and finds the first sheet ‘From’ cell with a newer date. (Hopefully, the MATCH function will work reliably here.) It grabs the cells of the rows from the newer date on down and then copies them below the max date on sheet TransferTo.
If you run the script twice in a row, or if there is otherwise no new data, then it will advise that no new data is available. If your second sheet is in a separate workbook, then you’ll have to modify the script accordingly. The date ‘format’ shouldn’t matter as long as it is an excel date and not a string.
Thank you Mockman, script works great. I have tried to mod the script to work with 2 workbook, with option to select sheet but it throws error
The whole script
RecentWorkbook → Book1 on Desktop (Selected) → worksheet From
FinalWorkbook → Book2 on Desktop → Several worksheet
tell application "Finder" to set RecentWorkbook to selection as alias --
set FinalWorkbook to "Catalina:Users:one:Desktop:Book2.xlsx" as alias
tell application "Microsoft Excel"
with timeout of 20 seconds
open FinalWorkbook
-- Select worksheet to work with
set mlist to name of every worksheet of FinalWorkbook
set chosenOption to choose from list of mlist with prompt "which WorkSheet, please select:" default items ""
if chosenOption is false then
error number -128 (* user cancelled *)
else
set chosenWorksheet to chosenOption's item 1 (* extract choice from list *)
end if
tell workbook FinalWorkbook
activate object worksheet chosenWorksheet of workbook FinalWorkbook -- →Error
tell sheet chosenWorksheet
set ursaTo to used range -- of sheet 'To"
set cour to column 1 of ursaTo -- date column
set name of cour to "nCourTo" -- named range for cells of date column
-- set destination range
set desRange to range ("A" & (first row index of (last cell of column 1 of ursaTo)) + 1)
--> cell "A5" of sheet "Transferto" of workbook 1 of application "Microsoft Excel"
-- get largest To date
set lrgTo to (evaluate name "=MAX(nCourTo)") as integer
--> 44498
end tell
end tell
open RecentWorkbook
tell workbook RecentWorkbook
activate object worksheet "From" --of workbook RecentWorkbook
tell sheet "From"
set ursaFr to used range -- of sheet 'From'
set cour to column 1 of ursaFr -- date column
set name of cour to "nCourFr" -- named range for cells of date column
-- in sheet From, get first From row with greater date
set firstRow to (evaluate name "=match(max(ncourto),ncourfr,1)+1") as integer
--> 3 (i.e. row 3 of From > last row of To)
-- get largest From date
set lrgFr to (evaluate name "=MAX(nCourFr)") as integer
--> 44501
-- set source range
set bRow to count of rows of ursaFr
if lrgFr is greater than lrgTo then -- i.e. new data exists
set cRows to firstRow & ":" & bRow as text
set srcRange to intersect range1 ursaFr range2 cRows
--> range "From!$A$3:$B$5"
set crFlag to 1
else -- i.e. no new data
tell me to display dialog "No new data"
set crFlag to 0
end if
end tell
end tell
activate object worksheet chosenWorksheet of workbook FinalWorkbook
tell sheet chosenWorksheet
if crFlag is 1 then -- new data exists
copy range srcRange destination desRange
end if
end tell
end timeout
end tell
I have not installed the Excel, but as I see, your script contains complete confusion with aliases and workbooks.
In addition, in the Excel dictionary, as I recall, it is recommended to use the keyword “file” with the addition of the HFS path to open files. As I said, I cannot test, so fix the possible errors in my version of the script yourself.
tell application "Finder" to set RecentWorkbookHFS to selection as text --
set FinalWorkbookHFS to "Catalina:Users:one:Desktop:Book2.xlsx"
tell application "Microsoft Excel"
with timeout of 20 seconds
set FinalWorkbook to open file RecentWorkbookHFS
-- Select worksheet to work with
set mlist to name of every worksheet of FinalWorkbook
set chosenOption to choose from list of mlist with prompt "which WorkSheet, please select:" default items ""
if chosenOption is false then error number -128 (* user cancelled *)
set chosenWorksheet to chosenOption's item 1 (* extract choice from list *)
tell FinalWorkbook
activate object worksheet chosenWorksheet -- →Error
tell sheet chosenWorksheet
set ursaTo to used range -- of sheet 'To"
set cour to column 1 of ursaTo -- date column
set name of cour to "nCourTo" -- named range for cells of date column
-- set destination range
set desRange to range ("A" & (first row index of (last cell of column 1 of ursaTo)) + 1)
-- get largest To date
set lrgTo to (evaluate name "=MAX(nCourTo)") as integer
end tell
end tell
set open RecentWorkbook to open file RecentWorkbookHFS
tell RecentWorkbook
activate object worksheet "From" --of workbook RecentWorkbook
tell sheet "From"
set ursaFr to used range -- of sheet 'From'
set cour to column 1 of ursaFr -- date column
set name of cour to "nCourFr" -- named range for cells of date column
-- in sheet From, get first From row with greater date
set firstRow to (evaluate name "=match(max(ncourto),ncourfr,1)+1") as integer-- get largest From date
set lrgFr to (evaluate name "=MAX(nCourFr)") as integer
-- set source range
set bRow to count of rows of ursaFr
if lrgFr is greater than lrgTo then -- i.e. new data exists
set cRows to firstRow & ":" & bRow as text
set srcRange to intersect range1 ursaFr range2 cRows
set crFlag to 1
else -- i.e. no new data
tell me to display dialog "No new data"
set crFlag to 0
end if
end tell
end tell
activate object worksheet chosenWorksheet of FinalWorkbook
tell sheet chosenWorksheet of FinalWorkbook
if crFlag is 1 then -- new data exists
copy range srcRange destination desRange
end if
end tell
end timeout
end tell
NOTE: not sure but choose from list command most likely needs tell scripting additions to choose from list…
Hi KniazidisR
Thank you for looking at it
You are right About Ms Excel, for my reference & correct me
it uses workbook file name = Full path to the document
workbook name = Identity of open document
I think paths seems fine in the updated script, but get
which I believe is incorrect reference to the named column in chosenworksheet in Final
how do I do
set FinalWorkbookFile to "Catalina:Users:one:Desktop:Final.xlsx" as alias
set FinalWorkbookName to name of (info for FinalWorkbookFile)
set RecentWorkbookFile to "Catalina:Users:one:Desktop:Recent.xlsx" as alias
set RecentWorkbookName to name of (info for RecentWorkbookFile)
tell application "Microsoft Excel"
with timeout of 20 seconds
open FinalWorkbookFile
--Set refernce to workbook
set FinalWorkbook to workbook FinalWorkbookName
-- Select worksheet to work with
set mlist to name of every worksheet of FinalWorkbook
set chosenOption to choose from list of mlist with prompt "which WorkSheet, please select:" default items ""
if chosenOption is false then
error number -128 (* user cancelled *)
else
set chosenWorksheet to chosenOption's item 1 (* extract choice from list *)
end if
tell FinalWorkbook
activate object sheet chosenWorksheet
tell sheet chosenWorksheet
set ursaTo to used range -- of sheet chosenWorksheet
set cour to column 1 of ursaTo -- date column
set name of cour to "nCourTo" -- named range for cells of date column
set name of (info for FinalWorkbook) to "FinalWorkbookName.xlsx" --? helpful
-- set destination range
set desRange to range ("A" & (first row index of (last cell of column 1 of ursaTo)) + 1)
-- get largest To date
set lrgTo to (evaluate name "=MAX(nCourTo)") as integer
end tell
end tell
open RecentWorkbookFile
--Set refernce to workbook
set RecentWorkbook to workbook RecentWorkbookName
tell RecentWorkbook
activate object sheet 1
tell sheet 1
set ursaFr to used range -- of sheet 'From'
set cour to column 1 of ursaFr -- date column
set name of cour to "nCourFr" -- named range for cells of date column
-- get largest From date
set lrgFr to (evaluate name "=MAX(nCourFr)") as integer
-- set source range
set bRow to count of rows of ursaFr
-- in sheet chosenWorksheet, get first From row with greater date
set firstRow to (evaluate name "=match(max(range \"FinalWorkbookName.xlsx!ncourto\"),ncourfr,1)+1") as integer --> This does Not work!!
if lrgFr is greater than lrgTo then -- i.e. new data exists
set cRows to firstRow & ":" & bRow as text
set srcRange to intersect range1 ursaFr range2 cRows
set crFlag to 1
else -- i.e. no new data
tell me to display dialog "No new data"
set crFlag to 0
end if
end tell
end tell
tell FinalWorkbook
activate object worksheet chosenWorksheet
tell sheet chosenWorksheet
if crFlag is 1 then -- new data exists
copy range srcRange destination desRange
end if
end tell --worksheet chosenWorksheet
end tell -- FinalWorkbook
end timeout
end tell
I don’t know what their current position is but once upon a time, microsoft recommended using ‘open workbook’ when opening a single workbook. This approach allows you to assign the opened workbook to a variable, which simplifies many things. Of course, things change over time so perhaps this doesn’t work in modern versions of the OS.
set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"
set theWorkbookName to name of (info for theWorkbookFile)
tell application "Microsoft Excel"
open theWorkbookFile
set theWorkbook to workbook theWorkbookName
end tell
--> workbook "My Workbook.xls" of application "Microsoft Excel"
First, there is a lot to digest here so for the moment I’ll just advise that there are distinct ways to refer to a file and a workbook, even if they are both ultimately the same item. When working with a workbook within excel it should typically be referenced with its name or index, e.g. workbook “RecentlyFrom1.xlsx”, workbook 1.
‘workbook’ should not be combined with ‘alias’ as excel/applescript can’t connect these two concepts and so cannot get the sheets (or anything else).
Working with the workbook object yields these examples:
[format] sheets of workbook “RecentlyFrom1.xlsx”
activate object workbook "RecentlyFrom1.xlsx"
sheets of workbook 1[/format]
Of course, you can use variables, which can be set along with the ‘open workbook’ command (or independently):
[format] set rf1 to open workbook workbook file name ((path to desktop) as text) & “RecentlyFrom1.xlsx”
sheets of rf1
→ {worksheet “From” of workbook “RecentlyFrom1.xlsx”}[/format]
Finally, a question: does the ‘evaluate’ command work for you? Open your ‘final’ workbook and leave it on the ‘transfer’ sheet and run this script snippet (of course, the cells in column A should contain valid dates). I’m not sure whether this works with every combination of macOS and Excel versions. While it sounded like it did (within the same workbook), if it doesn’t then you’ll need to take another approach.
[format]tell application “Microsoft Excel”
set courTo to column 1 of used range
set name of courTo to “nCourTo” – named range for cells of date column
set ev to evaluate name “=MAX(ncourTo)”
→ 4.4498E+4
end tell[/format]
It should result in an excel date with the above notation.
That’s good to see. So it seems that you can use excel’s ‘max’ function to identify the most recent date. Now it’s just a matter of getting it to work across different workbooks.
FYI, if you add ‘as integer’ the scientific notation will change.
Thanks Mockman & KniazidisR for looking at script and helpful suggestions
I think I have a working script. It errors out if chosen worksheet in FinalWorkbook is empty
plz suggest if anything can be done to improve it
Thank you
set FinalWorkbookFile to "Catalina:Users:one:Desktop:Final.xlsx" as text
tell application "Finder" to set RecentWorkbookFile to selection as text
tell application "Microsoft Excel"
set FinalWorkbook to open workbook workbook file name FinalWorkbookFile
set FinalWorkbookName to name of FinalWorkbook
set mlist to name of every worksheet of FinalWorkbook
set chosenOption to choose from list of mlist with prompt "which WorkSheet, please select:" default items ""
if chosenOption is false then
error number -128 (* user cancelled *)
else
set chosenWorksheet to chosenOption's item 1 (* extract choice from list *)
end if
activate object sheet chosenWorksheet
tell sheet chosenWorksheet
set ursaTo to used range -- of sheet chosenWorksheet
set cour to column 1 of ursaTo -- date column
set name of cour to "nCourTo" -- named range for cells of date column
-- set destination range
set desRange to range ("A" & (first row index of (last cell of column 1 of ursaTo)) + 1)
-- get largest To date
set lrgTo to (evaluate name "=MAX(nCourTo)") as integer
end tell -- sheet chosenWorksheet
set RecentWorkbook to open workbook workbook file name RecentWorkbookFile
activate object sheet 1
tell sheet 1
sort used range key1 column 1 order1 sort ascending header header yes
set ursaFr to used range -- of sheet 'From'
set cour to column 1 of ursaFr -- date column
set name of cour to "nCourFr" -- named range for cells of date column
-- get largest From date
set lrgFr to (evaluate name "=MAX(nCourFr)") as integer
-- set source range
set bRow to count of rows of ursaFr
-- in sheet chosenWorksheet, get first From row with greater date
set firstRow to (evaluate name "=match(MAX('[" & FinalWorkbookName & "]" & chosenWorksheet & "'!nCourTo),ncourfr,1)+1") as integer
if lrgFr is greater than lrgTo then -- i.e. new data exists
set cRows to firstRow & ":" & bRow as text
set srcRange to intersect range1 ursaFr range2 cRows
set crFlag to 1
else -- i.e. no new data
tell me to display dialog "No new data"
set crFlag to 0
end if
end tell -- sheet 1
tell FinalWorkbook --FinalWorkbookName
activate object worksheet chosenWorksheet
tell sheet chosenWorksheet
if crFlag is 1 then -- new data exists
copy range srcRange destination desRange
end if
end tell --worksheet chosenWorksheet
end tell
end tell
