tom616wilson
asked on
Automatic Data Entry to AS400 Screen from Excel File
I am working on a script to automate data entry to AS400 screen from Excel data. My script is below. I have found a simple script to enter "selected" cell data, but am trying to enter multiple rows of records. I need assistance in setting the range to a block of cells and then reading that data.
I have placed astericks around the area requiring modification.
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectio
GetExcelData
sub GetExcelData()
Dim oXL, oRange, oCell
Dim curVal, i
Set oXL = GetObject(,"Excel.Applicat
If oXL Is Nothing Then
MsgBox "You need to have the Excel list open!"
Exit Sub
Else
REM **************************
Set oRange = oXL.Application.Selection
REM **************************
End If
With autECLSession
For each oCell in oRange
curVal = oCell.Value
.autECLOIA.WaitForAppAvail
.autECLOIA.WaitForInputRea
.autECLPS.SendKeys "[pf16]"
.autECLPS.SendKeys curVal, 5, 39
.autECLPS.SendKeys "[enter]"
.autECLOIA.WaitForInputRea
.autECLPS.SendKeys "1"
.autECLPS.SendKeys "[enter]"
.autECLPS.SendKeys "[pf10]"
Next
End With
End Sub
I have placed astericks around the area requiring modification.
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectio
GetExcelData
sub GetExcelData()
Dim oXL, oRange, oCell
Dim curVal, i
Set oXL = GetObject(,"Excel.Applicat
If oXL Is Nothing Then
MsgBox "You need to have the Excel list open!"
Exit Sub
Else
REM **************************
Set oRange = oXL.Application.Selection
REM **************************
End If
With autECLSession
For each oCell in oRange
curVal = oCell.Value
.autECLOIA.WaitForAppAvail
.autECLOIA.WaitForInputRea
.autECLPS.SendKeys "[pf16]"
.autECLPS.SendKeys curVal, 5, 39
.autECLPS.SendKeys "[enter]"
.autECLOIA.WaitForInputRea
.autECLPS.SendKeys "1"
.autECLPS.SendKeys "[enter]"
.autECLPS.SendKeys "[pf10]"
Next
End With
End Sub
ASKER
Matt,
That is what I'll do.
If I select a block of cells, say C(1,1) through C(5,5); in what order does the "For each oCell in oRange" statement step through the cells?
Thanks,
Tom
That is what I'll do.
If I select a block of cells, say C(1,1) through C(5,5); in what order does the "For each oCell in oRange" statement step through the cells?
Thanks,
Tom
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Why not just select the multiple cells you want to enter prior to running? If you want it fully automated, how do you determine which cells you want to have entered?
Also, from my experience it is usually worthwhile to put a waitforinputready after pressing enter or any F-key; the as400 machine I use can get laggy and my macro will skip a step.
Matt