VBA Sendkeys Question

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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.


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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.