Applescript to copy row

Question

Level 1

20 points

Applescript to copy row

I have an Excel file that was output from FileMaker. It has 715 rows and 7 columns. I'm new to Applescript. I'd like to write a script that will to through the rows of the first column and if it finds data, duplicate the row and then removes all the info in the last 7 columns from the row. In other words, if there is the word "Text" in the first column, it would duplicate that row above the current row and delete the info in the last six columns. I would then end up with the word "Text" on a line by itself.

I've been able to open the file and copy the contents of the first cell but can't duplicate the row and delete the text.

Is there an easy way to accomplish this with Applescript?

Mac Pro, Mac OS X (10.6.7)

Posted on Nov 17, 2015 9:02 AM

Reply

Answer 1

Niftyix Author

Level 1

20 points

Nov 17, 2015 10:10 AM in response to Niftyix

BTW, I can insert a blank line above. I have the value of the cell I want to copy in "myVal". I just can't get it into the cell "A"&myRow&":A"&myCol.

Reply

Answer 2

Camelot

Level 9

54,333 points

Nov 17, 2015 10:43 PM in response to Niftyix

First off, Microsoft Excel is an absolute bear to script. Please do not use the upcoming negative sentiment you get as a reflection on AppleScript - it's all Excel's fault 🙂

Second, if you have a blank line and the value you want in a variable, you can set the cell value via something like:

set myValue to "Foo"

set myRow to 5

tell application "Microsoft Excel"

set value of cell ("$A$" & myRow as text) of worksheet 1 of workbook 1 to myValue

end tell

In your example it looks like you're trying to set a range, not an individual cell value.

Reply

Answer 3

Niftyix Author

Level 1

20 points

Nov 18, 2015 3:55 AM in response to Camelot

Thanks for the reply. I appreciate the help. I meant to type A"&myRow&":A"&myRow. I finally solved it. I didn't realize I had to put it in parens: set myVal to value of range ("A" & myRow & ":A" & myRow) works.

Reply