Phantom Code

[Amerifax-Bob]

We are running the following Macro and getting various results. Sometimes the Macro misses variables, sometimes they are doubled up but the results are never the same. What is missed or double one time, is fine the next time. The results are so random.

This is the code:

Clipboard Empty
Window Activate: Microsoft Excel - 
Get Mouse Position into (%N[1]%, %N[2]%) Relative to Current Window
// Name T1
Text Type (Simulate Keystrokes): <TAB>
Clipboard Copy
Variable Set String %T[1]% from the clipboard contents
// Company T2
Text Type (Simulate Keystrokes): <ESC><TAB><TAB>
Delay: 350 milliseconds
Clipboard Copy
Variable Set String %T[2]% from the clipboard contents
// Address T3
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 400 milliseconds
Clipboard Copy
Variable Set String %T[3]% from the clipboard contents
// City T4
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 350 milliseconds
Clipboard Copy
Variable Set String %T[4]% from the clipboard contents
// State T5
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 350 milliseconds
Clipboard Copy
Variable Set String %T[5]% from the clipboard contents
// ZIP T6
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 350 milliseconds
Clipboard Copy
Variable Set String %T[6]% from the clipboard contents
// Phone T7
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 350 milliseconds
Clipboard Copy
Variable Set String %T[7]% from the clipboard contents
// Toll Free T8
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 350 milliseconds
Clipboard Copy
Variable Set String %T[8]% from the clipboard contents
// Cell T9
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 400 milliseconds
Clipboard Copy
Variable Set String %T[9]% from the clipboard contents
// WebSite T10
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 400 milliseconds
Clipboard Copy
Variable Set String %T[10]% from the clipboard contents
// E-Mail T11
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 400 milliseconds
Clipboard Copy
Variable Set String %T[11]% from the clipboard contents
// ALT E-Mail T12
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 400 milliseconds
Clipboard Copy
Variable Set String %T[12]% from the clipboard contents
// Hour T13
Text Type (Simulate Keystrokes): <ESC><TAB>
Delay: 650 milliseconds
Clipboard Copy
Variable Set String %T[13]% from the clipboard contents
Window Activate: WordPad
Wait for Window Title: WordPad
Text Type (Simulate Keystrokes): %T[1]%
Text Type (Simulate Keystrokes): %T[2]%
Text Type (Simulate Keystrokes): %T[3]%
Text Type (Simulate Keystrokes): %T[4]%
Text Type (Simulate Keystrokes): %T[5]%
Text Type (Simulate Keystrokes): %T[6]%
Text Type (Simulate Keystrokes): %T[7]%
Text Type (Simulate Keystrokes): %T[8]%
Text Type (Simulate Keystrokes): %T[9]%
Text Type (Simulate Keystrokes): %T[10]%
Text Type (Simulate Keystrokes): %T[11]%
Text Type (Simulate Keystrokes): %T[12]%
Text Type (Simulate Keystrokes): %T[13]%

 

These are the results:

 

Roger

1030B Orlando Dr

De Pere Doubled

De Pere

WI

54115-9094

920-336-2603

920-336-7775

Cell Phone

shannon.gretz@mascocs.com

 

Like I said the results are so random it never has the same problem twice. Any help would be greatly appreciated.

 

Bob

[Cory]

I keep saying this but I'll try again.

..................................................

You need to include some time after all clipboard events in Excel. You are going directly to keystrokes. Depending on the machine speed I insert anywhere from 200 to 500mS after each clipboard event. If you read our other discussions on this matter you can see a detailed explanation from me on the extra cleverness that Excel needs time to complete before you feed it anything more. Understand this is not a bug in MEP. MEP has no control over how other applications are interacting with the clipboard.

 

Also I have suggested many times to you and others that you avoid interacting with Excel like this. It’s much better to copy a bunch of cells at once and parse them into variables in the MEP memory space. You can also assemble your results in a TSV formatted variable to paste back to Excel all at once. I’ve explained how to do that in this article here.

[Amerifax-Bob]

>>I keep saying this but I'll try again<<

 

I thought I was doing what you suggested by copying the cells as Variables. Evidently I'm not. Are you suggesting that I copy a range in Excel to another program such as Ultra Edit?

 

Bob

[rberq]

Your problem is almost certainly in the clipboard use. To echo what Cory said, clipboard functions are notoriously unpredictable as to how long they will take. Especially Copy to Clipboard, and even commands like VARIABLE MODIFY STRING - SAVE TO CLIPBOARD. Timing is unpredictable from one PC to the next, and from minute to minute on the same PC.

 

Search the ME3 and MEP forums for "clipboard" and you will see some tricks that others have found useful.

[Cory]

Yes, copy a range but no text editor.

.......................................................................

This is described on my webpage but let me try it another way. How I would do it.

1. Have the user copy a range in Excel.
   
2. Copy that clipboard to a var.
   
3. Break that variable into it’s constituent parts.
   
4. Do whatever I need to do then programmatically put the results in an output variable.
   
5. Copy that output to the clipboard and inform the user they can paste it back.
   

You will notice I have involved no other apps here because I’ve done it all in memory.

 

Now of course your wondering how I broke it up, right? When it’s copied into the variable it’s in a TSV file format and I can split apart by searching for delimiters. Delimiters are simply special characters used to signify the end/beginning of text. In Excel cells horizontally are split with the tab character and the lines are split with a carriage return and a line feed. Here’s a quick example. For illustration all text will be lower case and a tab will be indicated with “T” and carriage return “C” and line feed is “L”. A small list of drivers with first middle and last in different columns:

 

mark neary donahue

ayrton senna da silva

juan manuel fangio

 

Would look like this in the variable of macro express.

 

markTnearyTdonahueCLayrtonTsennaTda silvaCLjuanTmanuelTfangioCL

 

Now just break them out bit by bit. For instance you could find the position of the tab, copy 1 to that position to %First Name%, trim %First Name% (gets rid of the tab) and then from %Clipboard% delete 1 to %Position%. Do the same for middle and last. Then put that all in a repeat that does that for each line.

 

Now for output. Let’s say our macro was just going to rearrange them into Last First Middle. Well in the big loop after you split the names simply append to the output variable “%Last Name%%Tab%%First Name%%Tab%%Middle Name%%CR%%LF%”. Once done you can simply paste that back in.

 

The one part you may be wondering about is how you use those non-printing characters like tab since we can’t type them in in most places. Simply use the Variable Set to ASCII Char command. Tab is 9, CR is 13, and LF is 10.

 

Some users might be thinking that I should use arrays and the split and join commands but I want to keep it simple for now. I have created a simple demo macro but it must be used with only a range 3 by 3. Normally there would be more code to figure out how big the range was and such but I wanted to keep it simple for you to see the essential function.

 

It might seem a little bewildering at first but once you can see it in your mind it will quickly make sense. And I guarantee you will be slapping yourself in the forehead when you realize how much faster and more reliable this is than navigating Excel cell by cell.

Excel_Clipboard_Demo.mex