Hi, I was wondering if anyone could help me with this… I’m trying to get Applescript to autofill text from the first cell to the last row of another column. I can get the autofill to work, but the problem is that I have an undefined number of rows and I’m not sure how to reference that in the script itself. I’m using the repeat function on a different piece of the script to copy and paste, so each time it copies in the first part, I want it to autofill to the bottom, and then repeat the process.
Does anyone know how to set autofill to work with an undefined number of rows? Any help would be greatly appreciated.
Thanks,
R
Perhaps something like this
tell application "Microsoft Excel"
set lastRow to first row index of (get end range "B65536" direction toward the top)
fill down (get resize range "A1" row size lastRow)
end tell
The problem is that I need to copy more than just the top number in the row. I have ID numbers that match with certain records and which need to be associated with certain columns. So for example, record 1 would have the ID number 40316 and the corresponding record would have “Spanish” next to it. The second would have ID number 51426 and would have a corresponding field that said “English” next to it.
The fill down only gives me the first number in the list.
Could you describe a small example?
What data would you have in what cell addresses?
Where would you want it filled to?
What would the filled to cells hold?
Have you looked at the Auto Fill command?
Thanks for checking back.
I did actually use autofill and it sort of works. It fills to the bottom row of the data (and thanks for that, too!), but it when it copies down, the numbers change. Assuming I have an Excel spreadsheet with the following two fields as columns: ID and Course Taken. I have two ID numbers for two students, and each student has taken three classes each. I have the data coming in as ID - Course (Student 1), ID - Course (Student 2). Then below that, each course is listed without an ID number, alternating. For example:
ID Course
40125 English 112
10632 Spanish 302
Astronomy 101
Math 155
The problem is that I actually have an unknown number of students, and when I use autofill, the numbers change instead of copying the same ID numbers. The courses will always alternate, so the third record in the Course field will belong to student 1, and the fourth to student 2, and so on. I need to get it to autofill their IDs to the end of the records. Now, I can get it to autofill, but again, it doesn’t copy the IDs exactly, and I am not sure how to make it work so that I can have an undefined number of rows. Does that make it a little bit clearer?
Never mind. I got the fill copy command to work. I just don’t know how to reference an unspecified range in the first part? Because I will have an undefined number of IDs as this progress, so putting in “A2:A45” will only copy the first forty-five records. Eventually, I may have a few hundred.
The type argument will control if the data is incrimented. If your ID numbers are in A2:A3, try this.
The second part shows how to get an unspecifed range. First find the end point (via get end) and then resize (the -1 on the row size argument is to account for the A2 starting point.
tell application "Microsoft Excel"
autofill range "A2:A3" destination range "A2:A11" type fill copy
-- to get unspecifiec range
set myRange to get resize range "A2" row size ((first row index of (get end range ("A2") direction toward the bottom)) - 1)
return get address myRange
-- "$A$2:$A$11"
end tell
It’s this part
autofill range "A2:A3" destination range "A2:A11" type fill copy
that’s the problem. I need the “A2:A3” to be the unspecified part, because it’s the part that I don’t know. There will be a different number of records every time I run the script, and if I put in the cell references only for the data I have, then it only copies those records.
This assumes that the header “ID” is in cell A1 and “Course” is in B1.
It
-
sets sourceRange to all the cells in column A (less the header) This is A2:A3 in the example in Post #5.
-
sets destinationRange to the cells in column A that have data in the col B cell of the same row. This is A2:A5 from the example.
-
compares the size of the source and destination ranges, if source is larger then error message, if the same do nothing, if source is smaller then autofill.
tell application "Microsoft Excel"
-- source starts at A2
set startCell to get range "a2" of worksheet "Sheet1" of workbook "Workbook1"
-- column B will determine how far to fill
set sizingCol to get offset startCell column offset 1
-- startRange is all cells with data starting at startCell
set startRange to get resize startCell row size ((first row index of (get end startCell direction toward the bottom)) - ((first row index of startCell) - 1))
-- destinationRange is rows in startCell column that have data in sizing column
set destinationRange to get resize startCell row size ((first row index of (get end sizingCol direction toward the bottom)) - ((first row index of sizingCol) - 1))
if (count of rows of destinationRange) < (count of rows of startRange) then
display dialog "Data size Mismatch" & linefeed & "A is shorter than B"
else if (count of rows of destinationRange) = (count of rows of startRange) then
-- same sized ranges, do nothing
else
-- range sizes OK, do autofill
autofill startRange destination destinationRange type fill copy
end if
end tell
From the example data, this will return
ID Course
40125 English 112
10632 Spanish 302
40125 Astronomy 101
10632 Math 155
I’m not sure if I’ve properly interpreted which ranges you want to go where, but this shows how Get End, Get Resize and Get Offset can be used to make variable sized ranges.
That didn’t work for me for some reason, but I did find something that did. I used the code:
set lastRow1 to (first row index of (get end range "AN100000" direction toward the top))
set lastRowNum1 to first row index of (get end (cell 1 of column 39 of row (count rows)) direction toward the top)
autofill range ("AM1:AM" & lastRowNum1) destination (get resize range "AM1" row size lastRow1) type fill copy
Except that now I have an additional problem. I am actually copying columns AO to AX individually and pasting them beneath column AN, then autofilling column AM. The range “AO:AX” is downloaded from a form that students fill out and then submit. There are 29 options the students can select, but they rarely (if ever) select all of them. The autofill will not work if the last columns in the range are blank. Excel then kicks back an error message. My code is as follows:
tell application "Microsoft Excel"
repeat with i from 41 to 68
select column i
tell application "System Events"
keystroke "c" using {command down}
keystroke "Purpose"
end tell
set TargetRange to column 40 of the active sheet
set AfterCell to cell "AN1" in the active sheet
set FoundRange to find TargetRange what "" after AfterCell look in values look at whole search direction toward the top
select FoundRange
tell application "System Events"
keystroke "v" using {command down}
end tell
set lastRow1 to (first row index of (get end range "AN100000" direction toward the top))
set lastRowNum1 to first row index of (get end (cell 1 of column 39 of row (count rows)) direction toward the top)
autofill range ("AM1:AM" & lastRowNum1) destination (get resize range "AM1" row size lastRow1) type fill copy
end repeat
end tell
Now I’m not sure how to make it autofill if some of the last columns (for example, columns 67 and 68) are empty. Any ideas?
It looks like what you are doing (in pseudo-script) is
repeat with varColumn from Column AO to BP
…Copy contents of varColumn
…Paste to the bottom of Column AN
end repeat
fill copies of AM until the new bottom of AN
Two things,
-
in your script, if you swap the lastRowNum1 and lastRow1, I think it will work. In AutoFill, the destination has to be larger than the range.
-
the selecting and use of keystroke is not needed. Selecting just slows things down. Also, I don’t think that the autofill needs to be inside the loop. Try this
*** My error see next post ***
If you are dealing with cells that have formulas, rather than cells with constants, that may cause errors when formulas at the bottom of columns return a “” value. Your working with imported data suggest that this is not the case.
The script posted above has a flaw, the destination of the copy statement should be offset downward one row.
This corrects that and centralizes the hard coded values.
tell application "Microsoft Excel"
set copyToColumn to 40 -- copy stuff to column AN
set fillDownColumn to 39 -- fill down in column AM
repeat with colNum from 41 to 68
set columnOfCells to get resize (cell 1 of column colNum) row size (first row index of (get end (cell -1 of column colNum) direction toward the top))
copy range columnOfCells destination (get offset (get end (cell -1 of column copyToColumn) direction toward the top) row offset 1)
end repeat
set lastRowAM to first row index of (get end (cell -1 of column fillDownColumn) direction toward the top)
set lastRowAN to first row index of (get end (cell -1 of column copyToColumn) direction toward the top)
autofill (get resize (cell 1 of column fillDownColumn) row size lastRowAM) ¬
destination (get resize (cell 1 of column fillDownColumn) row size lastRowAN) ¬
type fill copy
end tell
Thank you for that. It does exactly the same thing but with cleaner code!
I’m still having the same problem, however. If there’s nothing in columns 60 through 68, for example, it kicks back an error message saying that the range doesn’t understand the autofill command. There are that many possible options that they could check, so I need the code to include those columns in the event that someone does select them, but often, they will probably be empty.
Any suggestions for making this work even though the columns may be empty?
That is odd.
Are there formulas in columns 41-68 of the original data, and the “blanks” in the last columns are formulas that evaluate to “”?
try replacing the copy range statemtent at the end of the repeat loop to
set value of (get resize (get offset (get end (cell -1 of column copyToColumn) direction toward the top) row offset 1) row size (count of rows of columnOfCells)+ to value of columnOfCells
This was done on a PC at work and may contain syntax errors. The idea is to transfer the values rather than copy the cells (along with thier formatting and formulas). (If formatting isn’t an issue and there are no formulas, this approach is faster than using copy range.)
I got it to work. Thanks so much!
One last question: Does anyone know the command to make all this happen without actually launching Excel? I want it to do all of this without the program launching. I saw it somewhere, and now I can’t find it.
AFAIK, you have to have an Excel file open when changing cells’ contents.
You might be able to make Excel invisible for this use.
open application "Microsoft Excel"
tell application "System Events" to set visible of process "Microsoft Excel" to false
tell application "Microsoft Excel"
-- your script
end tell
quit application "Microsoft Excel" saving no -- saving should be handled in your script
Another option is setting Excel’s application screen updating to false, which will speed things up.
Note: unlike in Excel VBA, this must be explicitly reset to true, including error situations.
tell application "Microsoft Excel"
set screen updating to false
-- your script
set screen updating to true
end tell
Thanks. I will try it tomorrow and see what happens… If I can’t make it invisible, it’s not a big deal. I really appreciate all your help!