caandal
asked on
Stop execution of code while app uploads data from scanner VBA
Hi Gurus
I have and application that runs very nicely that I shell to from VBA that uploads a batch file from a scanner and then reads the batch file line by line and then puts it into a table in the correct format. The problem is that the code starts to try and read the batch file line by line while the application is still trying to write to the batch file. I have put comments at the section of code that is affected. I have tried a number of suggestions from this forum and others but am not winning.
Your help will be very much appreciated
Regards
Alan
I have and application that runs very nicely that I shell to from VBA that uploads a batch file from a scanner and then reads the batch file line by line and then puts it into a table in the correct format. The problem is that the code starts to try and read the batch file line by line while the application is still trying to write to the batch file. I have put comments at the section of code that is affected. I have tried a number of suggestions from this forum and others but am not winning.
Your help will be very much appreciated
Regards
Alan
Sub LoadSourceFile(sFilename As String)
Dim iFile As Integer
Dim sInputLine As String
Dim sLocCode As String
Dim sStockCode As String
Dim sIssRet As String
Dim sDR As String
Dim sSQL As String
iFile = FreeFile
Open sFilename For Input As iFile
sSQL = "Delete * from BatchHolding"
CurrentDb.Execute sSQL
'**Shell to external app to bring write data to batch file
RetVal = Shell("C:\CipherLab\Forge\Batch\8 Series\Utilities\Data_Read.exe", 1)
'**We need to wait for this to finish before proceeding with the code execution at this point
Do While Not EOF(iFile)
Line Input #iFile, sInputLine
If Left$(sInputLine, 6) = "ISSRET" Then
sIssRet = sInputLine
ElseIf Left$(sInputLine, 2) = "DR" Then
sDR = sInputLine
ElseIf Left$(sInputLine, 3) = "LOC" Then
sLocCode = sInputLine
Else
sStockCode = sInputLine
If sLocCode <> "" Then
sSQL = "insert into BatchHolding (Bin, Stock,IssRet,DR) values ('" & sLocCode & "','" & sStockCode & "','" & sIssRet & "','" & sDR & " ')"
')"
CurrentDb.Execute sSQL
End If
End If
Loop
Close
SecurityID = GetSetting(appname:="Warehouse", Section:="Validate", Key:="Reg")
sSQL = "INSERT INTO dbo_tIssRet ( LocationID, ProductID, Qty, IssRetDate, IssRet,StaffID ) SELECT Val(Mid([Bin],InStr([Bin],'-')+1,4)) AS LocationID, Val(Mid([stock],InStrRev([Stock],'-')+1)) AS ProductID, Val(Mid([Stock],InStrRev([stock],',')+1)) AS Qty, Now() AS Expr1,Val(Mid([ISSRET],InStr([ISSRET],'-')+1,1)) AS Expr2," & SecurityID & " FROM BatchHolding;"
CurrentDb.Execute sSQL
End SubSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Fyed
No it is not.
It is an an app that comes with the batch scanners.
tx
No it is not.
It is an an app that comes with the batch scanners.
tx
ASKER
Hi Caprocorn1
It can't be that complicated surely?
It can't be that complicated surely?
ASKER
Fyed
I think that is a good idea but instead of a dummy file I could check to see if the application is running. Once it stops - I could loop out of the statement ( which is basically what the shellwait statement does, that capricorn1 suggested) Is there a statement to check if n application is running or not because it would be easy do do this.
regards
I think that is a good idea but instead of a dummy file I could check to see if the application is running. Once it stops - I could loop out of the statement ( which is basically what the shellwait statement does, that capricorn1 suggested) Is there a statement to check if n application is running or not because it would be easy do do this.
regards
Well, I found this:
Public Function IsRunning(ByVal strProcName As String) As Boolean
Dim objWMIService, objProcess, colProcess
Dim strComputer
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=imper
& strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = '" & strProcName & "'")
IsRunning = (colProcess.Count > 0)
End Function
Which seems to work well at identifying processes that are running.
Public Function IsRunning(ByVal strProcName As String) As Boolean
Dim objWMIService, objProcess, colProcess
Dim strComputer
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=imper
& strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = '" & strProcName & "'")
IsRunning = (colProcess.Count > 0)
End Function
Which seems to work well at identifying processes that are running.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I do something similar.
Since you are waiting for a file to exist, why don't you test for that in a Do While, DoEvents loop?
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
Do Until fs.FileExists("c:\somepath\somefilename.x
DoEvents 'do nothing
Loop
The other one I use is to test if a certain window still exists
It uses Dev Ashish's API code from http://access.mvps.org
But I've modified it fairly heavily to my own ends--and consolidated all the declarations so I can't post it and have it make sense
ShellAndWait is from there too
http://access.mvps.org/access/api/api0004.htm
Since you are waiting for a file to exist, why don't you test for that in a Do While, DoEvents loop?
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
Do Until fs.FileExists("c:\somepath\somefilename.x
DoEvents 'do nothing
Loop
The other one I use is to test if a certain window still exists
It uses Dev Ashish's API code from http://access.mvps.org
But I've modified it fairly heavily to my own ends--and consolidated all the declarations so I can't post it and have it make sense
ShellAndWait is from there too
http://access.mvps.org/access/api/api0004.htm
And Dev's ShellAndWait is dead simple to use
Nick,
The file will show up using Dir() and other methods, as soon as it is opened to be written to, even if it hasn't been closed.
The file will show up using Dir() and other methods, as soon as it is opened to be written to, even if it hasn't been closed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My usage is waiting for a cmd batchfile to execute PhotoResize from
http://www.rw-designer.com/picture-resize
With that particular app, no file exists for FileSystemObject to see until it can already be used, so I have never encountered a problem.
But yes, if a working file by the end name is the only thing created by the OP's batch, there could be grief
The VBA where I am testing for file existence actually writes the .cmd batch file line by line, and then uses Shell to execute it.
The other one I use ***beware all dependencies may not be complete*** is
Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Public Function FnFindWindowLike(strWindow
'We'll pass a custom structure in as the parameter to store our result...
Dim Parameters As FindWindowParameters
Parameters.strTitle = strWindowTitle ' Input parameter
Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
FnFindWindowLike = Parameters.hwnd
End Function
I can pass a partial Window caption to it and have VBA code pause until it returns 0.
Dim Success as boolean
Dim WIACommonDialoghWnd as Double
Dim devName as string
devName = "*SomeWindowCaption*"
success = False
Do Until success = True
WIACommonDialoghWnd = FnFindWindowLike(devName)
'MsgBox WIACommonDialoghWnd
success = (WIACommonDialoghWnd = 0)
If success = False Then
DoEvents 'do nothing
End If
Loop
http://www.rw-designer.com/picture-resize
With that particular app, no file exists for FileSystemObject to see until it can already be used, so I have never encountered a problem.
But yes, if a working file by the end name is the only thing created by the OP's batch, there could be grief
The VBA where I am testing for file existence actually writes the .cmd batch file line by line, and then uses Shell to execute it.
The other one I use ***beware all dependencies may not be complete*** is
Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Public Function FnFindWindowLike(strWindow
'We'll pass a custom structure in as the parameter to store our result...
Dim Parameters As FindWindowParameters
Parameters.strTitle = strWindowTitle ' Input parameter
Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
FnFindWindowLike = Parameters.hwnd
End Function
I can pass a partial Window caption to it and have VBA code pause until it returns 0.
Dim Success as boolean
Dim WIACommonDialoghWnd as Double
Dim devName as string
devName = "*SomeWindowCaption*"
success = False
Do Until success = True
WIACommonDialoghWnd = FnFindWindowLike(devName)
'MsgBox WIACommonDialoghWnd
success = (WIACommonDialoghWnd = 0)
If success = False Then
DoEvents 'do nothing
End If
Loop
ASKER
Hi Fyed, Nick67 and Caprocorn1
Got it working fine. Thank you all for your imput
Kind regards
Alan
Got it working fine. Thank you all for your imput
Kind regards
Alan
I did something similar a while back. In that effort, I used the external application to create two files, the datafile to be read, and another dummy file, to indicate that the Write operation was complete.
Then I inserted a loop in my code (immediately following your Shell command) which checked to see whether the second (dummy file) exists. When It did, then I dropped out of the loop, deleted the 2nd file, and executed the rest of the code.