How to Force Clipboard to Update in VBA

Question

There is a task I must perform daily. It involves copying information from excel into company software. I don't have any control over this particular piece of company software. I am trying to automate the task. It involves copying selected cells into the clipboard and then making 9 mouse clicks through the company software, pausing about half a second after each click.

It all seems to work, except the last step. I get a message that there is nothing in the clipboard. But as soon as the macro exits, the information is in the clipboard. I paused the macro for 30 seconds just to make sure I wasn't rushing the clipboard. It still didn't recognize the clipboard as full until right after the macro had completed.

Its as if range.copy copies to the clipboard but does not commit changes until after the macro has completed. They are not finalized until the macros are completed.

I don't know if I am missing some command like : clipboard.acceptchanges or clipboard.finalize or clipboard.refresh. I am not sure what I am missing. It seems that other people have similar problems caused by time delays of the clipboard, but as I said it isn't simply a time delay issue for me. It happens no matter how much time I give it.

Thanks.

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
Public Declare PtrSafe Function CountClipboardFormats Lib "user32" () As Long


Worksheets("writeorder").Range("M" & firstrow & ":M" & lastrow).Copy
Sleep 500
Call transactionclicksworkcomp


Sub transactionclicksworkcomp()


SetCursorPos 517, 1059 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 500

SetCursorPos 954, 33 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 500

SetCursorPos 659, 1029 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 500

SetCursorPos 588, 898 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 1200

SetCursorPos 767, 895 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 1200

SetCursorPos 705, 718 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 1200

SetCursorPos 1668, 889 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 1200

SetCursorPos 1852, 897 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 1200

SetCursorPos 1852, 885 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0

Sleep 3000

End Sub

The last click is on a paste button within my work application that retrieves info from the clipboard. The macro clicks the paste and the software shoots me an error message(empty clipboard). But when I click the button directly after the macro is finished, the information is pasted correctly.

You're unlikely to get much help unless you provide the actual code you're using. — Tim Williams, May 03 '23 at 18:47

score 2 · Answer 1 · answered May 03 '23 at 20:28

2

The first thing to try in cases like this is DoEvents, like this:

Worksheets("writeorder").Range("M" & firstrow & ":M" & lastrow).Copy
DoEvents
Sleep 500

It allows Excel to catch up before the rest happens.

answered May 03 '23 at 20:28

Sam

5,424
1
18
33

score 1 · Answer 2 · answered May 03 '23 at 22:14

This works for me, but it's not really different from your posted code, with the exception I'm using it to click around in Excel:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Declare Function SetCursorPos Lib "user32" (ByVal X As Long, ByVal Y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4

Sub transactionclicksworkcomp()
    'SetClipText "Hello" 'pastes OK
    Range("L1:L10").Clear
    Range("A1:A10").Copy
    
    LeftClickAndWait 2220, 360, 500
    LeftClickAndWait 2236, 400, 500
    
    ActiveSheet.Paste Destination:=Range("L1") 'pastes OK
End Sub

Sub LeftClickAndWait(xPos, yPos, mSec)
    SetCursorPos xPos, yPos 'x and y position
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    Sleep 3000
End Sub

You might try setting the clipboard text content (in place of copying the range) and see if that makes any difference.

Chronocidal · Answer 3 · 2023-05-04T09:00:53.317

0

If DoEvents doesn't work, then another method to allow Excel to "catch up" is through the use of Application.OnTime to schedule a second macro.

Sub FirstPart()
    'Yadayadayada
    'Copy cells to clipboard
    Worksheets("writeorder").Range("M" & firstrow & ":M" & lastrow).Copy
    'Schedule part 2 to happen after this macro ends
    Application.OnTime Now(), "SecondPart"
End Sub

Sub SecondPart()
    'This will start immediately, but the previous Macro did finish first
    Call transactionclicksworkcomp
    'et cetera, et cetera
End Sub

edited May 04 '23 at 09:00

answered May 03 '23 at 20:41

Chronocidal

6,827
1
12
26

1

I think you forgot to remove `Sub` from the middle of SecondPart – Sam May 03 '23 at 21:12

score 0 · Accepted Answer · answered May 04 '23 at 13:51

I attempted both the DoEvents and the Application.OnTime fixes. Neither of these fixed the problem.

But I did follow the suggestion of manually adding my text to the clipboard as opposed to copying to the clipboard. This solved my problem.

This is my modified solution.

Sub copytoclipboard(strstuff As String)

With CreateObject("htmlfile")

    With .parentWindow.clipboardData
        .setdata "text", strstuff
    End With

End With


End Sub

I found fuller instructions on doing so here:

https://stackoverflow.com/a/60896244/11937392

Thanks

How to Force Clipboard to Update in VBA

4 Answers4