Batch File Wait for Excel to Close and Then Continue
Hi
The simplest solution is to trap a resulting file. This file should best be created at the other programs exit but will probably work if the .bat file creates it at end. Other than this there are ways to trap the shell initiated program, even use CreateProcess() instead of shell(), but most include usage of Api, and as you said they trap the shell initiated program...
Does the file method applies to you?
Why Excel gives us so much but so little?
Was this reply helpful?
Hi
The simplest solution is to trap a resulting file. This file should best be created at the other programs exit but will probably work if the .bat file creates it at end. Other than this there are ways to trap the shell initiated program, even use CreateProcess() instead of shell(), but most include usage of Api, and as you said they trap the shell initiated program...
Does the file method applies to you?
I'm not sure I understand what you mean by TRAP. I'm pretty sure the SHELL AND WAIT routine I downloaded involved api but I"m not super sure of what that part means.
(The more I learn about VBA, the less I realize I know...arg)
Was this reply helpful?
ok, i explain
If you can access the file you want to monitor, then you make it create a file just before end, so you can check with VBA if this file exists, then erase it and proceed, otherwise just loop.
If you cannot access the file it'self then perhaps the batch file (*.bat) can prepare it for you (a simple line will do , like: echo "DONE" >myfile.txt
Another method is to rename it! yes, it works but don't know how renaming a file messes with how the system and antivirus programs will look at it. However renaming a temp file that you will kill at the end is ... free.
Use "Name "myFile.ext" as "myFile.ext" "
effectively renaming to self. Use Paths, curdir, chdir, kill etc to make sure you work on proper dir, then erase.
I include a small demo of a function that will test for Open Process AND Open file, using TASKLIST for the 1st (so you don't care where is the file's path) and NAME for the 2nd. Some API is included for Micro Timing only! NOTICEthat error handler adds delay for files to get ready, it will not correct anything...
Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Public DMinFl As Boolean
' Cmd based version. Not the fastest nor the most elegant...
'
' Usage as: if CheckRunningProgs("excel") then ? "Excel is running" else ? "NATHA"
'
Function CheckRunningProgs(MatchString As String) As Boolean
Dim myPath As String, myFile As String, dum As String, FileNo As Long, myBat As String
Dim StartTime As Double, microSecs As Long, oldPath As String
On Error GoTo ErrorHandler
StartTime = MicroTimer()
microSecs = 50
FileNo = FreeFile
myPath = ThisWorkbook.Path
myBat = "listCreator.bat"
myFile = "MyList.txt"
MatchString = LCase(MatchString)
oldPath = CurDir
ChDir myPath
' Check existence and create .Bat file
Open myBat For Output As FileNo
If LOF(FileNo) < 1 Then
'' Valid TaskList Filters. ex: tasklist /fi "IMAGENAME eq excel*" /fo "CSV" /nh
Print #FileNo, "tasklist /fi " & Chr(34) & "IMAGENAME eq Excel*" & Chr(34) & " /fo " & Chr(34) & "CSV" & Chr(34) & " /nh > Mylist.txt"
'' or complete list
'Print #FileNo, "tasklist > MyList.txt"
End If
Close FileNo
' This Execute TaskList function from a batch file
' sending results to txt file instead of screen
Debug.Print Shell(myBat, vbMinimizedNoFocus),
' This will be wrong (and call the On-Error loop) as long as the list
' is still open by the TaskList process...
Name "MyList.txt" As "MyList.txt"
FileNo = FreeFile
Open myFile For Input As FileNo
'' If filter is used AND /NH (no header) and /FO "CSV" then we are able to trak LOF only
'' Take care since filter cannot be *excel* so writting xcel* will not find anything
If LOF(FileNo) > Len(MatchString) Then CheckRunningProgs = True
'' Else we search all file line by line
'Do While Not EOF(FileNo)
' Line Input #FileNo, dum
' If InStr(LCase(dum), MatchString) > 0 Then
' CheckRunningProgs = True
' Exit Do
' End If
'Loop
Close #FileNo
Kill myBat
Kill myFile
ChDir oldPath
Debug.Print "Did: "; Round(MicroTimer() - StartTime, 3); " seconds ";
Exit Function
ErrorHandler:
DelayMicro microSecs
Resume
End Function
Public Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
If cyFrequency = 0 Then getFrequency cyFrequency ' Get frequency.
getTickCount cyTicks1 ' Get ticks.
'Result in Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency Else MicroTimer = 0
End Function
' Ticks are translated to MiliSeconds
'
Public Sub DelayMicro(Ticks As Long)
Dim StartTick As Double, lclMilis As Double
If Not DMinFl Then
DMinFl = True
lclMilis = Ticks / 1000
StartTick = MicroTimer
Do: DoEvents: Loop Until StartTick + lclMilis <= MicroTimer
DMinFl = False
End If
End Sub
Why Excel gives us so much but so little?
Was this reply helpful?
Source: https://answers.microsoft.com/en-us/msoffice/forum/all/shell-and-wait-with-bat-file-executing-separate/283c543a-3a4a-41c4-9b02-11d955b8fb5a
0 Response to "Batch File Wait for Excel to Close and Then Continue"
Post a Comment