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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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