Excel Clipboard Issues and copying range as a picture

[korhan]

Hello everyone,

I am having a little really nasty issue with VBA where I cannot find any solution at all. I am getting this infamous warning message where it displays
"We couldn't free up space on the clipboard. Another program might be using it right now". VBA gets into debugging mode and when I hit debug and then F5, it continues for a while and then the same message pops up. So annoying.
I am pretty sure this is caused by copying and pasting a range as a picture.
I am using the method below to copy a range as a picture:

rngHours.CopyPicture xlScreen, xlPicture
Set picHours = wsTemp.Pictures.
picHours.Left = .Range("D" & .Cells(Rows.Count, Range("D1").Column).End(xlUp).Row + 2).Left
picHours.Top = .Range("D" & .Cells(Rows.Count, Range("D1").Column).End(xlUp).Row + 2).Top

Is there anyway to overcome this problem?

Note: Please, don't give me Application.CutCopyMode = False. It doesn't work

Sub CopyToClipboard()
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    
    clipboard.SetText ActiveSheet.Range("B2")
    clipboard.PutInClipboard
End Sub

doesn't work.

Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long


Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

doesn't work.

This is a ridiculous error. There should be a solution.

 

[korhan]

Alright, I think I have found a solution.
After checking the usual error causing problems from the task manager, I have found which program is causing this issue.
First of all, I have tried turning of iexplore.exe via VBA. That did not do anything and it is weird that as soon as I run the macro somehow, iexplore.exe starts. Anyways, killing "Skype for Business" process didn't do anything neither.
Finally, I have decided to trap the error in a loop like this

            On Error Resume Next
            rngHours.CopyPicture xlScreen, xlPicture
            If Err.Number = 1004 Then
                ' Trap the error due to Clipboard
                Do
                    Err.Clear
                    rngHours.CopyPicture xlScreen, xlPicture
                Loop Until Err.Number <> 1004
            End If
            On Error GoTo 0
            Set picHours = wsTemp.Pictures.Paste

After this, I saw that the Outlook was the problem. Whenever, program falls into the loop, I have noticed that Outlook stopped responding until the error was resolved.
It works as is, meaning, program loops until it can copy; however, it takes time. Killing Outlook via macro is a way better approach in terms of performance. There was a significant decrease in the total processing time.
Close to 10 min. reduction. I was monitoring the total processing time with and without the Outlook. That is significant but, of course, this is not convenient for a person who needs the outlook to be open at all times.
Anyways, feel free to comment or use the code.