Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

AS400 Upload Macro PCOMM Script - Variable Loop Question

Status
Not open for further replies.

boesdorf

Technical User
Nov 18, 2016
2
US
Hello,
I'm OK with VBA in Excel and play a bit with the macros that upload excel data in our ERP system. I've come across one that I'm hoping someone can offer some advice. We have a spreadsheet to upload inventory after a stock take. Our ERP system has a set of keystrokes to enter the data, but on the 14th item, you have to hit page down instead of tab.

So basically I am trying to get the keystrokes to do a set of instructions (TASK A) 13 times, then TASK B on the 14th time, etc. The length of the list could be as short as 35 or as long as 10000 (variable).
TASK A: Do for items 1-13.
TASK B: Do for item 14
TASK A: Do for items 15-27
TASK B: Do for item 28
TASK A: Do for items 29-35

Here is my full code currently, which I can only do 14 items at a time:
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)
REM This line calls the macro subroutine

Const MK = 0
Const LOC = 1
Const RESOURCE = 2
Const LOT = 3
Const QTY = 4
Const KG = 5
Const QC = 6



Dim i
Dim CorrectRef

Dim BLANKLoad(14)
BLANKLoad(1) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(2) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(3) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(4) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(5) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(6) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(7) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(8) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(9) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(10) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(11) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(12) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(13) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")
BLANKLoad(14) = Array("MK","LOC","RESOURCE","LOT1234567","1","KG","FREE")



subSub1_
sub subSub1_()

i=1
Do While i < UBound(BLANKLoad)+1
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLPS.SendKeys BLANKLoad(i) (MK)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOC)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (RESOURCE)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOT)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (QTY)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (KG)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (QC)
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.Wait 500

i = i + 1
Loop

end sub


TASK A would be this (1-13, 15-27, etc.)
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLPS.SendKeys BLANKLoad(i) (MK)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOC)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (RESOURCE)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOT)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (QTY)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (KG)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (QC)
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.Wait 500

TASK B would be this (14,28,42 etc.) - difference in bold
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLPS.SendKeys BLANKLoad(i) (MK)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOC)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (RESOURCE)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOT)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (QTY)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(i) (KG)
autECLSession.autECLPS.SendKeys BLANKLoad(i) (QC)
autECLSession.autECLPS.SendKeys "[page rollup]"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.Wait 500

Any advice as this would save hours and hours of time for me! Thanks.

 
Something like this for a start:
Code:
iMaximum = 100
iCount = 1

Do while iCount <= iMaximum
   autECLSession.autECLOIA.WaitForAppAvailable
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (MK)
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOC)
   autECLSession.autECLPS.SendKeys "[field+]"
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (RESOURCE)
   autECLSession.autECLPS.SendKeys "[field+]"
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (LOT)
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (QTY)
   autECLSession.autECLPS.SendKeys "[field+]"
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (KG)
   autECLSession.autECLPS.SendKeys BLANKLoad(i) (QC)

   If iCount MOD 14 = 0 then
      autECLSession.autECLPS.SendKeys "[page rollup]"
   end if 

   autECLSession.autECLPS.SendKeys "    "
   autECLSession.autECLPS.SendKeys "    "
   autECLSession.autECLPS.Wait 500

   [highlight #FCE94F]iCount = iCount + 1[/highlight]
Loop
 
I had to tweak it a little bit, but the reply got me in the right direction of the "if iCount MOD 14". Here is the final code that worked for me. Thanks for your help!!!

iMaximum = 100
iCount = 1

Do while iCount < UBound(BLANKLoad)+1
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (MK)
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (LOC)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (RESOURCE)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (LOT)
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (QTY)
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (KG)
autECLSession.autECLPS.SendKeys BLANKLoad(iCount) (QC)
autECLSession.autECLPS.SendKeys "[tab]"

If iCount MOD 14 = 0 then
autECLSession.autECLPS.SendKeys "[roll up]"
autECLSession.autECLOIA.WaitForAppAvailable


end if

autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.Wait 500

iCount = iCount + 1
Loop

end sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top