At work we handle a lot of AS400 mainly to manage issues with the warehouse, I have made a macro to put all the codes in an excel with its quantity, which works. The problem comes if I enter a bad code, the macro is blocked and does not advance to the next code.
Sub Macro()
Dim archivo As String
Dim valor As String
Dim fila As Long
Dim carpeta As String
Dim cuantasFilas As Long
fila = 4
carpeta = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Mis documentos\IBM\Client Access\Emulator\private\"
archivo = carpeta & Range("B1") & (".mac")
On Error Resume Next
Kill archivo
On Error GoTo 0
Open archivo For Binary Access Write As 1
Put #1, , "[PCOMM SCRIPT HEADER]" & vbCrLf
Put #1, , "LANGUAGE=VBSCRIPT" & vbCrLf
Put #1, , "DESCRIPTION=" & vbCrLf
Put #1, , "[PCOMM SCRIPT SOURCE]" & vbCrLf
Put #1, , "OPTION EXPLICIT" & vbCrLf
Put #1, , "autECLSession.SetConnectionByName(ThisSessionName)" & vbCrLf
Put #1, , "subSub1_" & vbCrLf
Put #1, , "sub subSub1_()" & vbCrLf
Put #1, , "autECLSession.autECLOIA.WaitForAppAvailable" & vbCrLf
For fila = 4 To 30000
If Cells(fila, 1).Value = "" Then
Exit For
Else
If Cells(fila, 7).Value <> "" And Cells(fila, 7).Value <> 0 Then
cuantasFilas = cuantasFilas + 1
valor = Cells(fila, 1).Value
Put #1, , "autECLSession.autECLOIA.WaitForInputReady" & vbCrLf
Put #1, , "autECLSession.autECLPS.SendKeys " & valor & vbCrLf
Put #1, , "autECLSession.autECLOIA.WaitForInputReady" & vbCrLf
Put #1, , "autECLSession.autECLPS.SendKeys " & """[fldext]""" & vbCrLf
Put #1, , "autECLSession.autECLOIA.WaitForInputReady(1000)" & vbCrLf
'comprobar si hubo error con la posición del cursor
Put #1, , "if (autECLSession.autECLPS.CursorPoscol = 11) then" & vbCrLf
Put #1, , "autECLSession.autECLOIA.WaitForInputReady(1000)" & vbCrLf
Put #1, , "autECLSession.autECLPS.SendKeys " & """[reset]""" & vbCrLf
Put #1, , "else" & vbCrLf
valor = Cells(fila, 7).Value
Put #1, , "autECLSession.autECLOIA.WaitForInputReady" & vbCrLf
Put #1, , "autECLSession.autECLPS.SendKeys " & valor & vbCrLf
Put #1, , "autECLSession.autECLOIA.WaitForInputReady" & vbCrLf
Put #1, , "autECLSession.autECLPS.SendKeys " & """[fldext]""" & vbCrLf
Put #1, , "autECLSession.autECLOIA.WaitForInputReady" & vbCrLf
Put #1, , "autECLSession.autECLPS.SendKeys " & """[enter]""" & vbCrLf
Put #1, , "end if" & vbCrLf
End If
End If
Next
Put #1, , "autECLSession.autECLOIA.CancelWaits" & vbCrLf
Put #1, , "Msgbox(" & """Macro ejecutada. Pulse F3 y F9 para confirmar.""" & ")" & vbCrLf
Put #1, , "end sub" & vbCrLf
Close #1
If cuantasFilas = 0 Then
MsgBox ("No se ha creado ninguna fila de datos ya que ningún artículo tenía cantidades grabadas.")
Else
MsgBox ("Pedido Generado. Asegurese de tener el cursor en código artículo para que la macro funcione")
End If
End SubWhat argument should be inserted so that in case of error, reset the previous state it was in before entering the wrong code.