Inconsistent Copy/Paste Performance when using "Repeat Actions # times" many times

[maupinsmason]

Hello all,

First off, in a nutshell of what I am trying to accomplish is rather simple:

1. Copy the contents (a chemical formula) within an Excel (2016) cell, and then tabbed over one cell to the right.

2. Switch over to a software named MarvinSketch (a chemical structure drawing software) and paste the contents from excel.

3. Format the pasted Excel contents within MarvinSketch (consisted of a series of clicks), and then selecting "save as" to save the file into a dedicated folder for all of the samples within the spreadsheet.

4. Switch back over to Excel and copy the cell contents, which is my file name I'll be using to save my structure in MarvinSketch (don't forget I tabbed one cell to the right before switching apps in step 1 of this list). Then I moved the selected cell position to where I will start this process over again but for the next sample.

5. Finally, switch back over to MarvinSketch and paste the file name within the "save as" box, hit the return keystroke, and clear the canvas for the next sample's input.

6. The kicker is, I need to repeat (loop) these steps (my macro) 168,400 times.

FYI: just in case you were wondering what my logic was for using the green and red colors:

• Green = Actions executed within Excel

• Red = Actions executed within MarvinSketch

My intention was for it to be easier to read by breaking the actions up by the app in which the actions were being executed.

So, now you know the steps and rough idea of what I'm doing, the problem I am faced with is when I try to repeat this 168,400 times I can't get past 100 repeats without the copying and paste action proceeding ahead of the "Pause Until Application is at the Front" commands I set for the macro. Ultimately, forcing me to stop and start the macro over.

I searched the Google Machine far and wide to see if anybody was having similar problems with the copy and paste actions when using a rather large repeat value, such as 168,400. The best explanation I could find to explain why Copy/Paste wasn't waiting until my app was at the front and ready to be pasted to, is I wasn't giving KM an additional pause along with my "Pause until" command right next to each other, which in my opinion causes a messy redundancy.

I tried this suggested solution of adding longer duration pause to my macro, but no such luck. However, I was still kind of convinced that my iMac could possible be getting bogged down with all of the applications up and running (even though I'm working from a 2015 iMac 27" 5K Retina {; 4.0 GHz i7, and 32 GB of RAM})

The last big approach I took in attempt to solve this was I separated the one spreadsheet containing the 168,400 samples into ~34 individual spreadsheets containing only 2,000 samples each.

This didn't ultimately help either because even though I was able to get through ~600 samples until it started acting up again, I checked to see how many of the 600 samples actually processed and got saved correctly... and long story short; because I saved them all in one folder I used "⌘ + a" to select all of the files within the folder and opened up the "Get info." window and it turns out that only 559 was actually saved to the folder.... So, I'm not even sure if those 559 files that did get saved, was actually saved correctly....

So, I'm not really sure what to do next in terms of getting the macro to function the way I need it to, so I'm reaching out to the awesome KM community to see if anybody would have suggestions, questions, comments, etc. about my macro, I would tremendously appreciate it!!

I apologize about the pretty extensive post, but if a video demonstration is something that would help, I will be more than happy to record and share it as long as somebody can tell me how to upload it lol

Again, sorry about such a long explanation, but this has been driving me crazy for quite some time now, and I really need a different or a more experienced perspective!

Thanks again!

Copy-Paste & Save Peptide Structures into MarvinSketch.kmmacros (34 KB)

[gglick]

Hi @maupinsmason,

I'm afraid I know nothing about MarvinSketch, so I can't be of much help there, but I do have ideas for two possible approaches you could use to improve the Excel side of your macro's speed and accuracy.

First, you could replace the "Activate Excel, Copy, Tab" etc. actions with AppleScript actions that can get the same information without simulating keystrokes for copying, keeping Excel in the background and the focus on MarvinSketch. For example, to do this:

@maupinsmason

Copy the contents (a chemical formula) within an Excel (2016) cell, and then tabbed over one cell to the right.

you could use this script in an Execute an AppleScript action:

tell application "Microsoft Excel"
  set the clipboard to (get value of active cell)
  select (get offset active cell column offset 1)
end tell

And for this:

@maupinsmason

Switch back over to Excel and copy the cell contents, which is my file name I'll be using to save my structure in MarvinSketch (don't forget I tabbed one cell to the right before switching apps in step 1 of this list). Then I moved the selected cell position to where I will start this process over again but for the next sample.

you could use:

tell application "Microsoft Excel"
  set the clipboard to (get value of active cell)
  select (get offset active cell row offset 1 column offset -1)
end tell

These scripts can entirely replace the Excel actions you're currently using in the Repeat loop part of your macro, paring this:

down to this:

(I also deleted the second "Activate MarvinSketch" action here, because it's no longer necessary since MarvinSketch remains the frontmost app this way)

While even these scripts may not be the most efficient way of working with the data you describe, they should still significantly improve the Excel side of your macro (at least in the Repeat loop; I noticed there is another group of Excel actions in your macro following the loop, but except for the Save shortcut, I'm afraid I can't tell what those are meant to do from the actions alone).

So what's a more efficient way to work with the data you describe? The other idea I have is to copy the entire contents of an Excel sheet to a Keyboard Maestro variable and process the formula and file name contents "natively", which would look something like this:

While it would take some reworking of your macro as it stands now to make use of the data in a pure KM variable form like this, I think it would have the advantage of being even faster and more reliable than the scripting solution, since the data would all be "pre-loaded" in KM and wouldn't need to go through the clipboard at all.

While I repeat again that I know nothing about MarvinSketch and thus can't be of much help there, I do suspect that it might be possible to handle much if not all of the formatting you mention here:

@maupinsmason

Format the pasted Excel contents within MarvinSketch (consisted of a series of clicks)

within KM as well, which should also help with the macro's reliability and cut down on the number of actions you need to perform with simulated clicks in MarvinSketch. However, in order to help with that, we need an example of what a formula looks like when first copied from Excel, and what you want it to look like once the formatting is complete. In case the data is of a sensitive nature, it's fine to use bogus or sample data; the important thing is that we know exactly what format of text we're dealing with.

For now, here's a modified version of the macro you posted with the above AppleScripts implemented as shown in the screenshots, and the For Each loop example included at the end (disabled, so as not to inadvertently affect the current macro's testing):

Copy:Paste & Save Peptide Structures into MarvinSketch 1.1.kmmacros (28.5 KB)

Sorry I couldn't be of assistance with the MarvinSketch aspect of the macro; hopefully someone else can help you out with that. In the meantime, feel free to let us know if you have any further questions, and good luck!

[peternlewis]

When something fails eventually, that is always hard to debug.

It is usually a timing issue, but not already.

Your macro is very fragile with the typing and the clicks in there because they go through the event queue, but clicks require the screen to be correct at the time they happen. Things like animations can really mess things up.

You switch back and forth copying different things, but better would be to copy both things and then paste both things. Better still, as @gglick points out) would be to use AppleScript to get the cell values. Potentially, if it is one large spreadsheet, you can use AppleScript to get each cell value without ever having to activate Excel at all.

Whenever I do a process that will take a long time (eg sending out all the emails for a new release), I tend to code in a periodic rest to let the system recover (so every 100 or 1000 items, do a substantially longer pause, say 20 or 60 seconds). This just allows any pent up system caching etc to complete.

I think @gglick is on the right track, but I think if you take it even further and figure out the required AppleScript to extract the cells explicitly without any selection, then you’ll get much faster results, and hopefully more consistency.