Pasting problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using excel to link with another application (from which I am taking some data). The time to get this data can vary. If it finds the data in the time I have allowed for it, it will paste this perfectly into excel. If it does not find the data in time, I assume that it is trying to paste something which is not there. I get a runtime 1004 error Paste method of worksheet class failed. Is there anyway around this. Can I write a loop procedure to only try to paste once there is something in the clipboard. Any help would be much appreciated.

Thanks
 
Chip Pearson has some nice notes at:
http://www.cpearson.com/excel/clipboar.htm
for working with the windows clipboard.

This doesn't look very pretty, but it seemed to work ok:

Option Explicit
Public Sub ClearClipboard()
Dim MyDataObj As New DataObject
MyDataObj.SetText ""
MyDataObj.PutInClipboard
End Sub
Sub testme()

Dim MyDataObj As DataObject
Set MyDataObj = New DataObject

Call ClearClipboard

On Error Resume Next
Do
MyDataObj.GetFromClipboard
If Len(MyDataObj.GetText) = 0 Then
'stay in the do loop
DoEvents
Else
Exit Do
End If
Loop
On Error GoTo 0
MsgBox MyDataObj.GetText

End Sub

I sometimes got errors when I went to a different application and did a copy.
I'm not sure if there was some timing issue between loading the clipboard and
trying to get it off the clipboard.

You may want to try it without the "on error" statements.
 
Back
Top