VBA Sendkeys Question

Posted on 1997-11-07
Medium Priority
Last Modified: 2006-11-17
Within Access version 7....

I have a VBA sub that does many things.  At one point I use shell() to invoke an external application which copies a file from the PC to a unix box.  After shell(), I use Sendkeys() to fill in the remote copy application's dialog box.  When using Sendkeys I turn the wait flag = True.

Upon completion of the remote copy the VBA routine continues on doing other things.

The Problem:  The copy takes so long that Sendkeys() wait stops waiting and flow of execution continues while the copy is still in progress.  This screws up everything because subsequent keystrokes no longer are in synch with the place they are supposed to be entered.

The Question: Anyway for me to set/change the duration of the Sendkeys wait flag.  Any other timer that I should set/change to ensure that my processing stays sequential.  Any other ideas?

Question by:msaccess

Expert Comment

ID: 1958912

Setting the timer for SendKeys may not be a good idea, as the time it takes to copy a file to the remote host is dependent on lot of factors like n/w trafic and processor speed. So I would suggest you to use the command line arguments to that copy function, if available, so that you don't have to fill in that dialog box. Have you checked the syntax for command line arguments?

As far as the synchronization part is concerned, yes, Shell will run the command in a different workspace and hence it becomes different application and runs asyncronously. You can however control it so that your VBA code can wait for the completion of the applicaiton execution by calling APIs like _spawn. I don't remember the exact syntax.

However, if the first suggestion solves your problem you don't have to do this, otherwise I can give you the sample code that will run the applicaiton synchronously.


Accepted Solution

innovate earned 200 total points
ID: 1958913

What you need is the 32bit version of the shell wait function.

The code is available on support.microsoft.com

The article is   Q96844

Here's a partial copy.

Executing the Shell() function in a Visual Basic for Windows program starts another executable program asynchronously and returns control to the Visual Basic application. This shelled program continues to run independent of your application until the user closes it.

However, if your Visual Basic application needs to wait for the shelled process to terminate, you could use the Windows API to poll the status of the application, but this is not a very efficient process. This article shows by example how to use a better process.

There is a completely different process that would be used to accomplish the same thing from a 16-bit application. For additional information on the 16-bit implementation, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID:  Q96844
   TITLE     : HOWTO: Determine When a Shelled Process Has Terminated


The Win32 API has integrated functionality that enables your application to wait until a shelled process has completed. To use these functions, you need to have a handle to the shelled process. To accomplish this, you need to use the CreateProcess() function to begin your shelled program instead of the Shell() function.

Creating the Shelled Process

In a 32-bit application, you need to create an addressable process. To do this, use the CreateProcess() function to start your shelled application. The CreateProcess() function gives your program the process handle of the shelled process via one of its passed parameters.

Waiting for the Shelled Process to Terminate

Having used CreateProcess() to get a process handle, you can pass that handle to the WaitForSingleObject() function. This causes your Visual Basic application to suspend execution until the shelled process terminates.

Below are the steps necessary to build a Visual Basic for Windows program that uses the CreateProcess() function to execute the Windows Notepad (NOTEPAD.EXE) application. This code shows by example how to use the Windows API CreateProcess() and WaitForSingleObject() functions to wait until a shelled process terminates before resuming execution.

The syntax of the CreateProcess() function is extremely complicated, so in the example code, it is encapsulated into a function called ExecCmd(). ExecCmd() takes one parameter, the command line of the application to execute.

Step-by-Step Example

1.Start a new project in Visual Basic. Form1 is created by default.

2.Add the following code to the general declarations section of Form1:

      Private Type STARTUPINFO

         cb As Long
         lpReserved As String
         lpDesktop As String
         lpTitle As String
         dwX As Long
         dwY As Long
         dwXSize As Long
         dwYSize As Long
         dwXCountChars As Long
         dwYCountChars As Long
         dwFillAttribute As Long
         dwFlags As Long
         wShowWindow As Integer
         cbReserved2 As Integer
         lpReserved2 As Long
         hStdInput As Long
         hStdOutput As Long
         hStdError As Long
      End Type

         hProcess As Long
         hThread As Long
         dwProcessID As Long
         dwThreadID As Long
      End Type

      Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
         hHandle As Long, ByVal dwMilliseconds As Long) As Long

      Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
         lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
         lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
         ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
         ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
         lpStartupInfo As STARTUPINFO, lpProcessInformation As _

      Private Declare Function CloseHandle Lib "kernel32" (ByVal _
         hObject As Long) As Long

      Private Const NORMAL_PRIORITY_CLASS = &H20&
      Private Const INFINITE = -1&

      Public Sub ExecCmd(cmdline$)
         Dim proc As PROCESS_INFORMATION
         Dim start As STARTUPINFO

         ' Initialize the STARTUPINFO structure:
         start.cb = Len(start)

         ' Start the shelled application:
         ret& = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
            NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

         ' Wait for the shelled application to finish:
         ret& = WaitForSingleObject(proc.hProcess, INFINITE)
         ret& = CloseHandle(proc.hProcess)
      End Sub

3.Add the following code the Form_Click() event procedure of Form1:

      Sub Form_Click ()
         ExecCmd "notepad.exe"
         MsgBox "Process Finished"
      End Sub

4.Press the F5 key to run the application.

5.Using the mouse, click the Form1 window. At this point the Notepad application is shelled.

NOTE: The MsgBox statement following the ExecCmd() subroutine is not executed because the WaitForSingleObject() function prevents it. The message box does not appear until Notepad is closed when the user chooses Exit from Notepad's File menu (ALT, F, X).

The above is Microsoft code and should be modified to suite.

Hope this helps

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question