PDA

View Full Version : Read data (one column) from excel and pass to AS400



bachsi
09-01-2014, 01:42 AM
Hi all,

I am starting to learning VB, EXCEL and AS400 for my job. Now I have so many works which need to connect and get and read data from excel and pass to aS400 like. I need to do the table setup in excel file and now I need to use TABCPY to copy from one physical to another physical file but I dont think the best way to manually copy one by one item. I also have column item in excel now I would like to read this column and pass to command TABCPY. I need help. Can I share a macro written by VB and I will run it in AS400 for TABCPY command by open excel file and read data in one column and pass to ITEM field in TABCPY, I have more than 5000 item and I think that it will help me.

I also take example about recording one macro in AS400 about TABCPY command as below:



[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)

REM This line calls the macro subroutine
subSub1_

sub subSub1_()
autECLSession.autECLOIA.WaitForAppAvailable

autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "tabcpy"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[pf4]"

autECLSession.autECLPS.WaitForAttrib 5,36,"10","3c",3,10000

autECLSession.autECLPS.WaitForCursor 5,37,10000

autECLSession.autECLOIA.WaitForAppAvailable

autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "bcfgdta"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "1T3681bactdta"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "EMB"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[delete]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[right]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "replace"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[enter]"
end sub

Herer is the command

Transfer Table Items (TABCPY)

Type choices, press Enter.

Source Library . . . . . . . . . > BCFGDTA Character value
Source Company . . . . . . . . . > 1 A-9, *
Source Table . . . . . . . . . . > T3681 Character value
Target Library . . . . . . . . . > BACTDTA Character value
Target Company . . . . . . . . . * A-9, *
Target Table . . . . . . . . . . *STAB Character value
Item, Generic*, *ALL . . . . . . > EMB Character value Will get from excel
Generic Copy . . . . . . . . . . *NO *YES, *NO
MERGE into or REPLACE data . . . > *REPLACE *MERGE, *REPLACE
Generate Report . . . . . . . . *NO *YES, *NO
Report Only . . . . . . . . . . *NO *YES, *NO