Open Excel spreadsheet using i AccessClient macro

[Dominik Wiktor]

Hi Everybody,

my name is Dominik, I am new here and I need help
In the good old iSeries I had a macro which was switching to an Excel spreadsheet for me in the last step. I was using this macro to speed up a copy-paste data process from Excel to iSeries. Macros in iSeries were using a syntax similar to VBA so I was able to tailor them to my needs.
Now, in a java Access Client macros are in XML and I have no idea how to insert a command: "switch to excel spreadsheet" there.
Below I am pasting an "empty" macro in i Access so you know what I am talking about.
Can anybody help me write this command?
Thanks!

<HAScript name="nowe" description="" timeout="60000" pausetime="300" promptall="true" blockinput="true" author="Me" creationdate="2019-11-18 10:44:48" supressclearevents="false" usevars="false" ignorepauseforenhancedtn="true" delayifnotenhancedtn="0" ignorepausetimeforenhancedtn="true" continueontimeout="false">

<screen name="Screen1" entryscreen="true" exitscreen="true" transient="false">
<description >
<oia status="NOTINHIBITED" optional="false" invertmatch="false" />
</description>
<actions>
</actions>
<nextscreens timeout="0" >
</nextscreens>
</screen>

</HAScript>

[gcraill]

Hola!

See here https://www.ibm.com/support/knowledgecenter/SSEQ5Y_5.9.0/com.ibm.pcomm.doc/books/html/emulator_programming08.htm

and here https://www.ibm.com/support/pages/ehllapi-access-client-solutions-emulator

if you download and install the EHLLAPI bridge then you can use the VBA macros still. They may need to have some conversions done but if you are familiar with the vba that should not be insurmountable.

It also requires java version 8 or later I believe.

GC

[mballard]

I have a similar issue. My company IT department won't convert an old macro script to the new Client Solutions format.
Here is the old script, which prompts to open a saved Excel file to paste two columns of data into an emulator screen:
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)
Dim ObjExcelAppl, ObjWorkbook, ObjWorksheet, StrFileName
Dim cCol1, cCol2
Dim c1, c2

' ...
' Call the Main subroutine.
Main
' Clean up after executing your script.
Set ObjWorksheet = Nothing
Set ObjWorkbook = Nothing
Set ObjExcelAppl = Nothing

Sub Main()

' Open and create Excel spreadsheet.
Set ObjExcelAppl = CreateObject("Excel.Application")
StrFileName = ObjExcelAppl.GetOpenFilename _
("Microsoft Excel bestanden (*.xls*),*.xls*")
ObjExcelAppl.DisplayAlerts = False

' Valid name?
If (StrFileName <> False) then

' Open spreadsheet.
Set ObjWorkbook = ObjExcelAppl.WorkBooks
ObjWorkbook.Open StrFileName

' Check if spreadsheet is already open.
On Error Resume Next
ObjExcelAppl.ActiveWorkbook.Save
If Err.Number = 1004 Then
Msgbox "Excelfile is already open!"
Exit Sub
End If
On Error GoTo 0

' Load first sheet in workbook.
ObjExcelAppl.Worksheets(1).Activate
Set ObjWorksheet = ObjExcelAppl.Worksheets(1)

' ...
Process_Sheet
' ...

' Save and close workbook.
ObjExcelAppl.ActiveWorkbook.Save
ObjExcelAppl.DisplayAlerts = True
ObjExcelAppl.ActiveWorkbook.Close(True)

End If

End Sub

Function Process_Sheet()

' Correct sheet?
Dim c
If Is_Correct_Sheet() Then

' Process every row (except the header, c=1).
c = 2

' Stop upon the first empty row we find.
Do While ObjWorksheet.Cells(c, 2).Value <> Empty
Process_Row(c)
c = c + 1
Loop

Else
Msgbox "Wrong Excel sheet!"
End If

End Function

' Check if the given sheet is correct.
Function Is_Correct_Sheet()

Dim c ' column number
Is_Correct_Sheet = False
c = 1

Do While ObjWorksheet.Cells(1, c).Value <> ""

Select case ObjWorksheet.Cells(1, c).Value
case "COL1" cCol1 = c
case "COL2" cCol2 = c
End Select

c = c + 1

Loop

' Found header?
If ( cCol1 <> 0 And cCol2 <> 0 ) Then
Is_Correct_Sheet = true
End If

End Function

Function Process_Row(ByVal row)

c1 = CStr(ObjWorksheet.Cells(row, cCol1).Value)
c2 = CStr(ObjWorksheet.Cells(row, cCol2).Value)

' ... Insert your Recorded vbs Macro here

' Place cursor.
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLPS.SetCursorPos 11,033

' Type c1 in current field (where cursor is positioned).
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys c1
autECLSession.autECLPS.SendKeys "[field+]"

' Type c2 in the next field.
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys c2

' Send the data.
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLOIA.WaitForAppAvailable

' ...

End Function

' ...