You only need to press the return key and a brown box/button like what you see will appear. The word limit to how much can be loaded on each button and the size of the brown button can be pre-adjusted. After it is copied, then KBM can be told to press the return button and return to the next cell in Excel, or use the down key to move the cursor down.
I have a long list of items in an Excel spread sheet (a column) that I need to sort. I could mark each item with a different number or letter in the cell next to it, but sorting is a touchy feelly process. You look at each pile and try to figure out the reason why the pile exists.
Each of those buttons can be easily moved around, so it is like sorting small cards.
If I had a hint as to how to get KBM to move down the column copying each cell then pasting to Tinderbox, then I would do the sorting by hand later. I think I have to locate the cursor on a pre-set Excel cell, select it, then move to Tinderbox. What I don't understand is how can I get KBM to recognize the preset cell that I want it to recognize.
Tinderbox is a very cool program. I don't know enough about programing to make proper use of it, but if I did, it would be even more fun to use. It is like KBM in that it is very flexible. In any case, thanks to people like you, I am learning.
Ellenm, I think there may be some confusion about the word "sort".
The normal definition of sort is to order the list by some column/field, like "Title" or "Date Created".
However, your usage suggests to me that, by "sort", you mean "organize" into categories.
List sorting, as I use it, is NOT a "touchy feelly process". It is a very precise, mathematical process, which Excel does very well.
So before I go any further, perhaps it would be best to clarify exactly what you mean by "sort" the list of items you have in Excel.
Having said that, my immediate thought is that you can probably do most of what you want just using Excel features, or if needed, by Excel macros.
KM may be helpful in moving selected data between apps.
Excel is quite scriptable, although it's not all that easy.
This script selects a single cell:
tell application "Microsoft Excel"
activate
tell active sheet of active workbook
select range "A1:A1"
end tell
end tell
If you only have one column in Excel then that part of the job is dead simple:
tell application "Microsoft Excel"
activate
tell active sheet of active workbook
copy range used range
end tell
end tell
From there you do what Peter suggested and iterate through the lines gleaned from the clipboard.
Or you can actually get the values with your script and do something with them:
tell application "Microsoft Excel"
tell active sheet of active workbook
set valueList to value of used range
end tell
end tell
repeat with i in valueList
set contents of i to item 1 of (contents of i)
end repeat
valueList
If Tinderbox is scriptable then you can really go to town.
I have just tried to make it with the trial of Tinderbox.
This macro requires you to select the cells in Excel, which you need to transfer to Tinderbox.
And that there is a blank document in Tinderbox.
Then activate the macro.
The first part makes sure that data for Excel is in the Clipboard. Excel is sometimes very slow to copy.
Sorry my vocabulary is lacking. Yes, I want to categorize. In my world, these categories are lacking in cold logic. We are looking for patterns which are not necessarily logically explained.
Peter, CCstone, and Jimmy Hartington,
You guys are so helpful!!! I will try to put it together and get back with with results. Downloading Tinderbox Jimmy, was beyond the call of kindness. I was particularily worried about setting variables since I don’t understand this very well. I will get back to you with results.
A non-computer person like me would typically be printing out these 1000 Excel cells, cutting them into slips of paper, and categorizing them by hand. The wisdom among my non-comptuter saavy friends is, “put out the cat.” When I get this working, I am going to make a Youtube video to show them what is possible. I will toot KBM’s horn.
I tried Jimmy Harrington’s macro but it gets stuck. I think the way of bringing up the second program (Tinderbox) may be the problem. Tinderbox does not come to the front. There must be a way to test where the macro gets stuck.
Thank you very much for this, the macro is very helpful i have managed to use it for something else. Now i am trying to improve the macro and would really appreciate some assistance.
When copying from and then repeating for each of the lines that where copied can we do the following. As opposed to repeating each line and pasting once at a time is it possible to repeat and paste 50 lines at a time.
e.g. currently the macro performs; line 1, paste line 1 and then repeat line 2, paste line 2, repeat etc…
What i am trying to achieve; line 1-50, paste line 1-50 and then repeat line 51-100, paste line 51-100 repeat etc…
If there are 100 fields to be processed in Excel the current macros require it to be done one after another, this is extremely slow, but the second method above can perform this work in only two repeats as opposed to doing the same thing 100 times over. I need to be able to process 50 lines per repeat and paste these 50 lines in each repeat, if anyone is having difficulty understanding what i mean by paste please see the second last action in the macro (Insert text by pasting: %Variable%ToTinderBoxItem%)
I don’t really know anything about Excel or Tinderbox, but you can probably do something like this:
Select item 1, Copy to Variable A1, Select Item 2, Copy to Variable A2, etc.
then switch to Tinderbox, and then Paste Variable A1, Move to next, Paste Variable A2, etc.
There is no way (within Keyboard Maestro) to select Named Clipboards or Variables indirectly, so within Keyboard Maestro it would have to be done manually, creating each pair, specifying the variable (or Named Clipboard) explicitly.
Alternatively, you can switch out to AppleScript or Shell Script, and then refer to the variable by name, which can be a non-constant string.
Anytime you brute-force the User-Interface you’ll face timing issues.
Jimmy’s macro could be sped-up by altering the pauses, but you have to experiment on a case-by-case basis to see what works and what doesn’t work.
On my system I generally pause 0.05 seconds to allow the clipboard to catch up with changes made to it, but occasionally I have to give it more time.
Taking a 50 line bite of the data is simple:
tell application "Microsoft Excel"
tell active sheet of active workbook
set valueList to value of used range
end tell
end tell
set listLength to length of valueList
repeat with i in valueList
set contents of i to (contents of i) as text
end repeat
set AppleScript's text item delimiters to linefeed
repeat with i from 1 to listLength by 50
try
set itemToPaste to (items i thru (i + 49) of valueList) as text
on error
try
set itemToPaste to (items i thru -1 of valueList) as text
on error
set itemToPaste to false
end try
end try
if itemToPaste is not false then
# Do something with variable itemToPaste
end if
end repeat
What’s not so easy is dealing with a more complex structure in an Excel worksheet.
This script assumes a single contiguous column of data.
Of course it’s possible to get much fancier, but it also becomes more difficult.
Really sorry. I know nothing about apple script. It looks like you assume that the column in Excel is selected before the script begins. That’s “i”??
Not sure if the process of entering the values from Excel into the script is not described? That is TinderBox (the destination application) is not mentioned. A note has to be opened with a return, and then the value from Excel needs to be entered. This has to happen one note at a time.
Peter said it would be better to have the column entered into the clipboard and then the value copied out one at a time. I think that iterative process is in your script. But, I don’t see where Tinderbox is opened. Maybe I am supposed to divide the script up appropriately.
I need some script at the beginning and the end of each action. Could you divide it up for me?
I have modified the macro a bit with the pauses as Chris suggested.
Also made it pause until Tinderbox is at the front.
But it is UI-hacking. So it would not be as fast as a scripting solution if that was possible.
I have tried it with 200 cells from Excel and it was pretty fast.