Creating multiple variables from a single copy action (MS Excel)

[Siazo]

I'm not sure if this is easier to do in KM or Automator:

I have a working macro that uses Automator to grab the contents of a cell and put it into the clipboard. From there it creates a variable out of the cell. The process then repeats itself for a number of subsequent cells. The problem is that each copy action takes about a second, and there are about 30 cells to copy. I'm wondering if it is possible to speed this up by doing a single copy action that grabs the information from all the cells, and then do something in keyboard maestro to parse the data into different variables.

Any thoughts?

I can also upload the excel file and automator script, or direct this to an automator community.

Thanks!

[JMichaelTX]

@Siazo

I have a working macro that uses Automator to grab the contents of a cell and put it into the clipboard. From there it creates a variable out of the cell. The process then repeats itself for a number of subsequent cells. The problem is that each copy action takes about a second, and there are about 30 cells to copy. I'm wondering if it is possible to speed this up by doing a single copy action that grabs the information from all the cells, and then do something in keyboard maestro to parse the data into different variables.

Yes, that is exactly what you want to do: Copy all cells at once, then process.
When you copy multiple cells from Excel, it should put a TAB or LF or RETURN between each cell.

Here is an untested idea you could use:

1. use a KM Search and Replace action to replace all of the TABs with LFs
2. Use a For Each action on a Collection of Lines on the Clipboard into a For Each Variable "Cell_Value",
3. and process it however you wish.

Personally, I would not use Automator. Most things you can do with Automator you can do with KM and/or AppleScript.

Questions?

[herrevjan]

I think you may run into an issue with how Excel ends up in the clipboard which potentially makes it a bit more complicated to split the data (depending on the content).

I think running this part as part of your Macro to “clean-up” the clipboard before starting to split:

Creating file from Clipboard results in incorrect Tabs and Lineshifts

Hey @herrevjan, Da. You're running into an unexpected consequence of working with Excel's proprietary clipboard. I'm a bit surprised that Keyboard Maestro's Remove-Styles filter doesn't solve this, but it's easy enough to work around. -Chris Microsoft Excel -- Write Clipboard to Text File.kmmacros (5.6 KB) [image]

[ccstone]

@Siazo

I have a working macro that uses Automator to grab the contents of a cell and put it into the clipboard.

Hey @Siazo,

Don't do that! 

You can make this run in the blink of an eye with a little AppleScript.

-Chris

---

Excel ⇢ Selected Cells to Numbered KM Variables.kmmacros (5.8 KB)

[Siazo]

Thanks to everyone, the function is working perfectly now! I was working on learning Regex properly to get the search and replace function to work once the copy action was working properly (big thanks to herrevjan) and then ccstone's script worked wonderfully! I should start learning Applescript I think!

[herrevjan]

Actually, it’s a double thanks to @ccstone - as the author of the reply I quoted. I had the same nightmare while trying to learn some regex. Apparently, Excel is not an ideal partner to include on the start of that journey.