I created a hot key to insert text in Excel. This the text.
This worked great for last 30 or so days until today. Now it is inserting the text in cell A1 only instead of across all columns, A1 - H1. How could I update this so that it inserts the correctly?
I don't have Excel, but I have Numbers, and I've seen this happen before. What caused it was (a) when the cell is selected with a single click, everything worked perfectly as you want it, but (b) when I clicked inside the cell, and got the flashing text cursor, I had the same problem as you, where it was inserting all the text in a single cell.
So if Excel and Numbers work similarly, that could be your problem. You may want to test this out.
Rather than use an Insert Text action, why not use an AppleScript to just dump the text into the appropriate cells?
tell application "Microsoft Excel"
set value of range "A1:G1" to {"ShipTo_Name", ¬
"ShipTo_AddressLine1", ¬
"ShipTo_AddressLine2", ¬
"ShipTo_AddressLine3", ¬
"ShipTo_City", ¬
"ShipTo_StateProvinceCode", ¬
"ShipTo_PostalCode"}
end tell
Either a) change your action to "Insert text by typing" instead of "by pasting", or b) make sure cell A1 is selected but not activated (i.e. doesn't have a flashing insertion point inside it) when you run the macro.
This worked and I had tried this, several times, and the no matter how I wrote the script, it would not execute. It just occurred to me maybe why. Some of these lists come to us as tables. I may have been trying to execute this on of those examples. It was about month ago so can't remember.
Thank you the suggestion. It forced me to try it again.
What would be the script if I want to write to the first empty line after a contiguous block? (Add new line to an existing sheet)
If your table didn't have blank lines, you could just go to the bottom with a keystroke and then add a new line.
If your table does have blank lines, I think there's a way to remove all blank lines, after which you can go to the bottom with a single keystroke and add a new line.
If you don't want to delete blank lines, then you may have to resort to AppleScript or to a different spreadsheet app which can do this function.
Of course, there's a 50% chance my advice is wrong.
If it's after a contiguous block, and you can click in the first cell of the continuous block, send Command-Down Arrow and then Down Arrow, and you'll be in the first blank cell below the contiguous block.
-rob.
I'm useless and manipulating Excel cell ranges, so this is a bit clunky. And it does rely on the cells you want to fill being the first empty ones as you work down the column (as it is in your picture).
But say you want to add
4 Alice Boterkoek
...to the first empty row:
set theValues to {"4", "Alice", "Boterkoek"}
tell application "Microsoft Excel"
tell active sheet of document 1
set theRange to range "A:A"
set theRow to first row index of (find theRange what "" after (cell 1 of theRange) look in values search direction search next)
repeat with i from 1 to count of theValues
set value of cell i of row theRow to item i of theValues
end repeat
end tell
end tell
Like that, but more generic: without the specific value of “4”
It's only adding the 4 because 4 is the first item in the list theValues. Set that list to whatever (and however many items) you want and it will put them into the row, one item per cell, in that same order.
The list can be string literals, AS calculations, variables set from KM variables, etc. Give us a clue as to where the data is coming from and we can show you how to include it.
Getting the used range doesn't always work for various reasons.
Starting at the top and moving down can run into problems if you have blank rows.
So the way I would do it is this:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
to findLastRow(usingColumn)
-- check how we were given the column
if class of usingColumn is text then
-- if text, then we use a built-in formula to convert to an integer
tell application "Microsoft Excel"
tell active sheet of active workbook
-- use some error handling in case usingColumn doesn't
-- refer to a valid column
try
-- we get the column number for a range address constructed from usingColumn
-- e.g., if usingColumn = A, then we use range A:A
set indexColumn to (evaluate name "=COLUMN(" & usingColumn & ":" & usingColumn & ")") as integer
on error
-- if we run into problems, just default to column 1
set indexColumn to 1
end try
end tell
end tell
else if class of usingColumn is integer then
-- Excel allows a max of 16384 columns
if usingColumn > 16384 then
-- so if we don't get a valid number, default to column 1
set indexColumn to 1
else
-- otherwise, use the number we've been given
set indexColumn to usingColumn
end if
end if
(*
now, the meat...
we start at the very bottom row in the worksheet,
which is usually row 1048576
then we jump up to the last value populated in our index column
this will avoid hitting any blank rows in our data
*)
tell application "Microsoft Excel"
set lastRow to get end (cell indexColumn of row (count of rows)) direction toward the top
end tell
return lastRow
end findLastRow
to findNextRow(usingColumn)
-- get our current last row
set lastRow to findLastRow(usingColumn)
tell application "Microsoft Excel"
tell active sheet of active workbook
-- and then move down one row
set nextRow to get offset lastRow row offset 1
end tell
end tell
-- nextRow is where we can add our data
return nextRow
end findNextRow
on run
set dataToAdd to {"4", "Alice", "Boterkoek"}
-- find the next row where data can be added
set nextRow to findNextRow("A")
-- set nextRow to findNextRow(1) would also work
tell application "Microsoft Excel"
-- resize nextRow to match the number of data items we have
set nextRow to get resize nextRow column size (count of dataToAdd)
-- and set the value!
set value of nextRow to dataToAdd
end tell
end run
Oooh -- nice!