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.
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.