Create metric conversion macro

[bifjamod]

Can someone help me write a macro to copy a number from Excel, convert it using /2.2.046, and paste the result back to the selected cell? I've tried several variations on this theme with no success. The following is what I have created, but it does....nothing.

[peternlewis]

That looks like it should work. I would probably do it like this:

Type the ⌘C Keystroke
Set Variable ‘Clipboard’ to Text ‘%CurrentClipboard%’
Insert Text ‘%Calculate%Clipboard/2.2046%’ by Pasting

But what you’ve done should work.

I suspect the problem you’re running into it clipboard caching. Excel is probably doing its own clipboard caching, and because you haven’t done anything, it has no reason to believe the clipboard has magically changed since you did the Command-C, resulting in the new clipboard that Keyboard Maestro sets not being seen by the application. This is particularly prevalent in cross platform applications or applications that expect very large clipboards. A solution to this may require setting the clipboard, and then executing some other action to wake up the application to the change, such as changing out of the application and then back, or clicking in the menu bar.

[philippe]

Hi,

There you go:

Note that calculations work directly on the name of the variable, not on the token representing it. But CurrentClipboard is not a regular variable so you have to go through a variable.

Philippe

[bifjamod]

What you’ve both said makes sense, and in theory probably work. I suspect Peter is right about Excel’s clipboard: adding a “display clipboard” does show the proper result, but it simple won’t paste back into Excel.

Also, “display clipboard” kills the KM engine, rendering the whole thing pointless anyway. Sigh.

OK, so I’ve learned something - that there are multiple ways to skin the cat, and the KM has limitations for me. Not happy either way, but I’m also not going to waste more time down this rabbit hole with something I can do with Excel VBA at some point.

My biggest learning curve, I think, is understanding the KM syntax, if that’s the right word.

Thanks guys. (And yes, I note the recategorization from ‘Macro’ to ‘general’ for future reference.

[JimmyHartington]

Insert by typing could also solve the problem with the clipboard.
But Excel is pretty bad with clipboard caching.

Sendt fra min iPhone

[philippe]

Applications that don’t share their clipboard automatically will do it when they come to the front and leave it. So the solution should be to switch to Finder after the copy, then back to Excel just before pasting.

Philippe

[ccstone]

Let’s try doing this more directly with AppleScript.

tell application “Microsoft Excel”
   set valueOfActiveCell to value of active cell
   if valueOfActiveCell is not “” then
      set calculatedValue to valueOfActiveCell / 2.2046
      set value of active cell to calculatedValue
   end if
end tell


[bifjamod]

BRILLIANT! I knew I was going to love AppleScript! Now if I just had more time to delve into it, but the couple of instances I’ve been able to use it, it’s terrific. So, I pasted this into my KM macro, and it runs beautifully and does exactly what I need. THANK YOU SO MUCH!