Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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

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
caandal
Asked:
caandal
  • 4
  • 4
  • 4
  • +1
3 Solutions
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
try the shellandwait function

http://www.cpearson.com/excel/ShellAndWait.aspx
0
 
caandalAuthor Commented:
Hi Fyed

No it is not.

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

tx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
caandalAuthor Commented:
Hi Caprocorn1

It can't be that complicated surely?

0
 
caandalAuthor Commented:
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
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
 
Nick67Commented:
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
 
Nick67Commented:
And Dev's ShellAndWait is dead simple to use
0
 
Dale FyeCommented:
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
 
Nick67Commented:
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
 
Nick67Commented:
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
 
caandalAuthor Commented:
Hi Fyed, Nick67 and Caprocorn1

Got it working fine.  Thank you all for your imput

Kind regards
Alan
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now