Reflections WRQ VBA

[Robertson1995]

I have the following code that I use in WRQ Reflections. It is very basic and runs the command bts_status mc800bts877. The characters "877" which are in red below is a site # which changes. What I need is for the macro to run the command bts_status mc800bts and then have an inputbox ask for the next 3 characters (in this example 877) that then carriage return after ok is selected on the input box. Thanks.


Sub Macro1()
' Generated by the Reflection Macro Recorder on 04-22-2009 09:01:25.57.
' Generated by Reflection for UNIX and Digital 8.0.6.
On Error GoTo ErrorHandler
Const NEVER_TIME_OUT = 0
Dim CR As String ' Chr$(rcCR) = Chr$(13) = Control-M
Dim ESC As String ' Chr$(rcESC) = Chr$(27) = Control-[
CR = Chr$(rcCR)
ESC = Chr$(rcESC)
With Session
.Transmit "bts_status mc800bts877" & CR
' .WaitForString ESC & "[K", NEVER_TIME_OUT, rcAllowKeystrokes
Exit Sub
ErrorHandler:
.MsgBox Err.Description, vbExclamation + vbOKOnly
End With
' Recording stopped at 09:04:44.83.
End Sub

 

[xcruc1at3r]

am using the WRQ Reflection for IBM 3270 i need to Run a Macro In Reflection to copy data from Excel and paste it into Reflection
i dont know how to do that!!... i have created a example of vba for you to understand a create a vba macro from it
i have data in excel Column A and B
1st I need to Copy data from A1 and paste it into Reflection Position .WaitForEvent rcEnterPos, "30", "0", 3, 11 & after that it will move to next screen and have to copy data from B1 and paste it in position .WaitForEvent rcEnterPos, "30", "0", 4, 11
.WaitForDisplayString "SEALS..:", "30", 4, 2 and enter and this process should continue till the last cell with data
If required i can upload a Excel File with Screen Shots of Reflection Screens and positions

Sub macro3() 
 
With Session 
 
Dim excelwb As Object 
Dim z As Integer 
Set excelwb = GetObject(, "Excel.Application") 
Set excelwb = GetObject("C:\Documents and Settings\mintezar\Desktop\Book1.xls") 
z = excelwb.Worksheets("Load").Range("A 1").Value 
    Selection.Copy 
        .WaitForEvent rcEnterPos, "30", "0", 3, 11 
        .WaitForDisplayString "NBR:", "30", 3, 6 
        .Paste 
        .TransmitTerminalKey rcIBMEnterKey 
    End With 
End Sub

 

[schielrn]

I've never tried to use the paste feature in reflections if it is even available I am not sure? But here is some sample code where you can see how I get excel values populated to the screen and using a loop to go from row 2 to the last row of column A:

Sub AcctAdds()
Dim oXLApp As Excel.Application     'Declare the object variable
Dim oXLBook As Excel.Workbook       'Declare the workbook variable
Dim oXLSheet As Excel.Worksheet     'Declare the sheet variable
Dim oXLCell As Excel.Range          'Declare the range variable
If Session.GetDisplayText(1, 25, 23) <> "G/L ACCOUNT MAINTENANCE" Then
    MsgBox "You are not on the G/L Account Maintenance screen."
    Exit Sub
End If
Set oXLApp = New Excel.Application  'Create a new instance of Excel
strBook = oXLApp.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", Title:="Open a File To Check Product Lines", MultiSelect:=False)
If Len(strBook) < 6 Then Exit Sub
Set oXLBook = oXLApp.Workbooks.Open(strBook) 'Open an existing workbook
Set oXLSheet = oXLBook.Worksheets(1)
oXLApp.Visible = True               'Show it to the user
oXLSheet.Select
For a = 2 To oXLSheet.Cells(Rows.Count, 1).End(xlUp).Row
    With Session
        .SetMousePos 5, 26
        .TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
        .WaitForEvent rcEnterPos, "30", "0", 5, 26
        .TransmitANSI "A"
        .WaitForEvent rcEnterPos, "30", "0", 7, 26
        .TransmitANSI oXLSheet.Cells(a, 1).Value
        .WaitForEvent rcEnterPos, "30", "0", 10, 26
       .TransmitANSI oXLSheet.Cells(a, 3).Value
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
        .WaitForEvent rcEnterPos, "30", "0", 5, 17
        .WaitForDisplayString "Company", "30", 2, 3
        .SetMousePos 5, 17
        .TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
        .WaitForEvent rcEnterPos, "30", "0", 5, 17
       .TransmitANSI oXLSheet.Cells(a, 4).Value

Hope this makes some sense. Basically the lines in red are where I am inserting the values from Excel in the correct places.

 

[xcruc1at3r]

well am realllyyyyyyyyy glad that you replied .... thanks alottttt...
well as i was waiting for your reply i tried to add some of my efforts so that i can reach some where soo i created this code :
for clear understand Note :
Set excelApp = GetObject(, "Excel.Application")
Set excelApp = GetObject("C:\Documents and Settings\mintezar\Desktop\Book1.xlsx")
z = excelApp.ActiveSheet.Range("B2").Copy <<<< copies the data from Cell B2 and paste it in the software >>> like this >> .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 3, 11
.WaitForDisplayString "NBR:", "30", 3, 6 ( this is were it pastes)
.Paste
.TransmitTerminalKey rcIBMEnterKey << Enter the new screen comes up then goes to C2
z = excelApp.ActiveSheet.Range("C2").Copy
.WaitForEvent rcEnterPos, "30", "0", 1, 80 << NEW screen
.WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67 << cursor default position
.MoveCursor 4, 11 >> move cursor to 4 column, 11 row
.WaitForDisplayString "SEALS..:", "30", 4, 2
.Paste Well this process start from B2 and is till B4 well and my data varies from 500 to 1200 entries so is there any possibility that this process be made for Dynamic range ?
Steps :
if you could add the Loop in it and create a code and i'll test it and let you know the result !
am confused
1st phase it to connect reflection with Excel
2nd phase is to login
3rd goes to the Screen
4th Copies B2 cell data and Paste in the screen and enters
and then Copies C2 data, move cursor to 4, 11 and paste in the screen and enters
and this process should go on till the last cell found with Data.

Sub macro_22333()

Dim excelApp As Object
Dim z As Integer
Set excelApp = GetObject(, "Excel.Application")
Set excelApp = GetObject("C:\Documents and Settings\mintezar\Desktop\Book1.xlsx")
z = excelApp.ActiveSheet.Range("B2").Copy
    With Session
        .WaitForEvent rcEnterPos, "30", "0", 1, 1
        .TransmitTerminalKey rcIBMPf9Key
        .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
        .WaitForEvent rcEnterPos, "30", "0", 5, 13
        .WaitForDisplayString "==>", "30", 5, 9
        .TransmitANSI "1"
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
        .WaitForEvent rcEnterPos, "30", "0", 18, 33
        .WaitForDisplayString "ID:", "30", 18, 29
        .TransmitANSI "akhimki"
        .TransmitTerminalKey rcIBMTabKey

        ' Provide Password for Security
        Dim hostpassword As String
        hostpassword = ""
        hostpassword = .GetPassword("PASSWORD:", "", "", "")
        .TransmitANSI hostpassword
        .TransmitANSI "50.7.13"
        .TransmitTerminalKey rcIBMEnterKey
End With
    With Session
        .WaitForEvent rcEnterPos, "30", "0", 4, 16
        .WaitForDisplayString "CITY/SITE:", "30", 4, 4
        .TransmitTerminalKey rcIBMTabKey
        .TransmitTerminalKey rcIBMTabKey
        .Paste
        TransmitTerminalKey rcIBMTabKey
        .WaitForEvent rcEnterPos, "30", "0", 23, 15
        .WaitForDisplayString "SCREEN:", "30", 23, 7
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
        .WaitForEvent rcEnterPos, "30", "0", 1, 80
        .WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
        .TransmitTerminalKey rcIBMNewLineKey
        .TransmitTerminalKey rcIBMNewLineKey
        .TransmitTerminalKey rcIBMNewLineKey
End With
z = excelApp.ActiveSheet.Range("C2").Copy
    With Session
        .WaitForEvent rcEnterPos, "30", "0", 4, 11
        .WaitForDisplayString "SEALS..:", "30", 4, 2
        .Paste
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
z = excelApp.ActiveSheet.Range("b3").Copy
    With Session
        .WaitForEvent rcKbdEnabled, "30", "0", 1, 1
        .WaitForEvent rcEnterPos, "30", "0", 3, 11
        .WaitForDisplayString "NBR:", "30", 3, 6
        .Paste
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcEnterPos, "30", "0", 1, 80
        .WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
        .MoveCursor 4, 11
        .WaitForDisplayString "SEALS..:", "30", 4, 2
z = excelApp.ActiveSheet.Range("C3").Copy
        .Paste
        .TransmitTerminalKey rcIBMEnterKey
z = excelApp.ActiveSheet.Range("B4").Copy
        .Paste
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcEnterPos, "30", "0", 1, 80
        .WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
        .MoveCursor 4, 11
        .WaitForDisplayString "SEALS..:", "30", 4, 2
        
        
End With
End With
End Sub

 

[xcruc1at3r]

this is the link of the reflection Screens step by step !

https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdHhlckhsdmctbXIybmhVVkJQZ2VfSWc

 

[schielrn]

Get rid of the copy and paste:

Instead of:

.WaitForDisplayString "SEALS..:", "30", 4, 2
z = excelApp.ActiveSheet.Range("C3").Copy
        .Paste
        .TransmitTerminalKey rcIBMEnterKey

Use:

.WaitForDisplayString "SEALS..:", "30", 4, 2        
        .TransmitANSI excelApp.ActiveSheet.Range("C3").value
        .TransmitTerminalKey rcIBMEnterKey

Not sure if you need to specify the workbook or not because as you can see from my code I also specify the workbook. I'm not really sure what the loop would look like because it doesn't appear that it goes down a column as you have it going from C2 to B3 to C3 to B4. Unless it is suppose to go in that order and the next woulf be C4 and B5?

If that is the case then you could do something like:

for i = 2 to excelApp.ActiveSheet.cells(rows.count,"C").end(xlup).row
with session
        .WaitForEvent rcEnterPos, "30", "0", 1, 80
        .WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
        .MoveCursor 4, 11
        .WaitForDisplayString "SEALS..:", "30", 4, 2
        .TransmitANSI excelApp.ActiveSheet.Range("C" & i).value
        .TransmitTerminalKey rcIBMEnterKey
        .WaitForEvent rcEnterPos, "30", "0", 1, 80
        .WaitForDisplayString "UPDATE(Y/N):", "30", 1, 67
        .MoveCursor 4, 11
        .WaitForDisplayString "SEALS..:", "30", 4, 2
        .TransmitANSI excelApp.ActiveSheet.Range("B" & i+1).value
        .Paste
        .TransmitTerminalKey rcIBMEnterKey
end with
next i

Hopefully that makes some sense to you. If the copy and paste is working, then you can still use the loop as I have shown, but I would rather just input the value as opposed to copying and pasting.

 

[schielrn]

I didn't see anything in the links that showed the step by step screens. Also I didn't see the B2 part at the beginning, but hopefully you can build from what I have given you and just not use the +1 as I was in the loop.

 

[xcruc1at3r]

Dam that Goc.Doc its not uploading the SnapShots (..and from office we dont have permission to use any other site for loading....( if you would give me your email address so i'd email it to you...
my data range format in Excel
...Container No. Seal No.
APZU4626368 APA9919941
TRLU3853663 APA9910157
AMFU8833877 APA9910144
APHU6188231 APA9910126
TCLU8618708 APA9907850 this is how the data order is...
start from B2 goes to C2
then B3 goes to C3
And then B4 goes to C4 and so on....till the Last found data

 

[broberg]

I have done som coding along the lines of this, only I call Reflection from Excel as I find that controlling Reflection through Excel easier than the other way around.

I just wanted to give a little code, that may be able to make it a bit easier to write to reflection. The following functionis what I use to transmit text. Bear in mind that It's copied from within my broker class in Excel, but I reckon it all works in you change the "MySession" to "Session"

'---------------------------------------------------------------------------------------
' Procedure : TransmitText
' Author    : Morten Broberg Kristensen
' Date      : 17.01.2011
' Purpose   : Send text to the host, either to a specified location or to where the
'             cursor might be. Optional TextLength, and also it will truncate to fieldlen
'---------------------------------------------------------------------------------------
Public Function TransmitText(ByVal text As String, Optional Row As Long, Optional Column As Long, Optional TextLength As Long)

    Dim fieldLen As Long
    
   On Error GoTo TransmitText_Error

    If Not Row = 0 Then 'If we give it a row then first move the cursor!
        MySession.MoveCursor Row, Column
        MySession.WaitForEvent rcKbdEnabled, "30", "0", 1, 1 'Wait 30 sec for keyboard or time out
        MySession.TransmitTerminalKey rcIBMEraseEOFKey 'Clear field
        MySession.WaitForEvent rcKbdEnabled, "30", "0", 1, 1 'Wait 30 sec for keyboard or time out
    End If
    
    MySession.FindField CursorRow, CursorColumn, rcCurrent, rcAnyField  'Find any field in current field specified by cursorrow and cursorcol and set the properties
    fieldLen = MySession.FoundFieldLength 'Get the foundfieldlength property and use that as amount of chars to retrieve, ie. the full length of the field.
    
    
    
    If Not TextLength = 0 Then 'If textlengh is set then
        text = VBA.Left$(text, Min(TextLength, fieldLen)) 'Truncate text by the lesser of the lengths
    Else
        text = VBA.Left$(text, fieldLen) 'If textlength is not set then truncate by fieldlength
    End If
    
    'And then just send the text
    MySession.TransmitANSI text
    MySession.WaitForEvent rcKbdEnabled, "30", "0", 1, 1 'Wait 30 sec for keyboard or time out

   On Error GoTo 0
   Exit Function

TransmitText_Error:

  Err.Raise Err.Number, "@[Parser.TransmitText] " & Err.Source, Err.Description

    
    
End Function

Afterwards you can just call the function by calling the function

TransmitText("This is a testTxt",4,10)

And it will do the cursor moving and waiting for keyboard enabled etc.
It will remove any text allready entered in the field, start the text in the start-location of the field belonging to the position you pass.
And finally it will truncate the text if neccesary as to not write to the next field.

Use it if you want

 

[xcruc1at3r]

i replaced MySession with Session &
i tried to use the code in excel but its asking for Sub () and if i give it a sub () so it asks for end sub as well !!... (

 

[broberg]

Hi again

First off, you need to include the calling command in a sub, ie.

Sub TestTransmit()
      TransmitText("This is a testTxt",4,10)
End sub

Also if you want to use it from within Excel, you will need to setup a reference first, and then Dim the Session

The library you shoud reference is the "WRQ Reflection for IBM 12.0 Object Library", filename : R8ole32.olb.

If you reference it I would suggest you keep the naming of MySession, and then create a new Session object with the following code.

Dim MySession As Reflection.Session 
Set MySession = new Session

I have centralized all of this code in a class object that handles all my different functions and then all I need is to instantialize this class-object.

Hope this didn't confuse you more than before?