Clipboard Formatting - MS Excel

[skillet]

When I use Keyboard Maestro's clipboards it retains the formatting of the cells in MS Excel that I paste to. However if I use the system clipboard in the macro it retains the copied cells formatting and writes over the formatting of the cells I paste into.

I would like to be able to put things on Keyboard Maestro's clipboards but also retain the formatting of the copied cells. Is there a way to do this?

I made a little video showing first KM's clipboard (not what I want) and the second time running the macro using the system clipboard (what I want). I need to store multiple clipboards for things copies otherwise I would just use the systems clipboard. MS Excel and Keyboard Maestro.zip (1.1 MB)

I included the video in the zip file but here is an offsite link to the video only.

[ccstone]

Hey Skillet,

Can you do what you need with Edit > Paste Special (Match Destination Formatting)?

It looks like I can save Excel’s clipboard verbatim with AppleScript, so I’d think KM can do so as well — but I’m not going to fool with it to find out.

But. I don’t think the function you want is has anything to do with the clipboard contents — I think it’s a switch in how Excel uses the clipboard.

-Chris

[skillet]

Can you do what you need with Edit > Paste Special (Match Destination Formatting)?

That is actually what it is doing already (see video attachment above). However if I use copy and paste it retains the formatting of the copied text. Not sure why it is pasting in as the destination formatting when I use the Keyboard Maestro clipboard.

In other words there is no need to even paste special when I copy and paste in MS Excel it retains all the formatting just fine. It is not until I use Keyboard Maestro that the formatting is lost for some reason. It’s even lost when I use Keyboard Maestro to copy to the system clipboard, however if I use straight key commands it works. Of course I can’t do that though since I couldn’t swap cells without possibly pasting over something else I didn’t want to remove (hence the need for a clipboard history). I couldn’t find any special preference for MS Excel 2011 that changes this behavior either.

I will see what I can figure out and where that switch is, thanks.

[skillet]

tell application "Microsoft Excel" to activate

delay 0.5

tell application "System Events" to keystroke "c" using {command down}
set firstCell to the clipboard

delay 0.5

-- return to retain selection and move up a cell
tell application "System Events" to key code 36 using {option down, shift down}

delay 0.5

tell application "System Events" to keystroke "c" using {command down}

delay 0.5

set secondCell to the clipboard

delay 0.5

set the clipboard to firstCell

delay 1

tell application "System Events" to keystroke "v" using {command down}

delay 0.5

-- return to retain selection and move down a cell
tell application "System Events" to key code 36 using {option down}

delay 0.5

-- Escape to clear selection
tell application "System Events" to key code 53

delay 0.5

set the clipboard to secondCell
tell application "System Events" to keystroke "v" using {command down}

get secondCell
get firstCell

--This doesn't work either though it is successful in getting the clipboard for both command+c (copy) commands. 

[ccstone]

Hey Skillet,

Please provide an actual data sample and a sample of the desired outcome.

-Chris

[skillet]

How's this? Let me know if that is what you had in mind for a sample. I'll happily upload anything else to make it more clear.

Excel - Swap with Cell Below.kmmacros (25.1 KB)

[ccstone]

Hey Skillet,

Okay, the picture makes it easier to understand the problem.

This task is scriptable. Here are some hints:

tell application "Microsoft Excel"
  tell active window
    # Just tests.
    set rangeSelection to range selection
    set _color to color of font object of selection
    set _coler to reverse of _color
    
    # Reverse selection.
    set v1 to value of selection
    set newValue to reverse of v1
    set value of selection to newValue
    set color of font object of selection to _color
    
  end tell
end tell

# Simple Demo of changing font color of selection.
tell application "Microsoft Excel"
  tell active window
    set color of font object of selection to {255, 0, 0}
  end tell
end tell

I suspect it might be done with VBA as well in Excel 14.4.x — but I don’t use VBA and cannot be certain.

Swapping the values turns out to be dead simple (see script above), but getting the text-style-info for individual cells or rows or columns is more complicated.

Excel is a bit funny about how it manages styles in cells and how it copies and pastes ranges.

Even so I was able to perform your task manually using LaunchBar’s saved clipboards.

I tried doing this manually using Keyboard Maestro’s clipboard history switcher to no avail.

So. There might be something Peter can do to improve things behind the scenes.

But I would pursue the scripted solution myself.

I would ask on the Applescript Users List and MacScripter.net, and Microsoft probably has forums devoted to Excel.

That’s about all the time I’m interested in devoting to this, although I might get curious about dealing with fonts in Excel and fiddle with it some more at some point.

Okay, I got curious…

This will reverse the rows of the selection and maintain the color of the text.

tell application "Microsoft Excel"
  tell active window
    
    set fontColorList to {}
    
    tell selection
      set valueList to its value
      set rowList to its rows
    end tell
    
    repeat with i in rowList
      set end of fontColorList to color of font object of i
    end repeat
    
    set value of selection to reverse of valueList
    
    set fontColorList to reverse of fontColorList
    
    set n to 0
    
    repeat with i in rowList
      set n to n + 1
      set color of (font object of i) to (item n of fontColorList)
    end repeat
    
  end tell
end tell

If you need to do more than color you’re on your own.

I would think it would be possible to grab a range with complete style information and do something with it, however I’m not going to try to chase that down.

-Chris

[skillet]

This is fantastic and runs way faster then trying to run a bunch of key commands. It took me a second to realize that I have to select all the cells I want to reverse, no need to swap up or down like the two key commands I was using. Also a nice bonus you gave me is that you can select several Cells and have them all reverse.

Even so I was able to perform your task manually using LaunchBar's saved clipboards.

That is actually what I programed in first, I use LaunchBar's clipboard as my main history. The problem was it wasn't as consistent and I wanted to get something to work that depend on yet another application.

It works with ClipBuddy, Clipboard Center and Clipboard History as well. I have a few others but I figured that was enough to try out to see if this was pretty much isolated to Keyboard Maestro's clipboard history. I wonder if Peter can see what Keyboard Maestro is doing different with it's clipboard history.

Thanks for all the work you put into this and for being curious, this will be a command I will run many times a day. It is a wonder I didn't search for a solution sooner.

If it's of use to anyone attached is the final Macro's. Please note that the AppleScript doesn't save to MS Excel's undo history.
Move Selected Horizontal Cells Up or Down.kmmacros (59.2 KB)

[ccstone]

It is a wonder I didn’t search for a solution sooner.

Ah, that “I could have had a V8…” moment.

Been there.  

-Chris

[peternlewis]

The behaviour sounds like Excel is copying stuff to the clipboard, and then later adding an additional flavour.

The problem is that the API for putting something on the clipboard looks a bit like this:

• Start/Clear the Clipboard Flavours
• Put Flavor A on the Clipboard
• Put Flavor B on the Clipboard
• Put Flavor C on the Clipboard

You’ll notice there is no “Finish” API call. This is really dumb, but its the way it is.

So it’s impossible to tell whether there is more to come or not.

It seems like Excel is basically doing:

• Start/Clear the Clipboard Flavours
• Put Flavor A on the Clipboard
• Put Flavor B on the Clipboard
• Twiddle my thumbs for a while
• Put Flavor C on the Clipboard

And this looks like Copy AB, the Copy ABC.

Also remember that some Adobe/Microsoft flavours are explicitly excluded because reading them causes the applications to behave badly. So its possible that some of the information is in those excluded flavours.

I’ll take a look, but I may not be able to resolve it.

[peternlewis]

So I did some more digging and the issue is that Excel copies the clipboard in an internal format and then when you deactivate Excel (as happens when you display the Clipboard History Switcher for example), it then copies the clipboard into an external format.

So in order to make this macro work, instead of Copy to Named Clipboard, you have replace it with a sequence like this:

• Copy
• Activate Finder
• Copy Clipboard to Named Clipboard ‘MS Excel Clipboard 1’
• Activate Microsoft Excel

Then the macro works fine.

[ccstone]

So in order to make this macro work, instead of Copy to Named Clipboard, you have replace it with a sequence like this:

Hmm… I thought I’d tried that except with the KM Engine, but I’ve slept since then.

The peculiar thing though is the AppleScript I wrote copies the clipboard to a file without switching application context

I’m assuming the difference here is the Microsoft clipboard flavors KM excludes.

-Chris

[skillet]

Copy
Activate Finder
Copy Clipboard to Named Clipboard ‘MS Excel Clipboard 1’
Activate Microsoft Excel

Interesting, thanks for figuring that out, I just tried it and it works (not that I doubted), I’ll have to keep that in mind in the future. It is kind of nice that there are options. I do notice that the macro is much slower since it has to switch to the Finder and back again. I wonder if all the other clipboard applications I tried were aware of MS Excels behavior and are doing that in the background.

[skillet]

This task is scriptable. Here are some hints:

Chris, I have thought about your help with this often, I use your two AppleScript many times a day and saves so much hassle. I just wanted to give you a shout out again and say thank you for your work on this. It would be difficult going back to using a spreadsheet without it!

The swap with cell above is my most used macro used 2,701 times saving me 17 hours alone, swap with cell below comes in at 1,887 times so far with 11 hours saved. I just realized those stats are only specific to one computer and are not shared between computers that share macros, so the number is much higher than that. Swap with cell above is second only to my pomodoro tracking info on my other computer.

I just totaled them all up for a total use of 6,371 times since Keyboard Maestro 7 was released which was released 1 year ago yesterday, which would be when Keyboard Maestro started tracking use similar to how QuicKeys does. So 18 times a day on average, that seems a bit low, but still!

THANK YOU Chris!!