Problems pasting data to Office Clipboard

toveyj · Post by **toveyj** » Wed Jul 01, 2009 2:31 pm

Hi

I am having trouble with a bit of code I'm using to capture data from a webpage and paste it to Excel.
All I am doing is looping through a series of webpages, all of which have the same layout. Selecting the page, copying (using Control A, then Control C) and passing it to the clipboard. The code is simply:

MouseMove>posX,posY
LClick

Press CTRL
send>a
Release CTRL

PutClipBoard>
Press CTRL
send>c
Release CTRL

WaitClipBoard

Then I execute a macro in Excel to paste the data from the Clipboard into the spreadsheet. I have got this to work on a couple of machines but found on others that it doesn't work. When I attempt to paste the data it is not there on the clipboard.

Not sure if there could be some settings within Office that mean it works on some machines but not others. Any ideas?

Bob Hansen · Post by **Bob Hansen** » Thu Jul 02, 2009 12:35 am

Have you verified that it is not in the clipboard?

If you are using a Macro Scheduler macro script to do the pasting in Excel, make sure that you use a SetFocus after WaitWindow. Even if you are not using a Macro Scheduler macro, are you sure that the focus is in the desired cell before you paste from the clipboard?

Test for the clipboard content after you fill it, but before you proceed to paste it. You could send it to a variable and tiest for length, string content, etc. If it is OK, then proceed to pasting. If it is not OK, go back to the web page and do another Copy.

You could also do this to a variable and sook at the variable in the macro log to make determine if the problem is a Copy or a Paste issue.

JRL · Post by **JRL** » Thu Jul 02, 2009 4:58 am

Also, remove the PutClipBoard> line. If you are using Ctrl+C to copy to the clipboard you do not need the PutClipBoard> function. And, used as you have it with no parameters, it actually clears the clipboard.

toveyj · Post by **toveyj** » Thu Jul 02, 2009 8:50 am

Bob

Thanks for the thoughts. Once I have done the "Copy" I set Focus back to Excel, use a shortkey to kick off an Excel macro and attempt to Paste the data into the right place on the spreadsheet. I have an error-trap at this point which picks up if the clipboard is empty and lets me manually copy it again and then resume. I am finding that the code will run through a few loops and paste quite happily, but error out occassionally because the clipboard is empty. When I look at the webpage I can see the CtrlA has worked because the correct data on the page is highlighted but it seems not to have copied it to the Clipboard. I guess I couold try a loop in Macro Scheduler that tests the content and if it's empty copy again as you suggest.

As I say I have run this on different PCs. On some this error never happens, on others it seems to happen quite often. So it seems the code will work but not consistently on every PC - which is why I was wondering if it could be anything to do with settings in Office..

Bob Hansen · Post by **Bob Hansen** » Thu Jul 02, 2009 1:00 pm

Maybe you need to insert a Wait? after the CTL-A.?

You could experiment with a large value to confirm that works, then test reducing it to a smaller number that still does the job.

Did you look at a log after the copy to confirm that the clipboard was empty?

Post by **Marcus Tettmar** » Thu Jul 02, 2009 1:40 pm

I think you need a Wait too. At present you have no wait between the CTRL-1 and the CTRL-c - that means you are telling the script that it takes NO time to highlight all the text. But it definitely takes a bit longer than NO time. So give it a bit of a chance.

toveyj · Post by **toveyj** » Mon Sep 21, 2009 3:05 pm

I realise that I haven't replied for a long time on this one but I have never managed to find an answer. I tried adding Waits at lots of different places in the code; in between each of the "Press Ctrl" lines etc, wrote the clipboard data to a variable and found that the clipboard was empty each time. I tried putting in a loop to go back and Ctrl C again if the clipboard was empty with no success.

SetFocus>Internet *

Label>CtlC

MouseMove>posX,posY
LClick

Press CTRL
send>a
Release CTRL

Wait>.5
WaitClipBoard>

PutClipBoard> (note have tried without this line but it is useful to empty
clipboard)
Press CTRL
send>c
Release CTRL

WaitClipBoard

Getclipboard>cpbrd

MDL>%cpbrd% %posx% %posy%
Pos>UBRN,cpbrd,1,isitthere
If>isitthere=0
Goto>CtlC
EndIf

This just goes into an endless loop. The message shows me the PosX and PosY values which are in the correct place but no clipboard data. If I go back manually to the web browser window and do Ctrl C manually it immediately captures the data on the office clipboard. Any thoughts?

gdyvig · Post by **gdyvig** » Mon Sep 21, 2009 4:50 pm

Hi toveyj,

A couple of problems:
1. posx and posy not defined.
2. need wait after the send>c

Not sure why the WaitClipBoard is not working.

This works if your Internet * window is maximized:

Code: Select all

Label>CtlC
SetFocus>Internet *
Let>posX=100
Let>posY=5
MouseMove>%posX%,%posY%
LClick
PutClipBoard>
Press CTRL
send>a
Release CTRL

Wait>.5
WaitClipBoard>


Press CTRL
send>c
Release CTRL
Wait>.5
WaitClipBoard

Getclipboard>cpbrd

MDL>%cpbrd% %posx% %posy%
//Allow time to break out of macro
wait>1
Pos>UBRN,cpbrd,1,isitthere
If>isitthere=0
Goto>CtlC
EndIf

Gale

toveyj · Post by **toveyj** » Tue Sep 22, 2009 8:30 am

Hi Gale

Thanks for the thoughts. The PosX, PosY co-ordinates are set earlier in the code, based on searching for an image and then offsetting by a set number of columns/rows from where the image is found. Also the window is maximised earlier on in the code too - the code works fine up to the Ctrl A bit as it does correctly select the bit of the screen I wish to copy but it just doesn't add it to the Clipboard. I will try the extra Waits that you suggest below and see what happens. Thanks!

toveyj · Post by **toveyj** » Tue Sep 29, 2009 3:04 pm

Have tried putting the Wait> statements in as suggested but this doesn't solve the problem. I have tried this code on a variety of different PCs. On some the clipboard contains the correct data 75% of the time but is empty 25% of the time. On others it is empty 100% of the time. I have tried setting SK_Delay to different values (e.g. Let>SK_DELAY=10) in case this has some effect but can find no obvious difference whatever value I set it to. If using EXCEL I assume I am writing to the Office Clipboard so am still wondering if there could be any settings on individual PCs that cause this code not to work?

Totally puzzled so any further suggestions appreciated.

gdyvig · Post by **gdyvig** » Tue Sep 29, 2009 3:41 pm

Hi toveyj,

I assume you have already tried increasing the waits from .5 seconds to some very large waits. Also that you verified manually that Ctrl+V lets you paste the clipboard text into another application.

The GetClipboard only works for text. Try manually pasting into Notepad to make sure that is not an issue on some of the PCs.

You are already using SetFocus to make sure you are getting the clipboard text from the correct app. You might also try WaitWindowOpen and WaitReady prior to the SetFocus to make sure the window open and also fully rendered.

Try running your script against some simpler applications, like NotePad. When that works move on to more complicated apps.

Let us know if you are still having problems.

Gale

Post by **Marcus Tettmar** » Tue Sep 29, 2009 3:46 pm

I would break this right down. Make a script which ONLY focuses the web page and does CTRL-a followed by CTRL-c. I.e. ALL it does is attempt to copy to the clipboard. Run that script and verify that the data is in the clipboard by manually pasting it into Notepad or similar.

Make sure the correct part of the browser is being focused. E.g. it may need to be the page inside the browser that has the focus, not the browser itself.

Tweak until you have this little script working perfectly. Now you can add the part that does the paste. In fact, create that in isolation too.

Right now I can't see that we have any idea whether it's the copy that isn't working or the paste.

If still no luck contact support and let's hook up via BeamYourScreen so that we can get it working for you.

toveyj · Post by **toveyj** » Wed Sep 30, 2009 9:33 am

Thanks for the replies.
I am as sure as I can be that the problem is that the Copy to the clipboard is the bit that doesn't work. The web application I am screen-scraping from doesn't have any page structure so I have to look for particular items on the screen by matching them to a bitmap image and then I can offset a known number of rows/cols to put the cursor into the right part of the page and execute Ctrl A. So I don't think I can set the focus any differently??When I run the macro I jump from the MS code back into Excel to paste and also error trap it there if the clipboard is empty before I try to Paste. When it errors out if I manually go back to the web page the correct area of the screen is always highlighted (by the Ctrl A which has worked) but I have to manually execute the Ctrl C.

I have put in a Wait> up to 5 seconds between the Ctrl A and Ctrl C and the same after the Ctrl C and run the macro but this doesn't cure the problem. I can't imagine that going any higher than 5 would be necessary would it? If so it would be quicker to do it manually!

The difficulty is that the data I am screen-scraping is confidential so although I appreciate your offer I'm not sure I would get permission to have a remote link up.

Problems pasting data to Office Clipboard

Problems pasting data to Office Clipboard

Try this