Clipboard Copy woes

[Rustywinger]

Greetings, Gurus.

 

I have a lot of macros that begin with copying the contents of a highlighted Excel cell to the clipboard with a ME "Clipboard Copy" command.

 

However, there is a delay in between the execution of this windows command and the actual populating of the clipboard with the cell data, and it is wreaking havoc on a process that seemed bulletproof in 3.2.

 

What can I do to validate that the 'clipboard copy' process is complete before moving on to the next step? I don't want to have to add a delay between every step- that's just not efficient enough for an old OCD like me and I will stay up all night worrying about the nanoseconds I've lost not doing the actual job I'm being paid to slog through.

 

 

Thanks!

[acantor]

Unfortunately, when scripting macros, we have limited access to the inner workings of the OS and applications. And even when we do have programmatic access, it may not be the fastest way to accomplish a task. For example, it may be possible to harvest information about the user interface, store it in a variable, parse it, make decisions, and activate something... but it may be a lot faster to move the bloody mouse pointer to a button and click!

 

Like you, I do not like including delays in macros. I prefer to wait for events. But delays are an almost inevitable aspect of macro scripting. My goal is to script macros that are fast, portable, reliable, and elegant. In actual practice, I usually manage two or three out of four.

 

My suggestion is to make peace with delays. Adding delays is not illegal, not unhealthy, and not immoral. The worst thing about delays is that they are not pretty! But in time, delays begin to reveal a beauty of their own. There is art to be found...

[Rustywinger]

Thanks for the reply, Alan

 

I suppose if nothing else, I could set up a separate macro "widget" that contained a clipboard copy command, a loop to test the state of the clipboard data before continuing, and then nestle it within the main macro at various points requiring clipboard data. This seems just too inelegant. Perhaps someone out there knows a better way?

 

 

(Co-worker staring quizzically at the monitor)- "Is the macro waiting for data?" "No, It's taking a break for 3 seconds" "Well, why?" "It's making a point that no matter how efficient the world has become, you should stop and smell the roses, because when you don't you might walk away having missed something really important, like the clipboard data." "oh." (continues watching screen with brow furrowed)

 

 

 

/still remembers having the "Good, Fast, Cheap - Choose any two" hammered into his head years ago.

[stevecasper]

I suppose if nothing else, I could set up a separate macro "widget" that contained a clipboard copy command, a loop to test the state of the clipboard data before continuing, and then nestle it within the main macro at various points requiring clipboard data.

This is essentially what I have built into my macros that need the clipboard to actually copy something before the macro moves on. It is

nice in the sense that I don't have to guess how long a delay I need to build, and helps make sure that as soon as the clipboard actually

contains information, the macro will continue on (and not until then). It's definitely inelegant compared to a "Clipboard Copy Until

<>Empty" command (or something similar) - Sounds like a feature request is needed.

 

Clipboard Empty
Repeat Until %T[1]% Does not Equal "%T[1]%"
 Clipboard Copy
 If Clipboard Does not Equal ""
Repeat Exit
 End If
End Repeat

<CLIPBOARD EMPTY/>
<REPEAT UNTIL Variable="%T[1]%" Condition="\x01" Value="%T[1]%"/>
<CLIPBOARD COPY/>
<IF CLIPBOARD Option="\x02" CaseSensitive="FALSE"/>
<REPEAT EXIT/>
<END IF/>
<END REPEAT/>

I generally start with a Clipboard Empty to make sure the information in the clipboard is the actual information I'm trying to gather.

[Rustywinger]

Hi Scasper!

 

My thinking was that the clipboard copy function would not actually be within the loop. It looks like you have a clipboard copy command repeating itself over and over?

 

Clipboard Empty
Clipboard Copy
Repeat Until T1 Does not Equal ""
 Variable Set String T1 from the clipboard contents
End Repeat

[Cory]

I have done this in the past as well except I do a small delay like 50mS i the loop. Essentially repeat until the clipboard has something.

 

My other observation is that people try to do too much navigating in Excel. If you are doing something like Copy > Calculate > Move over one cell to the right > Paste result, Move down and over to the left one> Repeat a bunch of times or, actually, any pattern of moves and manipulations it's better to take the whole table of data out of Excel. Personally I will use F5 to highlight a range of cells, copy to the clipboard, copy to a string var, and save to file (or a series of arrays) then use ASCII File Process with Tab delimiters. The results of which get saved to a TSV file (or just in an output accumulator variable) and at the end stick the results on the clipboard and past them back in. This way I can do thousands of loops with many steps and the whole thing only takes fractions of a second. And since we're only talking about ASCII text here it doesn't even mess with the Excel formatting. IOW stop arrowing around in Excel and do it all in memory. If you would like to learn more check out my article on Building TSV Files

[stevecasper]

Hi Scasper!

 

My thinking was that the clipboard copy function would not actually be within the loop. It looks like you have a clipboard copy command repeating itself over and over?

 

Clipboard Empty
Clipboard Copy
Repeat Until T1 Does not Equal ""
 Variable Set String T1 from the clipboard contents
End Repeat

Hmmm...

 

I see your point. However, I've often had problems with the macro moving so fast (or the source application moving so slowly)

that the Copy command is executed before the text is even highlighted. Inserting the Copy command within the Loop allows for

the delay in the highlighting of text.

 

Your solution negates the need for a delay after the Copy command, but may require a delay before it. My solution

negates the need for a delay before it, and may not need a delay after it. Since the Copy command is being repeated, it should

eventually gather information quickly enough - in fact I've never had the loop hang for very long with the Copy inside.

[Cory]

I think you miss my point Steve. If you macro only does one copy and paste there is no difference. But if you macro is doing a thousand copy/pastes then my solution is superior as it only executes one copy/paste for everything.

[Rustywinger]

Hi, Steve, I see your point too, just different applications of theory because my macro starts off with a single clipboard copy command and no coded delay was required in ME3 and shouldn't be in ME4.

 

I am thinking simply the function lag in Excel also stalled ME3's progress, which doesn't appear to be the case now.

 

 

Thanks for the applied brainpower, everyone!

 

 

Your solution negates the need for a delay after the Copy command, but may require a delay before it. My solution

negates the need for a delay before it, and may not need a delay after it. Since the Copy command is being repeated, it should

eventually gather information quickly enough - in fact I've never had the loop hang for very long with the Copy inside.

[Rustywinger]

I think you miss my point Steve. If you macro only does one copy and paste there is no difference. But if you macro is doing a thousand copy/pastes then my solution is superior as it only executes one copy/paste for everything.

 

Hi Cory, I also employ this strategy and don't even bother opening the excel files. I just load it all into memory, slice and dice, then save it back to the excel file. Of course I got this idea from one of your ancient posts.

 

I think this method can be scary for some as it requires an absolute faith in your personal skills as a data visualist/analysist and not to mention your programming prowess.

 

However, for the task in this thread, the macro is called up by a clerk after they selected a single cell in the Excel sheet to process. The task spans several applications and a "behind scenes" DB query and returns to Excel with data to fill the row.