Help stopping a loop by evaluating the clipboard?

[Adam Fleischer]

Hi,

I have several users here set up to modify large lists of varying length (300-30,000 records) in an ERP system using a list of values in Excel. The ERP system is accessed by an emulator on a PC.

The macros I have set up work by copying the Excel cell contents on to the clipboard and then pasting the contents into the ERP system. I have tried to set up an "if" statement to say that if the clipboard is blank or contains a specified string then go to a label, otherwise continue the loop. Neither method has worked.

** I am not using the DDERequest command. I am switching programs, highlighting a cell and then using C to copy and then switching programs and pasting using V to paste. I know, this is the luddite way... As soon as I have some "innovation" time I'll work on it.

So, how can I put a conditional goto into my loops? Or, is there a better way to do this?

Thanks in advance for your help.

Adam

[Marcus Tettmar]

GetClipBoard>clip_data
If>clip_data=whatever
Goto>some_label
Endif

..
..
.. bla bla
..
..

Label>some_label
.. etc

May want to use subroutines instead of labels, or put the code inside the If/Endif rather than jump to the label. If using V10 you could use Exit if you just want to exit the script completely. If the label should ONLY be executed as a result of the Goto, then make sure normal flow jumps over it.

[Adam Fleischer]

Hi Marcus,

Thanks for the prompt reply.

That text is what I've tried. You would think it'd work but it does not. I've tried it testing for a blank cell. I've tried it using a predetermined text string and also pasting the contents of a blank cell into the conditional statement as well. All of these methods should yield the desired results but the outcome is that the loop just marches on regardless.

Any other methods?

[Me_again]

I think you need to check exactly you are getting from a blank cell, if I recall correctly it isn't nothing and it may be something like a CRLF.

[Adam Fleischer]

Here's a sample I just wrote up that I had previously tried. I tested this before posting with the same results as seen previously.

LABEL>THE_START
setfocus>Microsoft Excel - Book1
Press Down
Press CTRL
Send>c
Release CTRL
Wait>1
GCB>cell_contents
if>cell_contents="stopstopstop"
goto>THE_END
endif
setfocus>Document9 - Microsoft Word
Press CTRL
Send>v
Release CTRL
Send> bottles of beer on the wall
Press Enter
Wait>1
Goto>THE_START
label>THE_END

The column of excel data was:

99
98
97
96
95
94
93
92
stopstopstop


Should work, no?

[Marcus Tettmar]

Just a few things:

1) MacroScript doesn't want quotes for strings. So it should just be:

if>cell_contents=stopstopstop

2) As noted by me_again, often data received from Excel can contain CRLF pairs, or other non-printing chars. So you might not get an exact match with stopstopstop.

You could try:

GetClipBoard>cell_contents
StringReplace>cell_contents,CR,,cell_contents
StringReplace>cell_contents,LF,,cell_contents

You may also want to Trim the clipboard data - in case of trailing spaces.

But if you use the debugger, you'll have a better idea of why there isn't a match. Or at the very least add this temporary diagnostic after the GetClipBoard line:

GetClipBoard>cell_contents
MessageModal>|%cell_contents%|

Notice I've put | chars immediately adjacent to the start and end of the cell_contents variable. This will show up immediately if there are any spaces or line breaks.

Another thing you could do is not look for an exact match but see if stopstopstop is *in* the data:

Position>stopstopstop,cell_contents,1,p
If>p>0
...
...

Possibly cell_contents doesn't even contain anything like what you think it should. Possibly we're looking at the wrong thing and the problem is that the CTRL-C is landing in the wrong place and copying the wrong thing altogether.

We won't know without debugging.

Use the debugger. Add some diagnostic messages. Then you will see why this doesn't work the way you think it should.

[JRL]

As me_again pointed out, you need a %CRLF% in the mix if you want to match Excel clipboard data.

Also, remove the quotes, in Macro Scheduler they are characters. I have not tested this but I think it might work.

Edit----Or better yet use Marcus' suggestion using the Position> function

Position>stopstopstop,cell_contents,1,p
If>p>0

Code: Select all

LABEL>THE_START
setfocus>Microsoft Excel - Book1
Press Down
Press CTRL
Send>c
Release CTRL
Wait>1
GCB>cell_contents
if>cell_contents=stopstopstop%CRLF%
goto>THE_END
endif
setfocus>Document9 - Microsoft Word
Press CTRL
Send>v
Release CTRL
Send> bottles of beer on the wall
Press Enter
Wait>1
Goto>THE_START
label>THE_END

[Adam Fleischer]

Thanks Marcus, Me_again and JRL!

I got it working and I've learned some new things in the process.

Adam