Solved

Stop execution of code while app uploads data from scanner VBA

Posted on 2011-09-06
13
388 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)
Comment Utility
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
Comment Utility
try the shellandwait function

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

Author Comment

by:caandal
Comment Utility
Hi Fyed

No it is not.

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

tx
0
 

Author Comment

by:caandal
Comment Utility
Hi Caprocorn1

It can't be that complicated surely?

0
 

Author Comment

by:caandal
Comment Utility
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)
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
And Dev's ShellAndWait is dead simple to use
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Fyed, Nick67 and Caprocorn1

Got it working fine.  Thank you all for your imput

Kind regards
Alan
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

763 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

11 Experts available now in Live!

Get 1:1 Help Now