AppActivate and SendKeys

Sub Rec()
AppActivate ("Form One")
SendKeys "{TAB}"
SendKeys "{9}"
End Sub

Hi Folks,

I am trying to create a string of code that takes values a user inputs onto the macro-based spreadsheet and inputs them into a window called "Form One"

AppActivate ("Form One")

this part of the code works great in that it activates the window "Form One"

The next part doesn't work at all. I am trying to use the "Tab" button to move the cursor to the next field and 'type' the number "9" but it isn't working at all. And by not working at all I mean that the macro selects the window "Form One" and then nothing happens.

The weird thing is, if I run the macro enough times (repeatedly hitting the "Start" box I have assigned the macro to), the Macro does tab over and type 9 into the next field.

Can someone please troubleshoot?

Thanks!

If you have UAC on, you should turn it off.

This method is prone to errors. There are two issues critical other than UAC: (1) focus and (2) timing.

Add a wait after the AppActivate. Use False or True for those two commands 2nd parameter can sometimes help. I would definitely use True for the AppActivate.

Thank you for your response. I edited the code:

Sub Rec()
AppActivate ("Note Entry"), True
SendKeys "{TAB}", True
SendKeys "{9}", True
End Sub

The first time I tried it, it worked, but after repeated attempts to replicate, no success. Thoughts?

Also, you said "this method" is prone to errors. Are there other methods (I'm sure there are), would you be able to elaborate on those?

Thanks!

The other method would use API commands. API commands like FindWindow() are sometimes used. Other commands can be used to search for the windows handle by its classname.

Some API code to find a windows handle or a specific control's handle can be easy with a spy tool. See the 2nd link from the bottom for a free one that I use. http://patorjk.com/blog/software/

API command SendMessage() would send the keys. Some call a derivative of it, SendInput(). Search for this in google and you should find some examples. "Alternative Sendkeys vba"

Other API commands use these two keywords: "WM_KEYDOWN" and "WM_KEYUP".

e.g
http://www.vbaexpress.com/forum/show...ighlight=keyup

Some have written Classes that make sending keys a bit easier.

The main thing is find the windows handle for the dialog you want to send keys, add a delay, then send the keys and more delay added as needed for each key or set of keys.

Originally Posted by Kenneth Hobson

The other method would use API commands. API commands like FindWindow() are sometimes used. Other commands can be used to search for the windows handle by its classname.

Some API code to find a windows handle or a specific control's handle can be easy with a spy tool. See the 2nd link from the bottom for a free one that I use. http://patorjk.com/blog/software/

API command SendMessage() would send the keys. Some call a derivative of it, SendInput(). Search for this in google and you should find some examples. "Alternative Sendkeys vba"

Other API commands use these two keywords: "WM_KEYDOWN" and "WM_KEYUP".

e.g
http://www.vbaexpress.com/forum/show...ighlight=keyup

Some have written Classes that make sending keys a bit easier.

The main thing is find the windows handle for the dialog you want to send keys, add a delay, then send the keys and more delay added as needed for each key or set of keys.

I have heard about this, but also heard that is a very delicate process, easily susceptible to input errors which can cause the excel/other programs to crash and lose all work?

API methods can be tedious. Anyway you look at it, you can shoot yourself in the foot if you send the wrong keys or send them to the wrong window in any method that you choose. For reliability over Application.SendKeys, I would choose an API method.

While I don't know your other application, this example shows how to deal with the notepad application using API methods.

Option Explicit


Const NPMsg As String = "Notepad ID = "

Private Const GW_CHILD = 5
Private Const WM_SETTEXT = &HC
Private Const WM_GETTEXT = &HD
Private Const WM_GETTEXTLENGTH = &HE
Private Const WM_CLOSE = &H10
Private Const EM_REPLACESEL = &HC2
Private Const EM_SETSEL = &HB1
Private Const EM_SETMODIFY = &HB9
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
Private Const SWP_NOSIZE = &H1
Private Const SWP_NOMOVE = &H2

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
  (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
  ByVal lpsz2 As String) As Long
  
Private Declare Function GetWindowThreadProcessId Lib "user32" _
  (ByVal hWnd As Long, lpdwProcessId As Long) As Long
  
Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA" _
  (ByVal hWnd As Long, ByVal lpString As String) As Long
  
Private Declare Function GetWindow Lib "user32" _
  (ByVal hWnd As Long, ByVal wCmd As Long) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
  (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
  ByVal lParam As Long) As Long
  
Private Declare Function SendMessageStr Lib "user32" Alias "SendMessageA" _
  (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
  ByVal lParam As Any) As Long
  
Private Declare Function MoveWindow Lib "user32" _
  (ByVal hWnd As Long, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, _
  ByVal nHeight As Long, ByVal bRepaint As Long) As Long
  
Private Declare Function SetWindowPos Lib "user32" _
  (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, _
  ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

''To update the cell contents from Notepad.
Public Function GetNote(Ra As Range, hWnd As Long) As Boolean
  GetNote = False
  If hWnd = 0 Then Exit Function
  Ra.FormulaLocal = ReadNotepad(hWnd)
  GetNote = True
End Function

'Passing the cell contents in Notepad.
'HWnd new start time is omitted, it returns the hWnd.
Public Function PutNote(Ra As Range, Optional hWnd As Long = 0) As Long
  Dim strText As String
  If hWnd = 0 Then hWnd = OpenNotepad()
  PutNote = hWnd
  If hWnd = 0 Then Exit Function
  strText = Ra.FormulaLocal
  strText = Replace(strText, vbLf, vbCrLf)
  WriteNotepad hWnd, strText
End Function

'HWnd unsaved to clear the flag specified in Notepad.
Public Function SetSavedNotepad(hWnd As Long) As Long
  Dim i As Long
  i = GetWindow(hWnd, GW_CHILD)
  SendMessage i, EM_SETMODIFY, 0, 0
  SetSavedNotepad = i
End Function

'HWnd Close Notepad specified.
Public Sub CloseNotepad(hWnd As Long)
  SetSavedNotepad hWnd
  SendMessage hWnd, WM_CLOSE, 0, 0
End Sub

'Start a new notepad, hWnd returned.
Public Function OpenNotepad(Optional iWindowState As Long = vbNormalFocus) As Long
  Dim hWnd As Long
  Dim ProcID As Long, ThreadID As Long
  Dim i As Long, j As Long, k As Long
  On Error GoTo Err1
  i = Shell("notepad.exe", iWindowState)
  If i = 0 Then GoTo Err1
  hWnd = 0
  Do
    'hWnd = FindWindowEx(0, hWnd, "Notepad", "?? - ???")
    hWnd = FindWindowEx(0, hWnd, "Notepad", vbNullString)
    If hWnd = 0 Then GoTo Err1
    ThreadID = GetWindowThreadProcessId(hWnd, ProcID)
  Loop Until i = ProcID
  i = SetWindowText(hWnd, NPMsg & ProcID)
  'MoveWindow hWnd, 0, 50, 300, 200, 1
  'Z-order if you change the SetWindowPos
  SetWindowPos hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
  OpenNotepad = hWnd
  Exit Function
Err1:
  MsgBox "Error", , NPMsg
  OpenNotepad = 0
End Function

'HWnd content specified in Notepad, replace the specified character.
Public Function WriteNotepad(hWnd As Long, strTextAll As String) As Boolean
  Dim i As Long
  i = GetWindow(hWnd, GW_CHILD)
  WriteNotepad = (0 <> SendMessageStr(i, WM_SETTEXT, 0, strTextAll))
End Function

'HWnd specified in Notepad, add the specified character. ????? With a new line.
'IPos = 0: the current cursor position
'-1: Top
'1: last
Public Function WriteLineNotepad(hWnd As Long, strText As String, Optional iPos As Long = 0) As Boolean
  WriteLineNotepad = WriteTextNotepad(hWnd, strText & vbNewLine, iPos)
End Function

'HWnd specified in Notepad, add the specified character. No newline.
'IPos = 0: the current cursor position
'-1: Top
'1: last
Public Function WriteTextNotepad(hWnd As Long, strText As String, _
  Optional iPos As Long = 0) As Boolean
  Dim i As Long
  i = GetWindow(hWnd, GW_CHILD)
  Select Case iPos
    Case -1
      SendMessage i, EM_SETSEL, 0, 0
    Case 1
      SendMessage i, EM_SETSEL, 0, -1 'select all
      SendMessage i, EM_SETSEL, -1, 0 'Deselect (move the cursor to the end of the selected area)
  End Select
  WriteTextNotepad = (0 <> SendMessageStr(i, EM_REPLACESEL, 0, strText))
End Function

'HWnd specified the contents of the Notepad, you get a letter.
Public Function ReadNotepad(hWnd As Long) As String
  Dim i As Long
  Dim j As Long
  Dim x As String
  i = GetWindow(hWnd, GW_CHILD)
  j = 1 + SendMessage(i, WM_GETTEXTLENGTH, 0, 0)
  x = String(j, Chr(0))
  SendMessageStr i, WM_GETTEXT, j, x
  ReadNotepad = x
End Function

And this is accomplished through a Macro in Excel?

I am not sure what you mean. All of the API code that I posted is VBA macro code.

I'm sorry, my understanding/aptitude is relatively low. What I mean is, I tried putting the code above into an Excel Macro, behind a "Start Button" (Insert button wrote, "Start", assigned macro), opened Notepad, tried to run the macro and it did not work...

What am I missing?

Where can I go to learn more?