Solved

Stop execution of code while app uploads data from scanner VBA

Posted on 2011-09-06
13
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +1
13 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 48

Expert Comment

by:Dale Fye
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
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye 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 48

Expert Comment

by:Dale Fye
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

622 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