Excel Macro for IBM Mainframe 3270 [SOLVED] - Toolbox for IT [PDF]

OPERATING SYSTEMS

Excel Macro for IBM Mainframe 3270 45

0

0

+

Hi Experts, I am a IBM Mainframe 3270, Reflection User (Terminal Emulation Front End) and I don’t have access for Session Manager. I just need an Excel macro to do some process automation. Is it possible to interact with Mainframe without connectivity? Will Send Keys work from Excel to Mainframe? What are the ways to automate my process in mainframe? Thanks in advance. HT By Hariharan Tharagan 6 years ago EXCEL IBM IBM AIX

45 Answers Answer this question Best Answers First

Sign In to Post a Comment Sign In

A

Answered

andrew-thorne commented 6 years ago

Hariharan, You can write VBA to control a session like this: Public autECLSession As Object Public autECLPS As Object Public autECLOIA As Object Set autECLSession = CreateObject(""pcomm.auteclsession"") Set autECLPS = CreateObject(""Pcomm.auteclps"") Set autECLOIA = CreateObject(""Pcomm.autecloia"") autECLSession.SetConnectionByName (""A"") autECLSession.autECLPS.SetCursorPos lngRow, lngCol autECLSession.autECLPS.SendKeys ""\[eraseeof\]"" autECLSession.autECLPS.SendKeys strData, lngRow, lngCol I write a lot of these procedures for automating uploads to JDE. (Our IT people like to do it but I prefer to do it myself where I can. It is not as fast as the IT people but the delay in implementing is shorter!) Andrew

R

rganzo commented 6 years ago

Hi, Reflections has it's own version of macro recording just like VBA. I believe it's accessed through the ""Tools"" menu. I've automated file downloads and such using the tool and find it to be an intuitive process. It has embedded help and manuals. You must have connectivity to interact with the mainframe. If you can offer more details regarding what you're trying to do, I might be able to help further. Good luck, Robbie

A

andrew-thorne commented 6 years ago

Hariharan, I do not think this can be done without session manager because you always need to have an open session. There is no need to add any references or anything to Excel. All connections are established with the CreateObject and setting the session letter. http://publib.boulder.ibm.com/infocenter/pcomhelp/v5r9/index.jsp?topic=/com.ibm.pcomm.doc/books/html/host_access08.htm will give you all the information you need to set up VBA macros to access mainframe data. Andrew

C

cbreed commented 6 years ago

I have used this in many of my excel host interactions using WRQ V14 Reflections to interact with host screens in the past. I am able to edit and submit jobs, FTP and do simple screen scrape activities. This is a late binding example. 'late binding example Sub Open_Session(x As String) 'x= system to login to public myWRQ as object 'Logon Set myWRQ = CreateObject(""ReflectionIBM.Session"") DoEvents With myWRQ .Visible = True .opensettings 1, ""C:\Program Files\Reflection\"" .Connect .HostcommTimeout = 10 DoEvents 'Do your host/excel interaction activities here End With End Sub 'example of closing the session Private Sub Close_Session(x As String) 'x= system to login to With myWRQ 'back out and close your log in to the host session DoEvents .Disconnect DoEvents .savechanges = False DoEvents .Quit DoEvents Set myWRQ = Nothing DoEvents End With 'Stop Exit Sub End Sub

X

xcruc1at3r commented 6 years ago

Chuck Reed, I need your help my Issue is same as Hariharan's. I 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 don't 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 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(""A1"").Value Selection.Copy .WaitForEvent rcEnterPos, ""30"", ""0"", 3, 11 .WaitForDisplayString ""NBR:"", ""30"", 3, 6 .Paste .TransmitTerminalKey rcIBMEnterKey End With End Sub

X

xcruc1at3r commented 6 years ago

Well, I tried to do that, but I am not able to copy data from Excel the Paste sequence works ok! but how to copy from excel by using Reflection ! I don't know how to do that! :( This one is a recorded for Pasting! Well how to copy from Excel sheet? I am not able to do that. With Session .WaitForEvent rcEnterPos, ""30"", ""0"", 3, 11 .WaitForDisplayString ""NBR:"", ""30"", 3, 6 .Paste .TransmitTerminalKey rcIBMEnterKey End With End Sub

X

xcruc1at3r commented 6 years ago

Hariharan, can you create a Macro for me, if possible?

X

xcruc1at3r commented 6 years ago

Good to hear that :). Well this is how I link excel with Reflection 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 And I have to add another macro in to reach The Screen Where we paste Container No. and press enter to reach Second Screen where we place Seal No. This is the 1st step... if we will be able to this successfully so we can reach the 2nd step. I will let you know step by step so that we can avoid confusion ok :) Sub START_BOOKING() 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 ""AKHIAAK"" .TransmitTerminalKey rcIBMTabKey ' Password has not been recorded for security Dim hostpassword As String hostpassword = """" hostpassword = .GetPassword(""PASSWORD:"", """", """", """") .TransmitANSI hostpassword .TransmitANSI ""50.7.13"" .TransmitTerminalKey rcIBMEnterKey End With End Sub

X

xcruc1at3r commented 6 years ago

Well I did what you told me to do, record it from AutoHotkey, this is what it did: WinWait, Program Manager, IfWinNotActive, Program Manager, , WinActivate, Program Manager, WinWaitActive, Program Manager, Send, {ALTDOWN}{TAB}{TAB}{ALTUP} WinWait, Microsoft Excel - Book1, IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, WinWaitActive, Microsoft Excel - Book1, Send, {CTRLDOWN}c{CTRLUP}{ALTDOWN}{TAB}{ALTUP} WinWait, APL.rsf - WRQ Reflection - IBM 3270 Terminal, IfWinNotActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, , WinActivate, APL.rsf - WRQ Reflection - IBM 3270 Terminal, WinWaitActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, Send, {CTRLDOWN}v{CTRLUP}{SHIFTDOWN}{SHIFTUP}{ALTDOWN}{ALTUP} WinWait, Microsoft Excel - Book1, IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, WinWaitActive, Microsoft Excel - Book1, Send, {RIGHT}{CTRLDOWN}c{CTRLUP}{ALTDOWN}{ALTUP} WinWait, APL.rsf - WRQ Reflection - IBM 3270 Terminal, IfWinNotActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, , WinActivate, APL.rsf - WRQ Reflection - IBM 3270 Terminal, WinWaitActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, Send, {TAB}{TAB}{CTRLDOWN}v{CTRLUP}{SHIFTDOWN}{SHIFTUP}{ALTDOWN}{ALTUP} WinWait, Microsoft Excel - Book1, IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, WinWaitActive, Microsoft Excel - Book1, Send, {DOWN}{LEFT}{CTRLDOWN}c{CTRLUP}{ALTDOWN}{ALTUP} WinWait, APL.rsf - WRQ Reflection - IBM 3270 Terminal, IfWinNotActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, , WinActivate, APL.rsf - WRQ Reflection - IBM 3270 Terminal, WinWaitActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, Send, {CTRLDOWN}v{CTRLUP}{SHIFTDOWN}{SHIFTUP}{ALTDOWN}{ALTUP} WinWait, Microsoft Excel - Book1, IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, WinWaitActive, Microsoft Excel - Book1, Send, {RIGHT}{CTRLDOWN}c{CTRLUP}{ALTDOWN}{ALTUP} WinWait, APL.rsf - WRQ Reflection - IBM 3270 Terminal, IfWinNotActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, , WinActivate, APL.rsf - WRQ Reflection - IBM 3270 Terminal, WinWaitActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, Send, {TAB}{TAB}{CTRLDOWN}v{CTRLUP}{SHIFTDOWN}{SHIFTUP}{ALTDOWN}{ALTUP} WinWait, Microsoft Excel - Book1, IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, WinWaitActive, Microsoft Excel - Book1, Send, {DOWN}{LEFT}{CTRLDOWN}c{CTRLUP}{ALTDOWN}{ALTUP} WinWait, APL.rsf - WRQ Reflection - IBM 3270 Terminal, IfWinNotActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, , WinActivate, APL.rsf - WRQ Reflection - IBM 3270 Terminal, WinWaitActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, Send, {CTRLDOWN}v{CTRLUP}{SHIFTDOWN}{SHIFTUP}{ALTDOWN}{ALTUP} WinWait, Microsoft Excel - Book1, IfWinNotActive, Microsoft Excel - Book1, , WinActivate, Microsoft Excel - Book1, WinWaitActive, Microsoft Excel - Book1, Send, {RIGHT}{CTRLDOWN}c{CTRLUP}{ALTDOWN}{ALTUP} WinWait, APL.rsf - WRQ Reflection - IBM 3270 Terminal, IfWinNotActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, , WinActivate, APL.rsf - WRQ Reflection - IBM 3270 Terminal, WinWaitActive, APL.rsf - WRQ Reflection - IBM 3270 Terminal, Send, {TAB}{TAB}{CTRLDOWN}v{CTRLUP}{SHIFTDOWN}{SHIFTUP}{ALTDOWN}{ALTUP} I made more then 50 entries but how would we create a Loop! This it find the last cell with data!

X

xcruc1at3r commented 6 years ago

Well I have a macro which opens reflection from Excel and connects it from Host name with IP address but! It stops when I need it to press F9 ki and then it reaches for login window and asks for password!

C

cbreed commented 6 years ago

If you use the code I posted on Feb. 27th, I should be able to to help you with the commands to enter the values you need. For instance pressing F9 would be stated as: .transmitterminalkey rcibmpf9key 'early binding or .transmitterminalkey 386 ' late binding (matching my example code)

X

xcruc1at3r commented 6 years ago

Well check this out Chuck .... well error accrue is Automation syntax and is not able to identify .waitForEventPos command and .TransmitTerminalKey.....!!! Sub CreateReflectionObject() Dim Ribm As Object Dim Session As Object Set Ribm = GetObject(""ReflectionIBM.Session"") Ribm.Visible = True DoEvents With Ribm .opensettings 1, ""C:\Documents and Settings\Desktop\APL.rsf"" .Hostname = ""host ip"" .Connect End With With Session .WaitForEvent rcEnterPos, ""30"", ""0"", 1, 1 .TransmitTerminalKey rcIBMPf9Key .WaitForEvent rcKbdEnabled, ""30"", ""1"", 1, 1 .WaitForEvent rcEnterPos, ""30"", ""0"", 1, 1 .TransmitTerminalKey rcIBMPf9Key .WaitForEvent rcEnterPos, ""30"", ""0"", 5, 26 .TransmitANSI ""1"" .TransmitTerminalKey rcIBMEnterKey .WaitForEvent rcKbdEnabled, ""30"", ""0"", 1, 1 .WaitForEvent rcEnterPos, ""30"", ""0"", 18, 33 .WaitForDisplayString ""ID:"", ""30"", 18, 29 .TransmitANSI ""User ID"" .TransmitTerminalKey rcIBMTabKey ' Password has not been recorded for security Dim hostpassword As String hostpassword = """" hostpassword = .GetPassword(""PASSWORD:"", """", """", """") .TransmitANSI hostpassword .TransmitANSI ""50.13.9"" .TransmitTerminalKey rcIBMEnterKey End With End End Sub

C

cbreed commented 6 years ago

I'm assuming that you are using early binding and have placed a reference in your excel VBE project to Reflections for IBM Session. Also, may I ask why your using GetObject instead of CreateObject? I was under the impression that GetObject is used when you already have an instance of the application that you are attempting to interact with. Will that be true in your case or do you want Excel to open it's own instance of the Host session? -Chuck

C

cbreed commented 6 years ago

After further inspecting the code, you might try replace ""With Session"" with ""With RIbm"" as your code had not set Session as an object. That is most likely why the code is not performing as you expect at the moment.

X

xcruc1at3r commented 6 years ago

Well, I am using GetObject because I already have Reflection created on my desktop with settings so I guess I don't need it to connect ! rite cause it already has the host settings included! but why is it giving Automation syntax error! I tried to Replace With Session with With Ribm but still the same error accrues

X

xcruc1at3r commented 6 years ago

Do we have to add .wait (#) time so that the next part of coding should execute after Reflection is completely connected and after that f9 and user id and password command should execute !

C

cbreed commented 6 years ago

After you Connect command add .HostcommTimeout = 10 to handle a time out. See if that helps you. Also, I took your code above made the recommended changes along with changing the GetObject to CreateObject. Without that change, I too got an automation error.

X

xcruc1at3r commented 6 years ago

I have changed GetObject to CreateObject & added HostcommTimeout = 10 like this End With With Ribm HostcommTimeout = 10 End With With Session (coding stopping here) .TransmitTerminalKey rcIBMPf9Key I have tried With Ribm and as well as with Session the coding stops at error : Run-time error '4132' application defined or object defined error... reflection screen is getting connected with the given ip but is not executing the .TransmitTerminalKey rcIBMPf9Key USER_2386225 I cannot download from your link thanks to office restriction only doc.google

C

cbreed commented 6 years ago

Chang still with session to be with? ribm and give it a try.

X

xcruc1at3r commented 6 years ago

I did that changed with session to with Ribm, but still giving error at .TransmitTerminalKey rcIBMPf9Key I have written coding connecting reflection with Excel that has loop problem! I am not able to create the loop! Maybe you can help me with that !!

X

xcruc1at3r commented 6 years ago

When I recorded the macro in Reflection this is what the recorded coding looks like Sub START_BOOKING() 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 ""AKHIAAK"" .TransmitTerminalKey rcIBMTabKey ' Password has not been recorded for security Dim hostpassword As String hostpassword = """" hostpassword = .GetPassword(""PASSWORD:"", """", """", """") .TransmitANSI hostpassword .TransmitANSI ""50.13.9"" .TransmitTerminalKey rcIBMEnterKey End With End Sub

X

xcruc1at3r commented 6 years ago

What if we control Excel From Reflection? Well it is executing properly coping cell data from excel and pasting it in reflection like coping B1 and pasting it in reflection and then rcIBMEnterKey then copies data from C1 and paste it in reflection and then rcIBMEnterKey and in this order it moves down... from B2 to C2 then B3 to C3... and issue is that am not able to create a loop! This the last cell with data.

X

xcruc1at3r commented 6 years ago

Is there any possibility that you can please write a vba code for me if I can upload the Excel File with Screen Shots of WRQ Reflection screen to be used ?

X

xcruc1at3r commented 6 years ago

Can you please create a macro for me? And how will I create a excel macro in hotkeys?

X

xcruc1at3r commented 6 years ago

Well I have downloaded the portable version of AutoHotkey as per your instructions and am not able to understand what do I have to do with it. Do you want me to record the Macro given in Excel ? Would it cover the Link between Excel and Reflection?

HT

Hariharan Tharagan commented 6 years ago

Hi, I am trying to understand your code and could you please tell me what you want to do exactly? Like please mention the Process Step by Step. We will complete your macro ASAP. Thanks, Hariharan Tharagan

X

xcruc1at3r commented 6 years ago

1st step We need to open Reflection from Excel which is on the desktop 2nd step Transmit f9 ki and do the process of id and password like this .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 ""AKHIAAK"" .TransmitTerminalKey rcIBMTabKey ' Password has not been recorded for security Dim hostpassword As String hostpassword = """" hostpassword = .GetPassword(""PASSWORD:"", """", """", """") .TransmitANSI hostpassword .TransmitANSI ""50.13.9"" .TransmitTerminalKey rcIBMEnterKey 3rd step copy Data from excel A1 and paste in reflection z = excelwb.Worksheets(""Load"").Range(""A 1"").Value Selection.Copy .WaitForEvent rcEnterPos, ""30"", ""0"", 3, 11 .WaitForDisplayString ""NBR:"", ""30"", 3, 6 .Paste 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

X

xcruc1at3r commented 6 years ago

If you can give me your email address so can send you the excel sheet which snapshots of process so that you can get the clear picture.

X

xcruc1at3r commented 6 years ago

Well cell range differs. Its not fixed. Well it will record as many times I do that process right. But it would create a loop so that it can continue on it own till the last Cell with data.

SR

Stewart Rotherham commented 6 years ago

As a bear of but little brain, and looking at this as an outsider with no access to the IBM Reflections toolkit, but just looking at the subroutine ""CreateReflectionObject() "" above, is the Object ""Session"" actually being associated with the Reflections tools at all? It appears that the Object ""RIBM"" is being created as a ""Session"" function in the ""ReflectionIBM"" toolkit. If that is so, then the line ""With Session"" should read ""With RIBM"", and the "".TransmitTerminalKey"" function should then become available. ' Stewart

C

cbreed commented 6 years ago

Also I need to add that in the Excel VBE it is necessary to make a reference to reflections for host or the Reflection specific variables will not be recognized without converting them to their specific integer.?

C

cbreed commented 6 years ago

I've taken the sample code that you have provided and included the suggestions given to it during this discussion. Take this an make the necessary edits to fit your situation and give it a try: Sub CreateReflectionObject() 'This example uses late binding. You'll notice that all of the reserve words from Reflections for Session 'have been converted to their integer equivalants. You will not have to do this conversion if you use 'early binding and in the VBE set a reference to ""Reflections for Session"" Dim Ribm As Object Set Ribm = CreateObject(""ReflectionIBM.Session"") Ribm.Visible = True DoEvents With Ribm .opensettings 1, ""C:\Documents and Settings\Desktop\APL.rsf"" .Hostname = ""host ip"" .Connect .hosttimeout = 10 .WaitForEvent 6, ""30"", ""0"", 1, 1 'rcEnterPos .TransmitTerminalKey 386 'rcIBMPf9Key .WaitForEvent rcKbdEnabled, ""30"", ""1"", 1, 1 .WaitForEvent 6, ""30"", ""0"", 1, 1 'rcEnterPos .TransmitTerminalKey 386 'rcIBMPf9Key .WaitForEvent 6, ""30"", ""0"", 5, 26 'rcEnterPos .TransmitANSI ""1"" .TransmitTerminalKey 289 'rcIBMEnterKey .WaitForEvent rcKbdEnabled, ""30"", ""0"", 1, 1 .WaitForEvent 6, ""30"", ""0"", 18, 33 'rcEnterPos .WaitForDisplayString ""ID:"", ""30"", 18, 29 .TransmitANSI ""User ID"" .TransmitTerminalKey 405 'rcIBMTabKey ' Password has not been recorded for security .TransmitANSI .GetPassword(""PASSWORD:"", """", """", """") .TransmitANSI ""50.13.9"" .TransmitTerminalKey 289 'rcIBMEnterKey End With End Sub

HT

Hariharan Tharagan commented 6 years ago

Hi Andrew, Thank you so much Andrew for the quick reply? Is this can be done without connecting Session Manager?? Do I need to add some references in VB Project? Like adding dll? Etc. Could you please explain further? I am very new to this? Really thanks for your reply?. Thanks, Hariharan Tharagan

HT

Hariharan Tharagan commented 6 years ago

Wow, that's great. I need some done examples, so that I can understand first what all is about. Thank you so much for your reply. Thanks, Hariharan Tharagan

EK

Eric Katchmar commented 6 years ago

You have to have a connection to the mainframe - what you are doing is basically screen scraping - and you are working through the buffer, sending keys & looking fot the response from the mainframe, in the form of ""screens"" - which you discern as data positions within the buffer. What you have to do is walk through the manual data entry process on the mainframe, through an emulator & then write code that ""walks"" the same screens - and waits for the mainframe to be ""ready"" to accept data - otherwise, you will wind up with a locked screen in the background. I used to be an expert in this technology, with Attachmate technologies back in the early 90's - and what you are essentially doing is automating the exact same keystrokes & data entry that you would do if it was a manual process - if you fail to keep in sync with the mainframe, you will really honk things up & could wind up with a ""hung"" session.

EK

Eric Katchmar commented 6 years ago

What you need to do is record a macro within WRQ Reflections and do an end-to-end transaction - then wrap the excel vba stuff around that macro. You can take shortcuts - e.g. instead of starting from scratch, make the assumption that the mainframe session already exists & the user is at the first screen - then all you do is automate the work (vs. starting from scratch & needing to establish the connectivity to the mainframe & navigation to the beginning screen (whether TSO or CICS) In the same way that you can record a macro in excel, you can record a macro in WRQ Reflections (or any terminal emulator - like attachmate or hummingbird) - then generalize the code into a loop (paying particular attention to the ""wait for mainframe to be ready"" functions - which may be waiting for the x-clock to go off, or waiting for the appearance of particular screens.

EK

Eric Katchmar commented 6 years ago

What you need to do is record a macro within WRQ Reflections and do an end-to-end transaction - then wrap the excel vba stuff around that macro. You can take shortcuts - e.g. instead of starting from scratch, make the assumption that the mainframe session already exists & the user is at the first screen - then all you do is automate the work (vs. starting from scratch & needing to establish the connectivity to the mainframe & navigation to the beginning screen (whether TSO or CICS) In the same way that you can record a macro in excel, you can record a macro in WRQ Reflections (or any terminal emulator - like attachmate or hummingbird) - then generalize the code into a loop (paying particular attention to the ""wait for mainframe to be ready"" functions - which may be waiting for the x-clock to go off, or waiting for the appearance of particular screens.

EK

Eric Katchmar commented 6 years ago

Pretty much, all VBA is the same - if not extremely similar - you can merge the Excel & Reflections code into one macro - when launched from Excel, you need to get the handle to the session - if launched from reflections - you need to get the handle to the excel file - you can do file open commands - or assume the file is open & just reference the open file & data within. Sorry, I don't have the reflections client or access to a mainframe, so I can't help you with the actual code. The basic idea is that within your macro, you can reference stuff in memory & push it to the session buffer for the mainframe - but you have to have the connections to each.

HT

Hariharan Tharagan commented 6 years ago

Hi, I used auto hot keys for this automation and the macro is running successfully without any difficulties. http://www.autohotkey.com/ Thanks, Hariharan Tharagan

HT

Hariharan Tharagan commented 6 years ago

Good work done by you? I also got the same problem! For looping, I created an Excel Macro with short cut keys and I run the macro through auto hot keys. For Example: Activecell.offset(1,0).select Selection.copy For this give a shortcut key and give the short cut keys in Auto hot keys. I think you will get that! Thanks, Hariharan Tharagan

HT

Hariharan Tharagan commented 6 years ago

Hi, I can guide you for creating the macro. Whatever you want you ask me I can guide you on this. Thanks, Hariharan Tharagan

HT

Hariharan Tharagan commented 6 years ago

Hi, Frankly saying I do not understood your coding? Did you see Autohotkey, and installed it? If not please install it and use the macro recorder, record your activity as it is from excel to mainframe and paste it. Your work will be done!! Thanks, Hariharan Tharagan

HT

Hariharan Tharagan commented 6 years ago

Hi, This is the Auto hot key Code for running the excel macro Create an Excel Macro - Assign a Short cut key as Contl+y. Update the below code in the existing Auto hot key code and run the ahk. (Send, {CTRLDOWN}y{CTRLUP}) Thanks, Hariharan Tharagan http://knetsites.wipro.com/sites/5446/Pages/FAO-COE.aspx

HT

Hariharan Tharagan commented 6 years ago

It will not link, but it will call the windows name and do the work what you say? Go to (Autohotkey\AutoHotkey104805\AutoScriptWriter) Open this AutoScriptWriter - Click on record - Do a sample process (Copy data from excel and paste in the mainframe) and see the coding. Definitely you will be able to understand, if not paste the coding and we can discuss on that! Thanks, Hariharan Tharagan

EK

Eric Katchmar commented 6 years ago

With respect to this question. ""Is it possible to interact with Mainframe without connectivity? "" The answer is definitively NO - you cannot interact with the mainframe without having a connection established. You need to establish the steps to get connectivity then manually map out what you would like to automate and do it from the reflections macro (unless you can insert the reflections code into your excel macro). What you are trying to do is not difficult. You just need to have the appropriate access & understanding.

Related Tool to Coordinate Service Startup Across a Cluster Small but Perfectly Formed: IBM z14 Mainframe

How to Prevent Shapes from Being Deleted in Certain Range in Excel 2003 Excel Custom addin Development in C#/VB.net Replace Real Adapter in SEA (VIO) Db2 Application Tuning -- Part 4: Data Warehouse Strategies Db2 Application Tuning -- Part 2: The Tactics of SQL Tuning

PLAY HOT FULL TRACK ARTISTS CAMILA CABELLO CAMILA CABELLO NEVER BE THE SAME NEVER BE THE SAME

About Advertise Guidelines Terms Privacy Feedback

© 1998-2018 Toolbox is among the trademarks of Ziff Davis, LLC and may not be used by third parties without explicit permission.