AppleScript to filter and add data to Excel

Scripting Forums AppleScript | Mac OS X
#1

Hi all,
I’m trying to write a script that displays a dialogue which the user then adds a reference that will be located in an open Excel Spreadsheet. The script searches thru the cells in column C2:C3000 and filters to find the row containing the reference input by the dialog. Then, once the row is located for the script to show another dialogue where the user enters text - this is then deposited in Cell AM of the found row.

the below is as far as I can get using on line resources. I get it to compile but it simply doesn’t work. Any pointers greatly received.

tell application "Microsoft Excel"
activate
set myDialog to display dialog "Enter filter text for column C:" default answer ""
set filterText to text returned of myDialog
set activeSheet to active sheet
set columnC to range "C2:C3000" of activeSheet
select columnC
set hidden of column 3 of activeSheet to true
set visible of columnC to true
set autofiltermode to false
set autofiltermode to true
set filterRange to range "C:C" of activeSheet
select filterRange
set value of filterRange to filterText
set myDialog to display dialog "Enter text for cell AM:"
set cellText to text returned of myDialog
set AMCell to range "AM1" of activeSheet
set value of AMCell to cellText
set autofiltermode to false
set visible of columnC to true

end tell

#2 
tell application "Microsoft Excel"
	activate
	set findText to (input box prompt "Find this text: " type string)
	
	tell active sheet
		set colC to range "C2:C3000"
		with timeout of 10 seconds
			
			set foundCell to find colC what findText --  search for text
			set rowNum to first row index of foundCell -- row of found text
			
			set writeCell to get address of cell 39 of row rowNum -- cell 39 is column AM
			
			-- enter this text in cell AM of the found row
			set textWrite to (input box prompt "Deposit this text into cell AM" & rowNum & ": " type string)
			set value of cell writeCell to textWrite
			
		end timeout
	end tell
end tell
#3

Excellent once again - works like a dream, thank you!

1 Like
#4

Hi again,

Scratching my head with this one!

I’m trying to adjust your excellent scripts to suit a different purpose.

What I want is a dialog to pass a user input code to search Excel in the column range of M2:M3000 and find the matching code.

Then, once it’s found this, to copy the cells in the found row “C, I, J, M, P, R” to a safe area – A1000 to E1000 in the same workbook.

Select this new range and make this selection into a variable that I’m going to use in a different script elsewhere.

This is as far as I’ve got (without the variable steps).

The below script seems confused (like me) and selects the Cell at column C and row 367 which I can’t figure out why.

tell application "Microsoft Excel”

– bring the Excel window to the front

activate

– prompt the user to input some text to search for

set findText to (input box prompt "Find this text: " type string )

– tell AppleScript to interact with the active worksheet in Excel

tell active sheet

– set the variable “colM” to the range of cells in column M, from row 2 to row 3000

set colM to range "M2:M3000”

– set a timeout of 10 seconds for the following commands to execute

with timeout of 10 seconds

– search the “colM” range for the text specified in the “findText” variable, and store the result in “foundCell”

set foundCell to find colM what findText

– set “rowNum” to the row number of the first cell in the "foundCell” range

set rowNum to first row index of foundCell

– select the cell in column C of the found row

select cell 3 of row rowNum

– end the timeout

end timeout

– end the "tell active sheet” block

end tell

– end the "tell application” block

end tell

Any help greatly received.

#5

Try this:

I tweaked a little bit. When it creates the intersect range (ise), it simply uses the row it found the text in and the six columns you specified.

set inputCode to display dialog "Enter search string" default answer ""
set cAC to text returned of inputCode

tell application "Microsoft Excel"
	activate
	tell active sheet
		with timeout of 8 seconds
			set depositRge to "$A$1000:$F$1000" -- where to deposit the six cells' data
			clear range range depositRge -- clear range beforehand
			
			set mCol to range "M2:M3000" -- range to search in
			set cAsset to find mCol what cAC -- the string to search for, e.g. asset code
			set rowRge to row (first row index of cAsset) -- found row
			set colRge to range "C:C,I:I,J:J,M:M,P:P,R:R"
			
			set ise to intersect range1 rowRge range2 colRge
			--> range "[dataga4.xlsx]Sheet1!$C$6,$I$6:$J$6,$M$6,$P$6,$R$6"
			
			set aTemp to cell 1 of range depositRge
			copy range ise destination aTemp -- copy the six cells to "$A$1000:$F$1000"
			
			activate object aTemp
			set aTempcr to current region of aTemp
			--> "$A$1000:$F$1000"
			copy range aTempcr
			set clipRTF to the clipboard
			--> "F6338	icol	jcol	Find	pcol	rcol"
			
		end timeout
	end tell
end tell

By the way, if you want to have the script go to a particular cell (or range of cells), use activate object rather than select.

tell application "Microsoft Excel"
	activate object range "B12"
	activate object range "C3:D6"
end tell
#6

Superb once again! Thanks for the tip too!

#7

Question on the above.
Is there any reason or issues if I placed the temporary location on row 2 at these cells?

set depRge to "$FF$2:$FK$2"

The usual valuable data entered on the sheet stops at cell AM for all rows and it is highly unlikely it will extend any further right in future.
The reason I’m looking into this is I now have 4 scripts using variations on Mockman’s excellent solutions which have the temporary range located at:

set depRge to "$A$1000:$G$1000"

More entries are added every day (it’s basically a job tracker) and creeping ever closer to row 1000.
I was going to keep an eye on it and alter the range further down the sheet as entries get closer but there’s a danger I’ll forget (especially with multiple iterations of the script) and end up overwriting/clearing the data entered there.
I could instruct the admins to avoid row 1000 altogether but felt this is a little more behind the scenes resolution that doesn’t impact them.

I’ve altered and run the script and it operates as expected with no issues I can see - the cells are cleared after every run so it won’t be redundant data that others may filter/question about.

Any caveats or pitfalls I may be opening myself to?

Cheers for any input.

Regards

#8

I would say that there aren’t. The only negative is that it won’t be obvious when you open the spreadsheet and you’ll have to go look at it. But if your sheet is regularly evolving then it would likely be better to use permanently safe ranges.

In general, I try to stick such zones at the top of the spreadsheet and have the data start below it because then when I open the spreadsheet or go to the top, I can see the key data. On occasion, I have put it on the left and started the data in column K or AA (depending upon how much space I need). Obviously that won’t work in every situation so put it wherever it is convenient.

#9

It would be more work, but you could always copy your found data to a hidden worksheet and use it from there. That way, you wouldn’t have to worry about ever running into the copied data on the main sheet, no matter how many rows or columns it may have in the future.

Or, depending on your data, it’s possible you could even store it in a named item and use that instead of putting it into a cell on the sheet you are trying to do work on.

#10

Thanks - I’ve been using it for a couple of days with no issues.

#11

I did think about using another sheet but the document isn’t “owned” by me so adding things might cause other issues for the wider team. Plus, as you say it would complicate the steps - this is working fine as far as I can see with now issues. -thanks for your response tho.