How do I paste from clipboard to range using Excel VBA

[Guest]

I have just moved from Windows 2000 & Office 2002 (Old) to XP Pro and Office
2003 (New). The following statements work in the old version and also in
the new version when I step through the code using the debugger. They fail
when I try and run the macro in the new version.

Code:
Dim oSheet As Worksheet
' copy text to windows clipboard
oSheet.Paste
' error generated on the above stating "Paste method of Worksheet Class
failed."

 

[Dave Peterson]

Did you set osheet to the correct sheet?

Was that sheet protected?

Any chance you ran something that cleared the clipboard?

maybe:

if application.cutcopymode = false then
msgbox "nothing to paste"
else
osheet.paste
end if

If none of this helped, you'll have to include some more details.

 

[Guest]

Yes, the cutcopymode returns false. But when when it breaks, I step through
the debugger, the data gets copied from the clipboard, so it is there.

The data is copied to the clipboard by using a DDE command to the
"Bloomberg" application to copy the screen contents to the windows clipboard.
This instruction doesn't appear to notify the XP operating system that the
data is there. When it breaks and I step through, the XP operating system
then is able to find the data on the clipboard.

I will try and access the API directly (although I haven't had to do this
before) and debug.print as much info as I can to find this data when I run
the VBA in real-time. Thanks for your pointer and any more comments would be
appreciated. Thanks.

 

[Dave Peterson]

I've never used any DDE stuff.

I don't have any other guesses.

 

[Guest]

It appears that the XP operating system required my Excel VBA aaplication to
explicitly activate itself (although Windows 200 did not require this!). The
following code worked in XP:

AppActivate Application.Name
oSheet.Paste

 

[Dave Peterson]

Glad you posted back with your fix--now google knows!