How to Force Macro Code to Wait for Outside Procedure

   Sub Appacttest()

      ' Checks to see if Flag.txt already exists.
      FindIt = Dir("C:\Flag.txt")

      ' If the file Flag.txt has been found then delete it.
      If  Not Len(FindIt) = 0  Then
         Kill "C:\Flag.txt"
      End If

      ' Sets Myapp variable equal to the Shell statement.
      Myapp = Shell("C:\Custom.exe", 1)

      ' Executes the shell statement.
      AppActivate Myapp

      ' Checks to see if Flag.txt can be found yet.
      FindIt = Dir("C:\Flag.txt")

   ' The following While Wend loop will keep Microsoft Excel "suspended"
   ' until the custom application is complete. This will occur while the
   ' length of the FindIt variable is equal to 0. Microsoft Excel will
   ' remain busy until it finds the file Flag.txt, thus making the length
   ' of FindIt > 0 and ending the loop.

      ' Check to see if the length of FindIt variable is equal to 0
      ' chars.
      While Len(FindIt) = 0

      ' Continue to check if flag was created yet.
      FindIt = Dir("C:\Flag.txt")

      Wend

      ' Continue with more code if needed.

   End Sub
				

   Sub WaitForOutput()

   If Len(Dir("c:\output.txt")) > 0 Then Kill "c:\output.txt"
   If Len(Dir("c:\temp.txt")) > 0 Then Kill "c:\temp.txt"
   ' Test for previous files and delete them.

   Shell "command.com /c dir c:\windows\*.* > c:\temp.txt"
   ' Run MS-DOS DIR command to pipe the directory of
   ' c:\windows into an intermediate text file, temp.txt.

   On Error Resume Next
   ' Set error condition to skip to the next line,
   ' for Name statement below.

   Do Until Len(Dir("c:\output.txt")) > 0
   ' Begin a loop to test for final output file, output.txt.

      Name "c:\temp.txt" As "c:\output.txt"
      ' Attempt to rename temp.txt to output.txt;
      ' will fail until temp.txt is closed

      DoEvents
      ' Allow for other processes, including the shelled
 ' procedure above, to continue in the background Loop
   Loop
   'End the loop

   Workbooks.Open ("c:\output.txt")
   ' Open the resulting text file, output.txt, into an Excel worksheet.

   End Sub