Microsoft excel Filter and copy data to different worksheet

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.

Browser: Firefox 95.0
Operating System: macOS 10.12

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 :frowning:

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.

Excel-2004 applescript reference, p14

Browser: Firefox 95.0
Operating System: macOS 10.12

Hi Mockman, Thank u for quick reply
I looked up here
http://preserve.mactech.com/articles/mactech/Vol.23/23.02/2302AppleScript/index.html

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"

A couple of things…

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.

Browser: Firefox 95.0
Operating System: macOS 10.12

That’s equally valid but obviously, requires more code and also wouldn’t let you use any of the ‘open workbook’ options.

What you want to have after running those commands is an open workbook that you can work with, which you have either way.

Result of above evaluate code is → 4.327E+4

Thank you

[format]4.327E+4 = 43270 = 2018-06-19[/format]

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