3

I'm trying to simulate this action:

  • activate another application,
  • send keystroke ctrl+c,
  • go back to Excel,
  • send key stroke ctrl+v

and have that value in a cell.

It's a DOS window style application, so keystroke is the only way to manage it.

I managed to activate and to input keystrokes such as ENTER into that DOS style application, but when I try ctrl+C it is not seen to do anything.

I tried simulating it in Excel VBA with:

Range("E7").Select
SendKeys "^c"
Range("G7").Select
SendKeys "^v"

The E7 value is not copied, but G7 (paste destination) is highlighted as if it was selected for copying.

Note: I am not trying to copy things from Excel to Excel, but to execute keystrokes using Excel.

Community
  • 1
  • 1
Peter P.
  • 51
  • 1
  • 1
  • 3
  • 2
    Can you post some code? Are you sure you're using the right Send Key command for the key stroke you want to send? (list here --> http://www.autoitscript.com/autoit3/docs/appendix/SendKeys.htm). Note that the Enter key is {ENTER}, but not all keys are that intuitive ({LCTRL} for left ctrl key, for example). – Gojira Mar 07 '13 at 22:29
  • instead of `SendKey "^c"`, simply use `Range("E7").Copy` – Peter Albert Mar 11 '13 at 07:37
  • Have you solved the problem? I encounter the same issue. – Max Segal Dec 22 '14 at 11:44

3 Answers3

2

I ran into the same issue today.

I solved it by waiting a bit after sending CTRL-C to the application. It seems nothing is copied if you immediately execute another script line.

SendKeys "^c"
WScript.Sleep 100 ' wait a bit after CTRL+C (otherwise nothing is copied)

In fact, the same issue seems to happen when switching to another app and sending CTRL+V. Again, I solved it by waiting a bit:

AppActivate "Microsoft Excel"
WScript.Sleep 100 ' wait a bit after making window active
SendKeys "^v"
Tom Saenen
  • 141
  • 7
  • `WScript.Sleep` isn't available to VBA. – Daniel Oct 16 '13 at 14:19
  • @DanielCook Thanks, I was unaware of that. I was indeed using VBS. Sleep in VBA is covered here: http://stackoverflow.com/questions/469347/is-there-an-equivalent-to-thread-sleep-in-vba – Tom Saenen Oct 16 '13 at 14:31
  • Yes adding WScript.Sleep helps i had noticed that, but unfortunately even after adding delay of WScript.Sleep 500 (half second) sometimes it false say once in 10 tests and "^c" produces "c" instead of doing 'copy'. In addition to that i had even tried "^(c)" but same result, so if anybody had better solution than please share – hi0001234d Jul 19 '16 at 09:35
  • @hi0001234d - Did you get solution? Same issue for me. When I tried `sendkeys "^v"` it pastes only "v" instead of pasting. – Learning Jan 11 '17 at 12:18
  • actually i had not get any standard solution. But my requirements were changed so what i had to paste was now available to program as string variable so i had just send that variable to be written on text directly. tricky solution! – hi0001234d Jan 13 '17 at 14:12
2

The problem is that copying and pasting takes some time and VBA is not waiting for it.

First you need to specify second argument of send keys method which is "Wait". Set it to true. Thus VBA execution is waiting for until sending keys is completed.

Secondly you need to wait until process of copying data to clipboard is completed. "Wait" in sendkeys is not doing it because it's not about sendking keys by VBA but it's about Windows working with clipboard. To do it please use my function IsCopyingCompleted.

Here's how final can look like:

SendKeys "^a", True 'Select all
SendKeys "^c", True 'Copy
Do
    DoEvents
Loop Until Me.IsCopyingCompleted()
YourSheet.Paste

Function IsCopyingCompleted() As Boolean
'Check if copying data to clipboard is completed

    Dim tempString As String
    Dim myData As DataObject

    'Try to put data from clipboard to string to check if operations on clipboard are completed
    On Error Resume Next
    Set myData = New DataObject
    myData.GetFromClipboard
    tempString = myData.GetText(1)
    If Err.Number = 0 Then
        IsCopyingCompleted = True
    Else
        IsCopyingCompleted = False
    End If
    On Error GoTo 0

End Function
DMM Wallnut
  • 21
  • 5
0

I had this same problem - I developed code and it worked on my computer - SendKeys "^v"...but it did not work on another user's computer! I was trying EVERYTHING...delays, appreciate, accessig access from Excel, having the user check various settings, selection.paste...no good. Finally I noticed different syntaxes...we were using the same office version, I'm not sure about Windows. But I entered 6 different syntaxes and had it skip over if it failed....SendKeys with a capital v, enclosed in squigley brackets, each component in squigleys separated by a plus sign, etc. IT WORKED! 2 of the commands put the ^V in the body of outlook...I remove those 2 and I'm golden.

David
  • 1