How to Run a Mainframe Session Through Excel?

I want to open a mainframe session by clicking a link or command button in Excel file. Suppose for accessing something we give "ts899" on the PCOM screen, instead of asking user to enter this it should be able to take him directly to the login screen... on the similar grounds if we write "L abcde123" for accessing a cics screen then on clicking in excel it should take user to the login screen of this cics screen....

you don't run a mainframe session "through" excel - you are simply launching the mainframe emulation software from within excel. This is very basic stuff - you simply need to write a macro in whatever emulator language you are using (e.g. attachmate, hummingbird etc...) that launches a mainframe session, logs on & navigates to whatever system you want them to access. There should be an option to not show the gui, so in essence you are working through the buffer. Your macro would be modified to ask for userid and password & then submit these at the appropriate time (when the logon screen appears). You can extract information from the excel file & enter it on the mainframe & visa versa - basically, what you are doing is called "screen scraping".

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

Popular White Paper On This Topic

you don't run a mainframe session "through" excel - you are simply launching the mainframe emulation software from within excel. This is very basic stuff - you simply need to write a macro in whatever emulator language you are using (e.g. attachmate, hummingbird etc...) that launches a mainframe session, logs on & navigates to whatever system you want them to access. There should be an option to not show the gui, so in essence you are working through the buffer. Your macro would be modified to ask for userid and password & then submit these at the appropriate time (when the logon screen appears). You can extract information from the excel file & enter it on the mainframe & visa versa - basically, what you are doing is called "screen scraping".

If using Attachmate to interface, is there any way to see if a command is processed before proceding? I'm waiting a certain amount of time, but that's not ideal. The only thing I can think of is to save the response from the screen and analyze it, but I'd think that there would be an easier way.

The scripting language for Attachmate, called ExtraBasic (at least that was what it was called 20 years ago) has several strategies for determining when the mainframe is ready to accept input. One is waiting for the xclock to go away - but this is not reliable, since it may go off & on several times before settling down. The best approach is to identify a unique character string on the target screen and issue a wait for command (basically saying wait until this string occurs in the buffer at this particular screen location (row / column) - In this way, you are able to tell which screen you are on. I believe some of the newer emulators have a macro recording feature that allows you to identify several points on a screen as unique identifiers. What I am describing is a process of "walking" the mainframe screens in a transactional processing mode (e.g. establish session, logon to cics, issue transaction - then "walk" the transaction as if you were doing it manualy - but you've written a program to do it for you). If you are sitting on a single screen, issuing commands - then you need to do a similar process to identify "readiness" to continue - do the process manually and take note of any display characteristics that would give you a clue that the process is complete - then incorporate them into your wait statements.

However I have noticed that if I do something immediately afterwards, I can crash the session, so I use the SLEEP procedure to wait 200 milliseconds before proceeding.

If you are interested, I have a small library of about 7 VBA macros that will do most tasks that are required to read and write to a mainframe session. I use them a lot to make large scale updaters and inquirers with Excel.

It may be worth trying to Record a Macro of you undertaking this process, and then identifying what variables you can control in the dialogue to use in future. This might enable you to pick out the things that tell YOU - as a user - which screen the mainframe is currently displaying. You can then work with that as a starting point.

Andrew, yes, I'd like to see some of your VBA for working with Attachmate, if you don't mind; it would probably be pretty helpful. I've been using Sleep, which seems to work OK (as long as something doesn't take longer than expected). The Wait for string line in Attachmate seems to get hung up on occasion for me. And, the default .Wait hangs everything else up, which is worse than Sleep; so I never use that. Thanks, Excellin'

'
' Set the session we are using
'
Sub SetConnectionByName(strName As String)
Call DispMsg("Click OK if the session is '" & strName & "'")
autECLSession.SetConnectionByName (strName)
If autECLSession.commstarted = False Then
Call DispMsg("There does not appear to be a session '" & strName &
"'")
Call ObjEndExtra
End
End If
End Sub

Basically I set up and initialize the connection. Then I clear the screen
(function key 22 in JD Edwards) and put an "I" on row 3 column 23,
followed by the value of strIPG on row 6 column 23 and the value of
strCust on row 6 column 71. Finally I send the "enter" key so that JDE
will search for the information I require. There is no need to add the
WaitForInputReady as this is done in my SendKeys2Extra procedure so I can
then read the result and close the connection.

Putting these commands in loops should enable you to do lots of things. My
main use for them is doing large uploads for inquiries.

I can not see anything wrong. Is there no error and no response on the mainframe side? The only thing I can think of is the session name may be wrong. Try putting msgbox statements between each autECLSession command to check if anything is happening on the mainframe session.

I assume that you are using IBM’s Personal Communications emulator and not another, for example Extra!

I too open the session, log-in and go to the screen from where I will do the update manually, so there should be no problem there.

Are you getting any error messages? You said that the macro cannot identify the session. Does that mean that the SetSessionByName assignment is failing? If that is the case you need to find out the name of the session, which will be displayed in the window title – along with other information so be careful about which bit you take as the session name.

Not sure if this helps, and unfortunately I can't really support past this post but I've managed to have excel communicate with EXTRA! sessions. I've had a lot of success in automating virtually anything through this and have been able to use my shared workbook over hundreds of computers in 2 states. Below are some examples that may help. As far as timing, as Eric had mention previously relying solely on a definite time for the host to communicate (like WaitforHost) is unreliable and could result in slowing your code. I use : Sess0.Screen.WaitForString "WHATEVER TEXT YOU WANT IT TO WAIT FOR", 3, 11
The values 3,11 are the row and column on the screen. You don't have to express the length for this method and it works wonderfully. Just make sure your defining the text properly or else your code will hang until it times out. TO OPEN A NEW SESSION: Set System = CreateObject("EXTRA.System") ' Gets the system object If (System Is Nothing) Then MsgBox "Could not create the EXTRA System object. Stopping macro playback." Stop End If Set NewSes = System.Sessions.Open("C:\Program Files\Attachmate\EXTRA!\Sessions\[WHATEVER THE NAME OF YOUR SESSION IS}.EDP") If (Session Is Nothing) Then MsgBox "Could not create the Sessions collection object. Stopping macro playback." Stop End If

TO COMMUNICATE WITH AN EXISTING SESSION: Dim Sessions As Object Dim System As Object Set System = CreateObject("EXTRA.System") ' Gets the system object If (System Is Nothing) Then MsgBox "Could not create the EXTRA System object. Stopping macro playback." Stop End If Set Sessions = System.Sessions If (Sessions Is Nothing) Then MsgBox "Could not create the Sessions collection object. Stopping macro playback." Stop End If ' Set the default wait timeout value g_HostSettleTime = 1 ' milliseconds OldSystemTimeout& = System.TimeoutValue If (g_HostSettleTime > OldSystemTimeout) Then System.TimeoutValue = g_HostSettleTime End If ' Get the necessary Session Object Dim Sess0 As Object Set Sess0 = System.ActiveSession If (Sess0 Is Nothing) Then MsgBox "Session is not open! Please login to and try again." If (Sess0 Is Nothing) Then Exit Sub If Not Sess0.Visible Then Sess0.Visible = True
I hope this helps! Good luck to you!