MEP Autopasting in Excel 2007 Unexpectedly

[jrgreenman]

I have an spreadsheet (xls, but opened in Excel 2007) for which I am copying data from one sheet to another (attached).

 

The data "Copy" is in a named cell called Main (on MainSheet). The destination is a named cell on CopySheet called "Copy".

 

Here's the code:

 

========================================================================

<WINDOW ACTIVATE Title=".xls [Compatibility Mode]" Exact_Match="FALSE" Wildcards="FALSE" _IGNORE="0x0006"/>

<CLIPBOARD COPY/>

<VARIABLE SET STRING Option="\x02" Destination="%Customer%"/>

<TEXT TYPE Action="0" Text="<CTRL>x"/>

<TEXT TYPE Action="0" Text="<CTRL>g"/>

<WAIT FOR WINDOW TITLE Title="Go To" Partial="TRUE" Wildcards="FALSE" Indefinite="TRUE" Hours="0" Minutes="0" Seconds="0"/>

<DELAY Flags="\x02" Time="300"/>

<TEXT TYPE Action="0" Text="%Customer%<ENTER>"/>

<TEXT TYPE Action="0" Text="Copy<ENTER>" _ENABLED="FALSE"/>

========================================================================

 

As written, the code should copy BUT NOT PASTE the data into the destination cell. But it does paste it!

 

Curiosities:

1. When I execute the same keystrokes manually, data is not pasted.

2. When I invert lines 9 and 10, data is not pasted (even though %Customer% holds the string literal "copy").

 

I'm stumped. Why is MEP automatically pasting the data into the destination sheet??

 

Jace

 

test.zip

[Cory]

My guess is that you have anohter macro and the keystrokes of this macro are activating the other macro. Create a new macro file (export) with only the macro in it and try again. Make sure only this 1 MEX is open.

 

PS F5 works for the GoTo box as well

[jrgreenman]

My guess is that you have anohter macro and the keystrokes of this macro are activating the other macro. Create a new macro file (export) with only the macro in it and try again. Make sure only this 1 MEX is open.

 

PS F5 works for the GoTo box as well

 

Thanks for the reply, Cory. A good suggestion, but no joy. I even replaced <CTRL>g w/ <F5>, but still see the same behavior.

 

From my initial post, note that the macro works as expected if I merely invert the last two lines of code. That is, if I type out the string literal "Copy" instead of the contents of the variable %Customer%, the macro works fine.

 

I think this is some kind of bug in MEP related to the clipboard contents and typing out variables.

 

Will submit a bug report.

[Cory]

You should probably submit a support request, not a bugrep as i seriously doubt it's a bug.

 

Here's something I don't understand. Your code is opening the GoTo box but why on earth are you typing the customer name into the GoTo box? Don't you want to go somewhere and then paste in the customer? I mean unless what you are coping is a string like "Sheet1!B15" it isn't going to work.

[jrgreenman]

You should probably submit a support request, not a bugrep as i seriously doubt it's a bug.

 

Here's something I don't understand. Your code is opening the GoTo box but why on earth are you typing the customer name into the GoTo box? Don't you want to go somewhere and then paste in the customer? I mean unless what you are coping is a string like "Sheet1!B15" it isn't going to work.

 

This is the only reporting mechanism I am aware of: http://www.macros.com/bugreport.htm

 

Is a "support request" mae through some other web page??

 

Regarding the question on the GoTo box, I have created named cells so that I can search for data (like a customer name), expand my selection (which I don't do in the test code I uploaded) and copy the expanded selection to the destination named for the customer it applies to. So I'm using the customer name for double duty, both as the target of my search and the named cell that is the destination of the copied data. It helps keep my code data-driven and compact.

 

It will be more obvious if you take a look at the test.xls spreadsheet. You'll see that Copy (and Main) is the name of a cells as well as the literal data being moved around.

[Cory]

I beleive it's support-at-wintools.com

[jrgreenman]

Good tip. Will try there too. Thanks Cory!

[jrgreenman]

Hm. Bounced back: "Recipient address rejected: Access denied"

 

Re-sent to "info-at-wintools-dot-com". So far, so good.

[kevin]

Is a "support request" mae through some other web page??

For support options visit our Support page. There are a number of support options listed including an email link. The support page can also be reached by clicking 'On the Web' followed by 'Support' from within the program.

[jrgreenman]

Thanks Kevin. Stan got back to me and solved the problem. For everyone's reference, here's what he wrote:

 

========================================================================

Hi Jace,

 

I made one modification to your macro, adding a trim command after the Variable Set String. This seems to make it work correctly.

 

Variable Set String %Customer% from the clipboard contents

Variable Modify String %Customer%: Trim

 

Excel adds a bunch of hidden characters to a cell when it is copied to the clipboard. Trimming the variable strips the extra hidden characters. I noticed when I did the steps manually that pressing Enter twice pasted the text. One of the hidden characters may have been doing something similar to an Enter.

 

Thanks,

Stan

========================================================================

 

Thanks to everyone who contacted me to help out.

[Cory]

If you do not enter edit mode of a cell (F2) what ends up on the clipboard is a Tab file format. Each cell will be separated by a tab and each line will be terminated with a CRLF. Try copying and looking at what you get in a hex editor. If you choose to type this out it will in effect execute an Enter. Stan's solution is good but you also might consider simply pasting instead of emulating text.

[jrgreenman]

Thanks Cory. I've been spinning my wheels a bit and can't seem to find a hex editor that'll show me what's in the clipboard. Can you recommend one?

[Cory]

I use UltraEdit but it's money. I used to use one called HackMan which was cheap but one day I went to reinstall my apps and could not find it. Later I discovered it was bundled and now costs 21 pounds. I've heard of this free one named HexEdit which sounds interesting but I can't remember if I tried it or not. To my way of thinking hex editors should be one of those things that one can download for free. A quick Google search for free hex editors yields a number of hits. But I think your best bet is to go to download.com and search for hex editors and filter for free apps. I did so just now and there are 46 hits. The nice thing there is that you can read reviews and see overall user ratings.

 

How you get a hex editor to show you whats on the clipboard? Simply paste it into a blank text file! Of course that's not all that's on the clipboard but this is what will be passed to MEP if you set a var from the clipboard contents. But if yo uare interested in knowing more about the nature of what's on your clipboard check into ClipMate. It' a real handy utility to boot. Mainly I just use it for multiple copy and replace like that stupid Office Clipboard promised but never delivered.

[jrgreenman]

Thanks for the info Cory. Much appreciated.

[EnjoyRC]

Thanks Cory. I've been spinning my wheels a bit and can't seem to find a hex editor that'll show me what's in the clipboard. Can you recommend one?

Notepad++ can show you hidden characters

[jrgreenman]

Notepad++ can show you hidden characters

 

Sure enough Notepad++ reveals a trailing CRLF if one copies a cell from Excel. Live and learn.

 

Thanks for the help.

[jrgreenman]

check into ClipMate. It' a real handy utility to boot. Mainly I just use it for multiple copy and replace like that stupid Office Clipboard promised but never delivered.

 

I use one called Yankee Clipper (they also have a free version), but some months ago I found it (and another one called Clip Magic) interferes with some macros. I never figured out the cause, but perhaps it has something to do w/ keyboard hooks. I think clipboard management utilities try to be first in line to see keystrokes like MEP does.

 

But I'll try ClipMate and see if that one behaves any better.

[rvarnam]

But I'll try ClipMate and see if that one behaves any better.

Has anyone had any luck using MEP alongside any of the clipboard managers? I use Clipmate all the time, but haven't managed to get it to play nicely with any macro app.

[jrgreenman]

Has anyone had any luck using MEP alongside any of the clipboard managers? I use Clipmate all the time, but haven't managed to get it to play nicely with any macro app.

 

Sadly, no.

 

My "workaround" is to start my clipboard manager, Yankee Clipper, only when I know I need to cut/paste multiple items. Then I shut it off.

 

I'm not sure if I ever submitted a bug report about it, but will do so. If you submit one too, maybe the issue will get some quick attention.

[redwards]

I use ClipMate v7 with MacroExpress Pro. The only time the two don't play well together is when I want to use a feature in ClipMate called PowerPaste. When I need the PowerPaste feature I disable MEP then re-enable after. But I use PowerPaste rarely. Other than that I run both together all the time.

[paul]

I use ClipMate v7 with MacroExpress Pro. The only time the two don't play well together is when I want to use a feature in ClipMate called PowerPaste. When I need the PowerPaste feature I disable MEP then re-enable after. But I use PowerPaste rarely. Other than that I run both together all the time.

I've used Clipmate for years, and have never experienced any problems with the combination of MEP and Powerpaste. Note that in Windows 7 (x64?) Powerpaste doesn't work properly - it scrolls to the end of its list before I've had a chance to react to even the 1st entry!

[kevin]

Jace, I downloaded and installed Yankee Clipper on my computer last week. When I started the program it did not ask me to reboot. After a short time I noticed that it started to interfere with the clipboard operations in my macros. They were only corrected by terminating Yankee Clipper and terminating and restarting Macro Express Pro.

 

However, when installed, Yankee Clipper was set to load automatically when Windows starts. I have kept it running ever since and have not had a problem with it or with macro commands that utilize the clipboard. (I have not, however, used Yankee Clipper extensively.)

 

It is possible that a reboot is required after launching Yankee Clipper in order to allow it to work correctly with Macro Express Pro. It is also possible that the order that the two programs are loaded makes a difference.

 

But it seems that Yankee Clipper can successfully work when Macro Express Pro is running.

[jsampsonyyyyy]

I am trying Clipmate for the first time, on Windows 7 Home Premium 32-bit.

Powerpaste does not work properly when Macro Express Pro is running.

Symptoms are the same - selection runs up the list of clips without

user intervention. From previous items on the Web, it seems that any

macro software that monitors the clipboard will cause Powerpaste to

behave this way. My question is, can clipboard monitoring by

Macro Express Pro be turned off?

 

Regards

 

_John Sampson_

[paul]

I am trying Clipmate for the first time, on Windows 7 Home Premium 32-bit.

Powerpaste does not work properly when Macro Express Pro is running.

Symptoms are the same - selection runs up the list of clips without

user intervention. From previous items on the Web, it seems that any

macro software that monitors the clipboard will cause Powerpaste to

behave this way. My question is, can clipboard monitoring by

Macro Express Pro be turned off?

Sorry to disappoint you, but if you terminate MEP you'll still find that PowerPaste "works" in the same way as you described above.

I experienced the PowerPaste problem in Windows 7 within hours of installing W7. In a nutshell, PowerPaste has worked for me in W7 on exactly 3 occasions, none of which I can reproduce! The latest example of it working was the other day when I accidentally invoked PowerPaste: all my suspect software (UltraEdit, MEP, DOpus, Actual Window Minimizer, Everything, Executor, Firefox, Gdow, Katmouse, ResizeEnableRunner, Roboform, Logiitech SetPoint, True Launch Bar, UltraMon, Vmware) was running, and PowerPaste worked. I got quite excited, and immediately tried it again - this time it behaved as you described.

 

Unfortunately ClipMate was written in Delphi, for which there's no prospect of a 64-bit platform until long after that leaking oil well has been capped! I know you're not running 64-bit, but ClipMate has not been updated for a very long time, and I think the author (Chris Thornton) has simply given up.