ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Open Excel spreadsheet using i AccessClient macro

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Open Excel spreadsheet using i AccessClient macro

    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>

  • #2
    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
    Greg Craill: "Life's hard - Get a helmet !!"

    Comment


    • #3
      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

      ' ...


      Comment

      Working...
      X