Attachmate reflections and excel

[abturtle]

Hi there,

I've been having some trouble with the following script. The aim is to copy an account number from the excel spreadsheet and paste it into reflections and then submit and confirm (enter, f9). After that I need to copy some of the data from reflections and paste it into the spreadsheet, proceed to the next line and loop the process until there are no more entries.

I have the restriction of only being able to come at this using VBA through reflections. after going through I have found I am getting errors on the loop and I have received a few errors saying my variables can't perform certain actions... the information is a mix of numbers and letters and, as such. I would prefer to keep it as a string.

I haven't used VBA in over 8 years so am more than a little rusty and would appreciate any help.

Cheers

ABT

' Generated by Attachmate Reflection 2014 (15.6.797.0)
' Generated by the Macro Recorder on 05/12/2017 14:32:42
'---------------------------------------------------------------------
' Common variable declarations
Dim ibmCurrentTerminal As IbmTerminal
Dim ibmCurrentScreen As IbmScreen
Dim hiddenTextEntry As String
Dim excelApp As Object
Dim inputaccountNumber As String 'To store a value for the input account number
Dim returnValue As Integer
Dim timeout As Integer
Dim I As Integer
Dim MSForms_DataObject As Object
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
timeout = 15000
Set excelApp = GetObject(, "Excel.Application")
Set excelApp = GetObject("\\Server1\ProjectA.xlsx")


Set ibmCurrentTerminal = ThisFrame.SelectedView.Control
Set ibmCurrentScreen = ibmCurrentTerminal.screen
'---------------------------------------------------------------------
'1. CPAS Already on correct Command


'Open relevant Spreadsheets
'Set Counter variable
I = 2
inputaccountNumber = excelApp.ActiveSheet.Range("A2").GetText
clipboard = excelApp.ActiveSheet.Range("A2").GetText
'Create loop to copy selected cell contents for account Lis: LOOP START (Do while loop?)
Do While inputaccountNumber > 0
'b. Paste account number into cpas search box
With Session
clipboard.Copy y.cells(Rows.Count, "B" & I).End(xlUp).offset(1, 0)
'c. Use "Enter" or "Return" Command
ibmCurrentScreen.SendControlKey (ControlKeyCode_Transmit)
returnValue = ibmCurrentScreen.WaitForKeyboardEnabled(timeout, 0)
'Wait for cursor to be in position before continuing
returnValue = ibmCurrentScreen.WaitForCursor1(timeout, 1, 1)
If (returnValue <> ReturnCode_Success) Then
Err.Raise 5001, "WaitForCursor1", "Timeout waiting for cursor position.", "VBAHelp.chm", "5001"
End If
'Use F9 to proceed
ibmCurrentScreen.SendControlKey (ControlKeyCode_F9)
'e.Copy "paidToDate"
clipboard = .GetText(4, 68, 4, 76)
'ii. Paste "paidToDate" into C 'Tab across
clipboard.Copy y.cells(Rows.Count, "C" & I).End(xlUp).offset(1, 0)
'f.Copy "billToDate"
clipboard = .GetText(5, 68, 5, 76)
'iii. Paste "billToDate" into D 'Tab across
clipboard.Copy y.cells(Rows.Count, "D" & I).End(xlUp).offset(1, 0)
'g.Copy "nextReviewDate "
clipboard = .GetText(7, 52, 7, 60)
'iv. Paste "nextReviewDate " into E 'Tab across
clipboard.Copy y.cells(Rows.Count, "E" & I).End(xlUp).offset(1, 0)
'h.Copy "reviewBasis "
clipboard = .GetText(6, 44, 6, 46)
'v. Paste "reviewBasis " into F'Tab across
clipboard.Copy y.cells(Rows.Count, "F" & I).End(xlUp).offset(1, 0)
'i.Copy "benefit"
clipboard = .GetText(5, 44, 5, 49)
'vi. Paste "benefit" into G
clipboard.Copy y.cells(Rows.Count, "G" & I).End(xlUp).offset(1, 0)
Application.CutCopyMode = False

'k. Use "F12" on cpas to return to home screen
ibmCurrentScreen.SendControlKey (ControlKeyCode_F9)
'add to count integer
I = I + 1
'Copy next account number
inputaccountNumber = excelApp.ActiveSheet.Range("A" & I).Copy
clipboard = excelApp.ActiveSheet.Range("A2").GetText
'3. Next line on account number list spreadsheet (LOOP END)

Loop
End With

End Sub

 

[Beyond_avarice]

I am currently using Attachmate/Reflection/ISeries and have been able to connect a spreadsheet with AS400 and communicate data back and forth.

I suspect you may have different libraries than I do and can only make certain suggestions of problems I ran into.

Reflection doesn't appear to recognize "End(xlUp)" or any of the modern named arguments. It however does understand "End(-4162)" and similar numerically represented arguments.

Reflection has a similar "GetFieldText" method. I use this to populate my variable that I then iterate through my spreadsheets and assign a cell value to. ie I avoid copy and paste.

I wrap my loops within my "With Session/End With". This may not be a problem but you may have a sort of "one foot in, one foot out" issue.

I am attaching an example of a code; that I put together to help a coworker run through an Excel list of Order #'s and return a PO# and Bill of Lading # to the Excel Spreadsheet from the workscreen.

Hope this points you in the right direction.

Sub BOLPOLookup()
'This is to help find the PO# and BOL# with the worksheet of Order#'s
'Last updated 11/16/17




Dim Order As String, ordNum As String, PO As String, BOL As String
Dim lRow As Long




With Session
            .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
'            .Application.SelectWord 1, 2
'            .CopySelection
'            Order = .GetClipboardText
            .FindText " ", 1, 2
            Order = .GetFieldText(1, 2, .FoundTextColumn - 2)


       
    
        If Order <> "Order" Then
            MsgBox "You will need to enter the 'Order History Page' page before starting this process"
            Exit Sub
        End If
        
        fPath = Application.GetOpenFilename()
    
        If fPath <> vbNullString Then
                Set MyXL = GetObject(fPath)
                On Error Resume Next
                MyXL.Application.displayalerts = False      'turn off warning flag
                MyXL.Application.workbooks.open (fPath)
            Else: Exit Sub
        End If
        
     
    
    Set MyXL = GetObject(fPath)
    
    'Place BO# and BOL# headers
    MyXL.Application.Range("Q1").Value = "PO#"
    MyXL.Application.Range("R1").Value = "BOL #"
    
    lRow = MyXL.Application.Range("E" & MyXL.Application.Rows.Count).End(-4162).row        'find last row
    
    
    For I = 2 To lRow
        
        ordNum = MyXL.Application.Range("E" & I).Value
        If ordNum <> "       " Then
         
    
            .WaitForEvent rcEnterPos, "30", "0", 1, 10
            .WaitForDisplayString "#", "30", 1, 8
            .TransmitANSI ordNum
            .TransmitTerminalKey rcIBMEnterKey
            .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
            .WaitForEvent rcEnterPos, "30", "0", 1, 1
 


                .FindText " ", 16, 31
                PO = .GetFieldText(16, 31, .FoundTextColumn - 31)
                               
            If PO = "" Then
                 PO = "PO NOT ON FILE"
            End If




                .FindText " ", 1, 33
                BOL = .GetFieldText(1, 33, .FoundTextColumn - 33)
                
            If BOL = "" Then
                BOL = "NO BOL ON FILE"
            End If
            
            'Populate spreadsheet with PO and BOL#'s
            MyXL.Application.Range("Q" & I).Value = PO
            MyXL.Application.Range("R" & I).Value = BOL
    
            .TransmitTerminalKey rcIBMF10Key
                Else: MyXL.Application.Range("Q" & I).Value = " ""U""-Invoice"
        End If
    
    
    
    Next I


End With
End Sub