VBA task automation - stuck on sendkeys? Possible alternatives?

[Adamz]

Hi everyone,

I've been self studying VBA for a few months (I had no programming experience before that) and am stuck on a project I tried to do for fun / as a test for myself. Any help or guidance in the right direction would be much appreciated.

Basically I wanted to make a spreadsheet that would automate basic mouse clicks and keyboard inputs (not limited to within excel), but I cant get the keyboard input part to work.
The problem seems to be that the VBA window becomes active after each line is executed, so the sendkeys function sends the keys to VBA (is that right?)
If that is the issue, is there a way to stop the VBA window from becoming active?
If not, can anyone recommend an alternative way I could do this?

Here is what I have:

Example sheet structure:

VBA Code:

Public Sub RunInputSheet()
Dim Action_Type As String
Dim wait_time As Date
Dim Xposition As Long
Dim Yposition As Long
Dim clicks As Long
Dim KeysString As String

Worksheets("Inputs").Range("B2").Select

Action_Type = ActiveCell.Text
wait_time = ActiveCell.Offset(0, 1).Value
Xposition = ActiveCell.Offset(0, 2).Value
Yposition = ActiveCell.Offset(0, 3).Value
clicks = ActiveCell.Offset(0, 4).Value
KeysString = ActiveCell.Offset(0, 5).Text


Do Until IsEmpty(ActiveCell.Value)
Application.wait (Now + TimeValue(wait_time))
Select Case Action_Type
Case "Mouse Click"
Call Set_Cursor_Pos2(Xposition, Yposition)
Call ClickXAmountsOfTimes(clicks)
Case "Send Keys"
SendKeys KeysString, 10000
End Select
ActiveCell.Offset(1, 0).Select
Loop
MsgBox ("Macro Complete")
End Sub

 

[lrobbo314]

There is a special windows api that you need to invoke at the top of your code. Then use the appactivate function. I put the code in below. Let me know if that works for you.

Option Explicit

Private Declare Function FindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) _
    As Long
   
Private Declare Function APISetFocus Lib "user32" _
Alias "SetFocus" _
(ByVal hWnd As Long) _
As Long

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
                    ByVal hWnd As Long, _
                    ByVal lpOperation As String, _
                    ByVal lpFile As String, _
                    ByVal lpParameters As String, _
                    ByVal lpDirectory As String, _
                    ByVal nShowCmd As Long) As Long

Private Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Public Sub RunInputSheet()
Dim Action_Type As String
Dim wait_time As Date
Dim Xposition As Long
Dim Yposition As Long
Dim clicks As Long
Dim KeysString As String

Worksheets("Inputs").Range("B2").Select

Action_Type = ActiveCell.Text
wait_time = ActiveCell.Offset(0, 1).Value
Xposition = ActiveCell.Offset(0, 2).Value
Yposition = ActiveCell.Offset(0, 3).Value
clicks = ActiveCell.Offset(0, 4).Value
KeysString = ActiveCell.Offset(0, 5).Text

AppActivate "Untitled - Notepad" 'Change to the name that appears on the top bar of the application you want to set focus to

Do Until IsEmpty(ActiveCell.Value)
Application.Wait (Now + TimeValue(wait_time))
Select Case Action_Type
Case "Mouse Click"
Call Set_Cursor_Pos2(Xposition, Yposition)
Call ClickXAmountsOfTimes(clicks)
Case "Send Keys"
SendKeys KeysString, 10000
End Select
ActiveCell.Offset(1, 0).Select
Loop
MsgBox ("Macro Complete")
End Sub

 

[Adamz]

Sorry for the Delay. That Seems to work! thank you!

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂