How to use sendkeys with vba excel

Question

this is my code for excel (a) actually what i am trying is to send data to another excel (b) witch is locked (i can only press command & message buttons) and i have no access to the code (vba), i can only insert my data from (a) and for step_1 i have to press a), b), d), e) command buttons and c) a message box in order to go to step_2 where i have to one more time in order to get my results. Finally, when my results are ready i can't find the way to copy them from (b) to (a) PROJECT.xls = b ThisWorkbook = a

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
    Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
    Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Generate_XL(start_row As Integer, end_row As Integer)

    Dim xlWorkbookNewFileName As String
    Dim i As Integer
        Dim cmt As Comment
        Set cmt = ActiveCell.Comment
    For i = start_row To end_row
        Dim wb As Workbook
        Dim wb1 As Workbook
        Dim FileName As String
        Dim FilePath As String
        FileName = Cells(i, 1).Value


                             If Cells(i, 20) = "s0" Then
                                    Set wb = ThisWorkbook
                                    Set wb1 = Workbooks.Open("H:\PROJECT.xls")

'STEP_1
Worksheets("sheetA").Select
    ' max X = 1250
    ' max Y = 1250
Application.DisplayAlerts = False

    SetCursorPos 1050, 350  'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
Sleep 50
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
IsTimeToStop = True
Application.Wait (Now() + TimeValue("00:00:01"))
        Worksheets("Menu").Select
SetCursorPos 150, 530   'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
Sleep 50
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
IsTimeToStop = True
Application.Wait (Now() + TimeValue("00:00:01"))
    If cmt Is Nothing Then
Application.Wait (Now() + TimeValue("00:00:01"))
SendKeys "~"  '   pataw <ENTER>
End If
IsTimeToStop = True
Application.Wait (Now() + TimeValue("00:00:01"))
    SetCursorPos 150, 610 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
Sleep 50
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
IsTimeToStop = True
Application.Wait (Now() + TimeValue("00:00:01"))
     SetCursorPos 150, 650 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
Sleep 50
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
IsTimeToStop = True
Application.Wait (Now() + TimeValue("00:00:01"))


'STEP_2
        SetCursorPos 505, 434 'x and y position
    mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
    Sleep 50
    mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    IsTimeToStop = True
SendKeys "~"
SendKeys "~"
    SendKeys "{RIGHT 5}"
         SendKeys "^c"
IsTimeToStop = True
Application.Wait (Now() + TimeValue("00:00:01"))


'SendKeys "%{ }N"  ' = (ALT&tab)
'SendKeys "^a"
'SendKeys "^a"
'Range("l2").Select
'Cells(i, 12).Select

                             ' ElseIf Cells(i, 20) = "a0" Then
                             ' MsgBox ("REST")
                             '       Else: Cells(i, 20) = "x2" Then
                             ' MsgBox ("NOTHING)
                        End If  'Select
    Next i
End Sub

What code are you using? Why are you using `SendKeys` it's a fairly poor way to handle it especially when I'm guessing you could probably achieve this programatically if it is between two workbooks. — Tom, Oct 14 '17 at 09:13
How to use sendkeys? Short answer: don't! There is almost always a better way to do whatever it is you are trying to do. If this is one of those rare situations where `SendKeys` is the only viable solution, it would help if you would actually show the code. Invisible code is rather hard to debug. Please read [mcve]. — John Coleman, Oct 14 '17 at 09:15
Even if your other Excel is a separate instance it might be an idea to get the other instance as Object(https://msdn.microsoft.com/en-us/library/e9waz863(v=vs.90).aspx), and then work thru typical Excel Object Model. — MacroMarc, Oct 14 '17 at 13:25

score 0 · Answer 1 · answered Oct 14 '17 at 09:49

Send keys does the same thing as just pressing the keys on the keyboard, exactly as things are on your screen. You can only control where keys are "sent" the same way you can control where keys are sent when you're typing... that is, you need to first activate the where you want the key actions to take effect. In VBA, you'll need to activate the other window first.

That said... there should be no real need to send keys to another Excel window as virtually everything can be controlled directly through the object model. If you ask a question about what you're actually trying to achieve, you'll get help in delivering a much better solution.

How to use sendkeys with vba excel

1 Answers1