We help IT Professionals succeed at work.
Get Started

VBA Clipboard Unstable

JackieMaw
JackieMaw asked
on
2,428 Views
Last Modified: 2008-01-09
The following code (Excel 2003) is unstable. After numerous iterations it crashes with the following errors:
      DataObject:PutInClipboard OpenClipboard Failed
      Paste Method of Worksheet class failed
      Out of Memory

This differs per machine, all running the same build of Excel, and is quite temperamental.
Sometimes it will run 1000 iterations, sometimes only 20.

Any ideas how i can make it more stable?

Thanks, Jackie

Sub ClipPaste(PasteText As String, PasteRange As Range)
   
    Dim doClip As DataObject
    Dim Wbk As Workbook
    Dim WKS As Worksheet
    Dim Rng As Range
    Dim WbkCur As Workbook
    Dim WksCur As Worksheet
    Dim RngCur As Range
   
    Set WbkCur = Application.ActiveWorkbook
    Set WksCur = Application.ActiveSheet
           
    Set doClip = New DataObject
   
    doClip.SetText PasteText

    doClip.PutInClipboard
   
    Set doClip = Nothing
    Set Wbk = Workbooks(PasteRange.Parent.Parent.Name)
    Set WKS = Wbk.Worksheets(PasteRange.Parent.Name)
    Set Rng = WKS.Cells(PasteRange.Row, PasteRange.Column)
    Wbk.Activate
    WKS.Select
    Rng.Select
    ActiveSheet.Paste
    WbkCur.Activate
    WksCur.Select
   
    Application.CutCopyMode = False
   
End Sub
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2008
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE