Solved

Stop execution of code while app uploads data from scanner VBA

Posted on 2011-09-06
13
390 Views
Last Modified: 2012-05-12
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

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 Sub

Open in new window

0
Comment
Question by:caandal
  • 4
  • 4
  • 4
  • +1
13 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36489479
Is the Data_Read.exe file something that you wrote?

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.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 36489507
try the shellandwait function

http://www.cpearson.com/excel/ShellAndWait.aspx
0
 

Author Comment

by:caandal
ID: 36489639
Hi Fyed

No it is not.

It is an an app that comes with the batch scanners.

tx
0
 

Author Comment

by:caandal
ID: 36489664
Hi Caprocorn1

It can't be that complicated surely?

0
 

Author Comment

by:caandal
ID: 36489969
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36490084
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=impersonate}!\\" _
        & 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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 36490094
I modified what I found at:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23058220.html?#20582080

To come up with that function.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36490937
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.FileSystemObject")

    Do Until fs.FileExists("c:\somepath\somefilename.xxx") '<--- sub in your file name
        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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36490950
And Dev's ShellAndWait is dead simple to use
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36490983
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.

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 300 total points
ID: 36490985
In your case, after you pasted Dev's code to a module, you just have
ShellWait("C:\CipherLab\Forge\Batch\8 Series\Utilities\Data_Read.exe") at line 15
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36491112
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(strWindowTitle As String) As Long

    '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
0
 

Author Closing Comment

by:caandal
ID: 36491470
Hi Fyed, Nick67 and Caprocorn1

Got it working fine.  Thank you all for your imput

Kind regards
Alan
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

948 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now