Pass keystrokes to another program?

[acollins]

Is it possible to write a VBA macro that passes keystrokes to another program? I have a spreadsheet with over 650 UPC codes for new products that have to be entered into our inventory system. I have downloaded and tried a few macro recorders that record keystrokes and mouse movements, but find them to be unreliable at best.

When I use the macro recorder to automate a task in Excel, (such as moving data from one sheet to another or importing data from a text file) I always go in after I'm done recording and add additional code to bullet-proof the macro (make sure the focus is set properly, etc.). I need to have this kind of control for this task and a macro recorder doesn't give me that (at least not these lower-end shareware programs I have been trying). I know that there are some higher-end macro recorders that include scripting that you can write and edit, but this our "slow" season business-wise and my supervisor has made it clear that he doesn't want to spend any money right now.

My task is simple (just very repetitive, which is why I don't want to spend all day sitting in front of my PC doing this):
1. Open Excel spreadsheet, go to starting data cell
2. Open 3rd party application and go to the appropriate data entry screen
3. Write a macro that does the following:
- checks to make sure the sheet is the active sheet
- copies contents of the active cell
- moves focus to other app and pastes data from active cell
- moves focus back to spreadsheet, copies the contents of the cell to the right (using ActiveCell.Offset?)
- makes other app active and pastes data
- repeat copy/paste for the next cell to right (in spreadsheet)
- press <ESC> twice to save record before returning focus to the spreadsheet
- move cursor down one line, repeat entire process until active cell is blank

Is this possible to do? The third party program is not able to listed as a reference in VBA, but I didn't know if it was possible to at least pass keystrokes to it. It is actually a terminal emulator (Anzio Lite). I guess the full-blown version of the program has some scripting capabilities (from the documentation I've read), but the Lite version does not.

Thanks,

Alan

 

[BrianB]

Any application in Windows can be run using VBA if you are prepared to put in the time and effort - which can be considerable. The main problem is that code runs too fast to allow time to happen on screen, so delays have to be inserted. The best attitude to take is that you are prepared to put longer, rather than shorter, delays and do something else while the process is running. This is especially the case if you are running a server-based application, when response times can be extremely variable. Try to have everything on a local hard drive.

Hope the following helps :-

'========================================================================================
'- SIMPLIFIED SENDKEYS EXAMPLE TO RUN AN EXTERNAL APPLICATION
'- Use keyboard only (not mouse) to get the keystrokes required, then convert to code.
'- NB. we cannot step through code because the code module becomes the active application
'-     so SendKeys results appear in the module.
'- Brian Baulsom June 1999-October 2007
'========================================================================================
'- Uses Windows Calculator to sum numbers 1 to 100 & put the total into the active worksheet
'- Being a Windows Application this works very easily.
'- In practice, with other applications, things become much more complicated.
'- The key to success is to use lots of Application.Wait + DoEvents lines to stop the code
'- for a second or two to allow time for things to happen on screen.
'- **Extend these times if things do not work as they should **. Slow is good.
'-
'- Sometimes Sendkeys does not work at all well, when it is necessary to use API calls
'- to mimic keyboard (KeyUp and KeyDown) - ref : Declare Sub keybd_event Lib "user32.dll"
'- This is a lot more stable.
'========================================================================================
Sub TEST()
    Dim MyData As DataObject    ' clipboard manipulation
    Dim ReturnValue, I
    '------------------------------------------------------------------------------------
    '- from VB Editor help
    ReturnValue = Shell("CALC.EXE", 1)  ' Run Calculator.
    AppActivate ReturnValue             ' Activate the Calculator.
    For I = 1 To 100                    ' Set up counting loop.
        SendKeys I & "{+}", True        ' Send keystrokes to Calculator
        '--------------------------------------------------------------------------------
        '- additional lines not needed here
        'DoEvents
        'Application.Wait Now + TimeValue("00:00:01")   ' stop code for 1 second
       '--------------------------------------------------------------------------------
    Next I                              ' to add each value of I.
    '------------------------------------------------------------------------------------
    '- copy value to clipboard
    SendKeys "^C", True                 ' Ctrl + C
    '------------------------------------------------------------------------------------
    '- paste to sheet
    '- This method used because it is the basis for larger applications.
    Set MyData = New DataObject
    MyData.GetFromClipboard
    ActiveSheet.Range("A1").Value = MyData.GetText(1)
    '------------------------------------------------------------------------------------
    '- close
    SendKeys "%{F4}", True  ' Send ALT+F4 to close Calculator.
End Sub
'============= END OF PROCEDURE =========================================================

'- FURTHER SendKeys etc. CODE SAMPLES

'- EXTERNAL APP. RUN PRINT SCREEN MULTIPLE TIMES AND PASTE PICTURES INTO A WORKSHEET
'  http://www.mrexcel.com/board2/viewtopic.php?t=170917

'- INCLUDES CODE TO WRITE MP3 DETAILS TO FILES IN EXPLORER
'  http://www.mrexcel.com/board2/viewtopic.php?t=280238&highlight=

'- RUN SAP : THIS INCLUDES SendKeys,API calls & Mouse.
'  http://www.mrexcel.com/board2/viewtopic.php?p=791077#791077

 

[hatman]

Brian... I have read multiple articles stating that Sendkeys() is flaky at best, and that it isn;t suported at all under Windows Vista. Most artcile sthat I have read suggest using the SendInput or SendMessage API.

I wa sstarting to build a response Alan's post using API's (don't have something handy in my back pocket, other than this example but you slipped in first. I don't want to nay say nor confuse Alan with too much overhead. Obviously your code is simpler than approach would end up (which is good for Alan) but I was wondering what your experience with Sendkeys() has been. Myself, the times I have tried using it for more than one or two simple strokes has ended in disaster, which drove me to do the research and change over to SendInput instead (I really haven't played with SendMessage, but I probably should have used in the other post I reference above... just haven't had the time). Anyway, just fishing for thoughts.

 

[BrianB]

experience with Sendkeys()

As you can see from the few samples supplied my experience with SendKeys has been extensive. There are also notes there which give clues concerning its use, as well as its limitations - mainly related to speed and the need to insert Wait lines and DoEvents. If the delays are long enough SendKeys will work fine - the art is to balance this with having the process run at a reasonable rate. Patience is the key.

I have had big problems with running server applications due to the variable response times during the day. Despite this, users have been happier with the process rather than without.

The API alternative often seems more stable but suffers from the same time limitations. We also have to imitate a key down and key up event for each character. SendKeys handles whole strings at a time.

This is the API subroutine I use in the SAP example found at
http://www.mrexcel.com/board2/viewtopic.php?p=791077#791077

'============================= 
'-  *** PARTIAL CODE ONLY *************
' API PRESS KEY 
'============================= 
Private Sub PressKey(k) 
    Call keybd_event(k, 0, 0, 0)          ' down 
    Call keybd_event(k, 0, VK_KEYUP, 0)   ' up 
    DoEvents 
    DELAY1 
End Sub

Hope this helps.

 

[hatman]

Thank you Brian. You obviously have much more experience with sending keystrokes in general than I do. You have given me much to think about, but even at a glance I can see that I may have overcomplicated my methods unnecessarily. Thak you again for taking the time to offer some pointers <:O)

 

[Legacy 98055]

Sendkeys. I have not had the success that Brian has had and very rarely use it. I'll use SendKeys for a throw-away procedure. It may work well for you and I would try it. Why not? If, however, this is a procedure that you intend to run repeatedly and dependably for some time, you may wish to look into other methods. Using the API has some great advantages over SendKeys. There are not usually any focus issues, it can transfer data more rapidly, runs asynchronously, there is no need to fiddle with the clipboard, and is altogether more dependable. Here is a very simple example of sending 1000 items in a range to notepad. Open a new session of notepad with the default window caption, "Untitled - Notepad". Place some data into a1 to a1000 and run ExampleUSAGE. If the text needs to be appended to existing text, it is very easy to do this as well...

<<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> FindWindow <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "FindWindowA" (ByVal lpClassName <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> lpWindowName <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> FindWindowEx <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "FindWindowExA" (ByVal hWnd1 <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> hWnd2 <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpsz1 <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> lpsz2 <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SendMessage <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "SendMessageA" (ByVal hWnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> wMsg <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> wParam <font color="#0000A0">As</font> Long, lParam <font color="#0000A0">As</font> Any) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> WM_SETTEXT = &HC

  <font color="#0000A0">Sub</font> ExampleUSAGE()
       DPTN "Untitled - Notepad", Join(Application.Transpose([a1:a1000]), vbCrLf)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Public</font> <font color="#0000A0">Sub</font> DPTN(WindowCaption <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> <font color="#0000A0">Text</font> <font color="#0000A0">As</font> String)
       <font color="#0000A0">Dim</font> NotePadHwnd <font color="#0000A0">As</font> Long, EditHwnd <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

       NotePadHwnd = FindWindow(vbNullString, WindowCaption)
       EditHwnd = FindWindowEx(NotePadHwnd, 0, "Edit", vbNullString)
       SendMessage EditHwnd, WM_SETTEXT, <font color="#0000A0">ByVal</font> 1, <font color="#0000A0">ByVal</font> <font color="#0000A0">Text</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("1026200710940402").value=document.all("1026200710940402").value.replace(/<br \/>\s\s/g,"");document.all("1026200710940402").value=document.all("1026200710940402").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1026200710940402").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1026200710940402" wrap="virtual">
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
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 SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Private Const WM_SETTEXT = &HC

Sub ExampleUSAGE()
DPTN "Untitled - Notepad", Join(Application.Transpose([a1:a1000]), vbCrLf)
End Sub

Public Sub DPTN(WindowCaption As String, ByVal Text As String)
Dim NotePadHwnd As Long, EditHwnd As Long

NotePadHwnd = FindWindow(vbNullString, WindowCaption)
EditHwnd = FindWindowEx(NotePadHwnd, 0, "Edit", vbNullString)
SendMessage EditHwnd, WM_SETTEXT, ByVal 1, ByVal Text
End Sub</textarea>

As for your application, Anzio Lite, I have no specific examples available because I do not have the program.

 

[iknowu99]

i'd like to say:

Set fs = CreateObject("Scripting.FileSystemObject")
Set ag = fs.CreateTextFile("C:\" & "Name" & ".txt", True)
ag.WriteLine ("hello world")
ag.Close

for text files


and more on topic - when you need to send keys use http://www.macroexpress.com - it would be great to see the engine from the app used in excel! (thus my next post)

 

[Legacy 98055]

That is fine for writing to a disk file but not to a window in a running application.

 

[Greg Truby]

Tom,

That's gonna work OK if you're just dumping to that application. But ANZIO is a terminal emulation program. So I am going to guess that Alan is looking to skip about on the screen (probably using the TAB key?) and input stuff in different fields. I use our terminal emulation software's object model (rickety, but works most of the time) to define a screen object and input at specific coordinates. Is there any way to use generic functions like you're doing with Notepad to hit specific fields or screen areas? (I am going to be *seriously* impressed if you can, BTW :wink.

Regards,

 

[Legacy 98055]

Greg. I don't know how to send data to a specific area. I do not even know what control the text area on a terminal emulator might be. A big ole' textbox? Don't know. In that case, seeing that a terminal type app is much more keyboard friendly, I would use sendkeys. Even so, I would not use the sendkeys function in VBA, but the API alternative which, in my opinion, is better. See this page.
http://www.vbaccelerator.com/home/VB/Tips/SendKeys_using_the_API/article.asp