Rumba Mainframe Excel/VBA Macro

[Sachin Shama]

Hi ,
I am trying to write a macro which takes the values from my excel sheet & put it into RUMBA mainframe & viceVersa.I have been successful to code the 'excel to Mainframe' copy functionality.However, I am facing issue when I copy text from mainframes to Excel. I have tried using CopyPSToString / CopyPS / CopyFieldToString functions but everytime the string is returned as blank..... I have read somewhere that I have to change default session settings to 'ATTRB' which can be done by using SetSessionParameters , However, I am not able to use it?
Can anybody please help on this?


Thanks,
Sachin

 

[janvi]

Hi Sachin,
I am new to VBA and want to script down a macro which takes the values from my excel sheet & put it into RUMBA/Extra Xtreme mainframe. i did some reasearch around this but could not find any thing .Can you please help me with how to go forward for this or with the macro which have written so that build some understanding through that.

Thanks,

 

[Sachin Shama]

Okay...I m trying to find whr I have kept the sheet...will let u knw....

 

[janvi]

Thank you very much.U can mail me the code at ajanvi123@yahoo.com

 

[Sachin Shama]

I am pasting the sample here: Instead of "EhlApi32.dll", you have to give the location of EhlApi32.dll which probably will be somewhr in Your C drive.:

Private Declare Function WD_ConnectPS Lib "EhlApi32.dll" (ByVal hInstance As Integer, ByVal ShortName As String) As Integer
Private Declare Function WD_CopyFieldToString Lib "EhlApi32.dll" (ByVal hInstance As Integer, ByVal Position As Integer, ByVal Buffer As String, ByVal Length) As Integer
Private Declare Function WD_CopyStringToField Lib "EhlApi32.dll" (ByVal hInstance As Integer, ByVal Position As Integer, ByVal Buffer As String) As Integer
Private Declare Function WD_Sendkey Lib "Eehllapi.dll" (ByVal hInstance As Integer, ByVal KeyData As String) As Integer
Private Declare Function WD_SetCursor Lib "EhlApi32.dll" (ByVal hInstance As Integer, ByVal Position As Integer) As Integer
Private Declare Function WD_SetSessionParameters Lib "EhlApi32.dll" (ByVal hInstance As Integer, ByVal SSPData As Long) As Integer

Sub Main()
C = WD_ConnectPS(100, "A") ' Connecting to Rumba where 'A' is the first session.For 2nd session provide a value 'B'
Y = ThisWorkbook.Worksheets(Sheet1).Cells(1, 1)
X = WD_CopyStringToField(100, 48, Y) ' At the position of 48, macro will put the value of Y
E = WD_Sendkey(100, "@E") ' Enter command
Z = WD_CopyFieldToString(100, 52, V, 7) ' Macro will pick a string of lenght 7 from the position 52 and will put it into "V" buffer
ThisWorkbook.Worksheets(Sheet1).Cells(1, 2) = V ' Putting the value of "V" back to excel
End Sub

 

[janvi]

Thanks Once again
Its very good and simple code but i am not able to figure out few things
X = WD_CopyStringToField(100, 48, Y)
i see 100 used in almost every statement what does this 100 points too and position 48 refer to what..i mean mainframe screen is generally of row and columns for example like 72x24. if i have to paste the excel data on 5th column of 3rd row how to get position of that field
Also when i tried to run this code it throws and error saying that it can find the Eehllapi.dll. I tried it both in RUMBA and EXTRA! Xtreme but it cannot find the Eehllapi.dll file. i did search on net for the lib corresponding to WD_Sendkey but all those didnt work out​

 

[Sachin Shama]

Replied in your Mrexcel inbox ....

 

[ganeshkumars82]

Hi Sachin,

I have done the above coding. But in my case, I am using Citrix server to access both MS-Access and Rumba emulator. However, in the following code, there is no Rumba server name or ipaddress to connect the Rumba session. And this code is not able to identify the mainframe rumba session or screen as I am opening the session manually throgh Citrix server.

Plesae assist me on the following.

Regards,
Ganesh
ganeshkumars82@gmail.com


Declare Function WD_ConnectPS Lib "C:\Program Files\Rumba-52C\SYSTEM\Ehlapi32.DLL" (ByVal hInstance As Long, ByVal ShortName As String) As Integer
Declare Function WD_SendKey Lib "C:\Program Files\Rumba-52C\SYSTEM\Ehlapi32.DLL" (ByVal hInstance As Long, ByVal KeyData As String) As Integer
Declare Function WD_CopyPSToString Lib "C:\Program Files\Rumba-52C\SYSTEM\Ehlapi32.DLL" (ByVal hInstance As Long, ByVal Position As Integer, ByVal Buffer As String, ByVal length As Integer) As Integer
Declare Function WD_DisconnectPS Lib "C:\Program Files\Rumba-52C\SYSTEM\Ehlapi32.DLL" (ByVal hInstance As Long) As Integer
Declare Function WD_SetCursor Lib "C:\Program Files\Rumba-52C\SYSTEM\Ehlapi32.DLL" (ByVal hInstance As Long, ByVal Position As Integer) As Integer
Sub Rumba()
Dim retval As Integer
Dim screen As String
Dim Nom_S As String
Dim NSub_S As String
'Connects the REXX application program to the presentation space window
retval = WD_ConnectPS(100, "A")
If retval = 1 Then
MsgBox ("This will not run on a users own version of Rumba. Please launch from menu.")
End
End If
screen = String$(Val(7), 0) 'Set value
Do 'What screen
RV = WD_CopyPSToString(100, 16, screen, 7) 'Copy data from screen to dataset
Loop Until RV = 0 'What the above successful?
screen = Trim(screen)
If screen <> "Nom/Sub" Then
MsgBox ("Ensure you are in Nom/Sub Function and Restart")
WD_DisconnectPS (100)
End
End If
Sedol = ActiveCell
Do Until ActiveCell.Value = ""
'Cursor. First value always 100? Second value position in char accross screen

RV = WD_SetCursor(100, 1148)

'Send keystroke until successful
Do
RV = WD_SendKey(100, Sedol)
Loop Until RV = 0
Do 'Enter
RV = WD_SendKey(100, "@E")
Loop Until RV = 0

'Setup Field positions
Nom = 268
NSub = 828

'Retrieve fields values
screen = String$(Val(3), 0)
Do
RV = WD_CopyPSToString(100, Nom, screen, 3)
Loop Until RV = 0
Nom_S = Trim(screen)
Do
RV = WD_CopyPSToString(100, NSub, screen, 3)
Loop Until RV = 0
NSub_S = Trim(screen)

Do 'PF9
RV = WD_SendKey(100, "@9")
Loop Until RV = 0

'Paste field values into Excel
ActiveCell.Offset(0, 1) = Nom_S
ActiveCell.Offset(0, 2) = NSub_S

ActiveCell.Offset(1, 0).Activate
Loop
WD_DisconnectPS (100)
End Sub

 

[janvi]

whether MS-Access and Rumba emulator which you are using are installed as citrix application or are they are on your virtual machine and you are using citrix to logon on your virtual machine/desktop .
If they are on your virtual machine and if you are trying to connect MS on virtual machine with rumba on virtual machine it should work..

 

[ganeshkumars82]

Thanks for your updates.
I don't have information about Citrix, However, I able to run One of the other MF scrapping using Reflections in Citrix. Using the same reflection I am not able to connect the Rumba.

Rumba Items:
I open my MS-Access application, I able to see the following items in my reference file. Where to provide the Hostname and Port number to connect Rumber session.
How to create the object for the same.

Declare Function WD_ConnectPS Lib "C:\Program Files\Rumba-52C\SYSTEM\Ehlapi32.DLL" (ByVal hInstance As Long, ByVal ShortName As String) As Integer

I tried to define and declare the object as follows. Please suggest me to go further. Thanks in advance

dim connect as RUMBASCRIPTATLIB.SYSTEM or session or connectivity or Area

RumbaScript AT 1.0 Type Library with the following classes:</SPAN>
Area</SPAN>
Connectivity</SPAN>
Cursor</SPAN>
FileTransfer</SPAN>
HostOptions</SPAN>
OlAobj</SPAN>
Screen</SPAN>
Session</SPAN>
Sessions</SPAN>
System</SPAN>
TelnetSetup</SPAN>
Waits</SPAN>