Just for your curiosity, here is another sorting project where the buttons are being sorted in pre-defined areas. This is great fun.
One option to consider would be:
- Command-Shift-Down Arrow (select all the cells from here down)
- Copy
- Set Variable to Clipboard
- Switch to Tinderbox
- For Each Lines in Variable
- Do whatever with the line
That will likely be a lot simpler and a lot faster and a lot more robust than bouncing back and forth.
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.
Hey Ellen,
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.
-Chris
Hi Ellen
Peter is suggesting the way I would do it.
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.
Macro: http://cl.ly/1M270j183z0H/download/Excel%20to%20Tinderbox.kmmacros
Thank you all for your help.
MichaelTx,
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.
Will get back to you later.
Best,
Ellen
1 Like
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.
Hi,
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%)
Any thoughts or hack would be really helpful.
Thank you
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.
Remember that some applications will not like to switching the clipboard over and over again, but hopefully Tinderbox is well behaved.
Hey There,
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.
–
Best Regards,
Chris
Hi Chris,
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?
Best,
Ellenm
Hi Peter,
I think maybe Tinderbox is well behaved. It is written in a very sparce way so us non-computer coding people have a hard time.
Maybe this is the non-constant string that you are referring to. (Written by ccstone below):
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
I am trying to think how I could use his script. As it is, it does not work. I think that KBM actions have to be used as well.
Thank-you,
Ellenm
Hi Ellen
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.
Here is the macro: http://cl.ly/2J2Y1N0L1K3s
Hey Ellen,
No. I'm grabbing all of the values in the used range at once into a variable.
tell application "Microsoft Excel"
tell active sheet of active workbook
set valueList to value of used range
end tell
end tell
From there I don't have to fool with Excel anymore, and the script is waaay faster.
I don't think you want to be trying to use the script I wrote for @demirtas1. His is a somewhat different task than yours.
-Chris
The used range is not the selection. It provides coordinates of the used range without needing a selection.
-Chris
Tinderbox is NOT AppleScriptable.
This works:
-----------------------------------------------------------------------
# This script assumes a SINGLE column of values in the front worksheet in Excel.
-----------------------------------------------------------------------
tell application "Microsoft Excel"
tell active sheet of active workbook
set valueList to value of used range
end tell
end tell
if valueList ≠ "" then
repeat with i in valueList
set contents of i to item 1 of (contents of i)
end repeat
tell application "System Events"
if quit delay ≠ 0 then set quit delay to 0
set frontmost of application process "TinderboxSix" to true
repeat with i in valueList
set the clipboard to (contents of i)
delay 0.2
keystroke "v" using {command down}
delay 0.2
key code 36
end repeat
end tell
end if
-----------------------------------------------------------------------
The delay might have to be adjusted for systems other than mine.
[Edit 2015/06/19 20:32 CST — changed delay times.]
Oh, I note that you cannot paste into a new Tinderbox document without first clicking in the entry pane.
Tinderbox is finicky about how fast you paste data into it.
-Chris
-----------------------------------------------------------------------
# This Script works with the single SELECTED column in Excel.
-----------------------------------------------------------------------
tell application "Microsoft Excel"
tell active window
set valueList to value of selection
end tell
end tell
repeat with i in valueList
set contents of i to item 1 of (contents of i)
end repeat
# valueList
tell application "System Events"
set frontmost of application process "TinderboxSix" to true
repeat with i in valueList
set the clipboard to (contents of i)
delay 0.2
keystroke "v" using {command down}
delay 0.2
key code 36
end repeat
end tellHi Chris,
The first part of your code works.
The second part fails to open a note.
To make a note (where you want to input one cell of Excel), you have to double click on the mouse pad of TBx (Tinder ). You need a keyboard shortcut.
I tried making a note and then pasting (command V). The whole Excel column was pasted in instead of the specific cell.
Here is the clunky thing I did, but It did not work to paste in one cell at a time and then make a new note. I think it is stuck in not knowing how to make a new note.
Ellen Madono
Hey Ellen,
You never specified a new note should be opened. [ Edit: I was thinking document — notes are not so much opened as created within the context of the document.]
I did mention problems with this:
What you've done with the AppleScript cannot work.
You can't split up programming that shares variables without somehow passing those variables.
Your macro provides no pause to allow selecting a column in Excel.
In general user-intereaction is not desired in an automated process. It slows things down and creates more opportunities for error.
It would be better to have an Excel document with ONE column and use the used range script rather than the selected column script if reasonably possible.
If you really want to sort this out then send me a proper example of the Excel file you're working with to: listmeister@thestoneforge.com
-Chris
As far as I can see Tinderbox provides no formal method of moving the focus into the Map pane other than:
A) Creating a new document.
B) Clicking within the map side of the front document
C) Using the menu View > Tab > Previous (or Next) to move out of the Map view and then back again.
It does not seem to respect normal accessibility methods using System Events, so while SEV can see the map pane it cannot change the focus to the map pane.
The only way I see around this is to click at some specific starting point within the map pane before beginning to paste notes.
-Chris