Open and Send Inputs to Another Program From Excel

[ransomedbyfire]

I am trying to open the PortfolioCenter Export Wizard from Excel and send it inputs to craft a custom export each day. But I can't get the SendKeys method to work. Besides, I've heard that SendKeys isn't very reliable anyway for this. How can I do this?

 

[Kenneth Hobson]

How do you do it manually?

If you use SendKeys(), turn off UAC.

Search for keybd_event code if you want UAC on. http://msdn.microsoft.com/en-us/library/ms646304(VS.85).aspx

I wrote a vb.net EXE that can be used. Of course one needs some of the vb.net framework files installed to use a vb.net EXE program. http://www.wpuniverse.com/vb/showthread.php?30618-VB.NET-109-Pass-Parameters-3-SendKeys

Some use programs like Auto-It.

 

[ransomedbyfire]

Thank you Kenneth. Your link to the keybd info led me to investigate the sendinput API; and this seems to be what I need. Does anyone know if it wil work - or if there is anything comparable that will work - on 64-bit system?

 

[Kenneth Hobson]

Yes, it should work however there are not that many examples and the code is more involved than keybd_event.

Here is an example:

Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub test()
 'NUM_Off
 NUM_On
End Sub

Sub NUM_TOGGLE()
  'Toggle NUM-Lock key state
  keybd_event VK_NUMLOCK, 1, 0, 0
  keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub

Sub NUM_On()  'Turn NUM-Lock on
  If Not (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

Sub NUM_Off() 'Turn NUM-Lock off
  If (GetKeyState(vbKeyNumlock) = 1) Then
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
  End If
End Sub

 

[ransomedbyfire]

Thank you so much!

 

[ransomedbyfire]

Okay. One more question. When I use the following code with Notepad, it works fine. But when I use it with another non-Microsoft program, the text ends up in Excel instead of the program I meant for it to be in. Is there a fix for this? Here is my code:

Const VK_TAB = 9
Const VK_ENTER = 13
Const VK_DOWN = 31
Const VK_UP = 30
Const VK_a = 65

...


Private Type KEYBDINPUT
  wVk As Integer
  wScan As Integer
  dwFlags As Long
  time As Long
  dwExtraInfo As Long
End Type

Private Type GENERALINPUT
  dwType As Long
  xi(0 To 23) As Byte
End Type

Private Declare Function SendInput Lib "user32.dll" _
(ByVal nInputs As Long, _
pInputs As GENERALINPUT, _
ByVal cbSize As Long) As Long
 
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(pDst As Any, _
pSrc As Any, _
ByVal ByteLen As Long)

Sub Test()
 
    Shell "C:\Program Files\Schwab Performance Technologies\PortfolioCenter\PCExportWizard.exe", 1
 
    'call the SendKey-function
    SendKey VK_TAB

...

End Sub

Private Sub SendKey(bKey As Byte)
 
    Dim GInput(0 To 1) As GENERALINPUT
    Dim KInput As KEYBDINPUT
    KInput.wVk = bKey  'the key we're going to press
    KInput.dwFlags = 0 'press the key
    'copy the structure into the input array's buffer.
    GInput(0).dwType = INPUT_KEYBOARD   ' keyboard input
    CopyMemory GInput(0).xi(0), KInput, Len(KInput)
    'do the same as above, but for releasing the key
    KInput.wVk = bKey  ' the key we're going to realease
    KInput.dwFlags = KEYEVENTF_KEYUP  ' release the key
    GInput(1).dwType = INPUT_KEYBOARD  ' keyboard input
    CopyMemory GInput(1).xi(0), KInput, Len(KInput)
    'send the input now
    Call SendInput(2, GInput(0), Len(GInput(0)))
    
End Sub

 

[Kenneth Hobson]

As with Sendkeys() you must set the focus and time it just right to use that method or methods like it. The focus is often set by a windows handle depending on the API method. This one reason why some of the Auto-It routines work well.

Getting a class name to use and API routine to find a windows handle is needed sometimes. If you don't know the class name, a spy routine can help. I like this free API spy program. http://patorjk.com/downloads/patorjkapispy51.zip

Since NotePad is really text file, there are many ways to write to it in the background which is very fast. Here is a SendKeys() method:

Sub SavePartcorrect()
  Dim myPath As String, txtPath As String
  Dim rc As Long
  Dim wb As Workbook
  
  Set wb = ActiveWorkbook
  myPath = ThisWorkbook.Path & "\"
  txtPath = myPath & "Test.txt"
    
  rc = Shell("NOTEPAD.EXE " & txtPath, vbNormalFocus)
  AppActivate rc
  Application.Wait Now + TimeValue("00:00:01")
  SendKeys Application.UserName, True
  SendKeys "{Enter}", True
End Sub

 

[ransomedbyfire]

Okay. So, I've run into one more problem. I have successfully opened a program and sent it Enter and Tab keys; but for some reason, the "p" I am trying to send just won't take.

Here is my code:

Private Declare Function BringWindowToTop Lib "user32" (ByVal _
 hwnd As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias _
 "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
 As Any) As Long
 
 Const VK_p = 120
Const VK_Enter = 13
Const VK_Tab = 9
Const KEYEVENTF_KEYUP = &H2
Const INPUT_MOUSE = 0
Const INPUT_KEYBOARD = 1
Const INPUT_HARDWARE = 2

Private Type MOUSEINPUT
  dx As Long
  dy As Long
  mouseData As Long
  dwFlags As Long
  time As Long
  dwExtraInfo As Long
End Type

Private Type KEYBDINPUT
  wVk As Integer
  wScan As Integer
  dwFlags As Long
  time As Long
  dwExtraInfo As Long
End Type

Private Type HARDWAREINPUT
  uMsg As Long
  wParamL As Integer
  wParamH As Integer
End Type

Private Type GENERALINPUT
  dwType As Long
  xi(0 To 23) As Byte
End Type

Private Declare Function SendInput Lib "user32.dll" _
(ByVal nInputs As Long, _
pInputs As GENERALINPUT, _
ByVal cbSize As Long) As Long
 
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(pDst As Any, _
pSrc As Any, _
ByVal ByteLen As Long)

Public Sub IEFrameToTop()
 Dim THandle As Long
 
 THandle = FindWindow("WindowName", vbEmpty)
 
  BringWindowToTop THandle
 
End Sub
Sub Test()

On Error GoTo callwait
 
    Shell "C:\Program Files\...ProgramName.exe", 1
    Call waitforprogram
    Call IEFrameToTop
    
    'call the SendKey-function
    SendKey VK_Enter
    SendKey VK_Enter
    SendKey VK_Tab
    SendKey VK_Tab
    SendKey VK_Tab
    SendKey VK_p
     
callwait:
Call waitforprogram

End Sub
Private Sub SendKey(bKey As Byte)
 
    Dim GInput(0 To 1) As GENERALINPUT
    Dim KInput As KEYBDINPUT
    KInput.wVk = bKey  'the key we're going to press
    If bKey = 13 Then
        KInput.dwFlags = KEYEVENTF_EXTENDEDKEY
        Else: KInput.dwFlags = 0 'press the key
    End If
    'copy the structure into the input array's buffer.
    GInput(0).dwType = INPUT_KEYBOARD   ' keyboard input
    CopyMemory GInput(0).xi(0), KInput, Len(KInput)
    'do the same as above, but for releasing the key
    KInput.wVk = bKey  ' the key we're going to realease
    KInput.dwFlags = KEYEVENTF_KEYUP  ' release the key
    GInput(1).dwType = INPUT_KEYBOARD  ' keyboard input
    CopyMemory GInput(1).xi(0), KInput, Len(KInput)
    'send the input now
    Call SendInput(2, GInput(0), Len(GInput(0)))
    
End Sub

Sub waitforprogram()

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub

 

[Jaye7]

I would like to use this api also, can you please advise the best source to find the key stroke scripts.

I want to do ctl T,
tab
down arrow
enter
right mouse click.