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
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"
'**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
sStockCode = sInputLine
If sLocCode <> "" Then
sSQL = "insert into BatchHolding (Bin, Stock,IssRet,DR) values ('" & sLocCode & "','" & sStockCode & "','" & sIssRet & "','" & sDR & " ')"
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;"