WRQ Reflection/VBA programming

[cshivers]

Does anyone have any experience using VBA with WRQ Reflection? I have tried multiple searches related to WRQ Reflection and VBA with no luck on figureing out how to use VBA to access WRQ Reflection. I cant find how to even connect let alone start sending commands do and from. Right now i am just trying to establish a connection or anything. Any Help is appreciated. Thank you.

 

[cshivers]

So after playing around with WRQ and a lucky guess later i now have this code to connect.

Sub CreateReflectionObject()
    Dim Ribm As Object
    Set Ribm = CreateObject("ReflectionIBM.Session")
    Ribm.Visible = True
    
    With Ribm
        .Hostname = "192.168.56.201"
        .Connect
            End With
End Sub

Now i am trying to Send some keys to it. Any ideas?

 

[arimaspi]

You have probably figured it out already but if not here is a script that i wrote for WRQ Reflection session.

Private Sub CommandButton3_Click()

On Error GoTo ErrorHandler
Const NEVER_TIME_OUT = 0
Dim HT As String ' Chr(rcHT) = Chr(9) = Control-I
Dim CR As String ' Chr(rcCR) = Chr(13) = Control-M
Dim ESC As String ' Chr(rcESC) = Chr(27) = Control-[
HT = Chr(Reflection2.ControlCodes.rcHT)
CR = Chr(Reflection2.ControlCodes.rcCR)
ESC = Chr(Reflection2.ControlCodes.rcESC)
UserForm1.Hide

With Session

.Transmit "beg"
.Transmit HT ' Press Tab (Simulate pressing the keyboard Tab key).
.Transmit "order type"
.Transmit HT
.Transmit "="
.Transmit HT

.Transmit "multis" & CR 'transmit Multis and then carriage return
' Press VtF6 (Perform the Vt F6 function).
.TransmitTerminalKey rcVtF6Key

End With
Exit Sub
ErrorHandler:
Session.MsgBox Err.Description, vbExclamation + vbOKOnly

End Sub

 

[cshivers]

I did, wow I forgot all about this thread until an email popped up saying i had a reply. I actually havn't been using Excel since probably April. Thanks for the reply anyways.

 

[xcruc1at3r]

i really need help from both of you...
as you both are aware of WRQ Reflection

m 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
i can upload a Excel file with screen shots of Reflection if required

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

 

[arimaspi]

I have had more luck in importing data from .txt, or .html files....what i have done in the past is to save my .xls file as a html or txt file, and then do something like below...

Sub GoatSlotsNew()
Const Filename As String = "L:/goatslots.txt"
Dim LineIn As String
Dim FSlot As String
Dim TSlot As String
Dim U As String
Dim S As String
Dim C As String
Dim ProcessCount As Integer
Dim msgstring As String

ProcessCount = 0
GoatMenu.Show
GoatHeader
Open Filename For Input As #1 'this is where you open you text file for reading.
Do
Line Input #1, LineIn 'Read the txt file line by line
If (Mid(LineIn, 1, 1) <> "#") And (Len(LineIn) >= 15) Then 'ignore invalid lines
FSlot = Mid(LineIn, 1, 5) '
TSlot = Mid(LineIn, 7, 5) '
U = Mid(LineIn, 13, 1) 'gather valid data
S = Mid(LineIn, 15, 3) '
C = Mid(LineIn, 19, 1) '
Call GoatSubmit(FSlot, TSlot, U, S, C) 'call sub
ProcessCount = ProcessCount + 1
End If
Loop Until EOF(1)
Session.TransmitTerminalKey rcIBMClearKey
Close #1
msgstring = Str(ProcessCount) + " Slot ranges submitted."
MsgBox msgstring
End Sub

Private Sub GoatSubmit(FromSlot As String, ToSlot As String, Use As String, Size As String, Count As String)
With Session
.WaitForEvent rcEnterPos, "30", "0", 2, 15
.WaitForDisplayString "From", "30", 2, 10
.TransmitANSI FromSlot '
.TransmitANSI ToSlot '
.TransmitANSI Use 'transmit valid data to tnp session
.TransmitANSI Size '
.TransmitANSI Count '
.TransmitTerminalKey rcIBMPf12Key 'transmit F12 key for TNP session
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
End With
End Sub


Hope this helps....

if you open your tnp session there is a lot of help available for vba programming just press F11 as you would in excel....

 

[xcruc1at3r]

thanks alottttt...Bro for your replyyy well am realllyyyyyyyyy glad that you replied ....
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 :
Steps that i top :
1st phase it to connect reflection with Excel or whatever you suggest
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.
help with 4th Phase is required
for clear understanding :
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 ?

Code that i created :

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
    .MoveCursor 4, 11    
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 ! has Snapshots as well
https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdHhlckhsdmctbXIybmhVVkJQZ2VfSWc

 

[arimaspi]

Just create a loop using a FOR statement like below.....

Sub macro_22333()
Dim excelApp As Object
Dim z As Integer
Dim LR as Integer
Set excelApp = GetObject(, "Excel.Application")
Set excelApp = GetObject("C:\Documents and Settings\mintezar\Desktop\Book1.xlsx")

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
With excelApp.ActiveSheet
LR = excelApp.ActiveSheet.Range("B65536").End(xlUp).Row  'FInd the last row
For x = 2 to LR  'create a loop then the data from the cells is copied and pasted below.
z = excelApp.ActiveSheet.Range("B" & x).Copy
.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
.MoveCursor 4, 11 
End With
z = excelApp.ActiveSheet.Range("C" & x).Copy
With Session
.WaitForEvent rcEnterPos, "30", "0", 4, 11
.WaitForDisplayString "SEALS..:", "30", 4, 2
.Paste
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "30", "0", 1, 1
Next x
End With

End With
End With
End Sub

 

[xcruc1at3r]

were you able to see the screen shots in the Excel file ? cause when i open it with Google.doc or download it from google.doc it does not show the snapshots
i'll try your created code ...and would give my feedback... once again thanks alooottt bro for your help and effort