Page 1 of 1

Run an mainframe VB macro from Excel

PostPosted: Tue May 07, 2013 6:17 pm
by Vijender Singh
Hi all,

Need to run an Mainframe VB script from Excel.

I have recorded a Mainframe macro and pasted it in excel. On a daily basis i have to change the data in the macro from the web site.

All i do is copy the data from website and paste it in excel and click on a button, that button sorts the data and edits the mainframe VB script on sheet3.

Then we copy the script paste it on mainframe and run the macro.

I want to omit the copy and paste option and run the macro directly from Excel, like a mirror.

Thanks & Regards
Vijender Singh

Re: Run an mainframe VB macro from Excel

PostPosted: Tue May 07, 2013 6:39 pm
by enrico-sorichetti
and... by grace
How is this related to the Forum Rules ???

Re: Run an mainframe VB macro from Excel

PostPosted: Tue May 07, 2013 9:41 pm
by dick scherrer
Hello and welcome to the forum,

If you post this "mainframe macro" it may help someone help you.

It should also give us a hint where to put your topic :)

Re: Run an mainframe VB macro from Excel

PostPosted: Sat May 11, 2013 1:18 am
by Vijender Singh
I am new to the subject forum i was searching for an answer to my question. and came through this website and as it said IBM mainframe, i posted my query. Guide me if i am wrong, as am in need of an answer which can help me. :)

Re: Run an mainframe VB macro from Excel

PostPosted: Sat May 11, 2013 1:41 am
by dick scherrer
Hello,

You were asked to post this "mainframe macro". You have not yet done so.

I have never seen a mainframe run a macro on a Win-box.

Re: Run an mainframe VB macro from Excel

PostPosted: Mon May 13, 2013 12:47 pm
by Vijender Singh
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)

REM This line calls the macro subroutine
subSub1_
sub subSub1_()
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys"001"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys"p1.1"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"gbp1.1"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"1.1"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys"002"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys"p2.2"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"gbp2.2"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"2.2"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys"003"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys"p3.3"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"gbp3.3"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"3.3"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys"004"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys"p4.4"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"gbp4.4"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"4.4"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys"005"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys"p5.5"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"gbp5.5"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"5.5"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys"006"
autECLSession.autECLPS.SendKeys "[tab]"
autECLSession.autECLPS.SendKeys"p6.6"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"gbp6.6"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys"6.6"
autECLSession.autECLPS.SendKeys "[field+]"
autECLSession.autECLPS.SendKeys "[roll up]"

This is the recorded Mainframe macro which i want to run from Excel. Please help

Re: Run an mainframe VB macro from Excel

PostPosted: Mon May 13, 2013 2:00 pm
by enrico-sorichetti
being new does not prevent from trying to use the proper terminology

a MAINFRAME <something> is - the name tells - something that runs/executes ON THE MAINFRAME

even if PCOMM, being a 3270 emulator, is loosely related to mainframes,
since no mainframe software ( zOS, zVM, zLINUX) supports VB it is not a mainframe problem.

You might be better off in a forum dedicated to windows, and VB scripting.

Re: Run an mainframe VB macro from Excel

PostPosted: Mon May 13, 2013 5:25 pm
by Vijender Singh
Dim sAVEStatusbar, TimeLmt
Dim ChannelNumber As Single 'DDE Channel ID
Dim ConvDDE As String 'String to be used in creating Conversations
Dim Temp As Variant 'Misc Field
Dim FE As String, Transaction As Integer, Counter As Integer
Dim ScreensFilledIn As Integer, ScreensProcessed As Integer
Dim TabC As String, VDU As Object
Dim ValueDate As String, PostingDate As String
ScreensFilledIn = 1
FE = "field exit"
TabC = "tab field,"
Transaction = 7 'Transactions start from row number 7 in this spreadsheet
Set VDU = Workbooks(ActiveWorkbook.Name).Worksheets("VDU")
sAVEStatusbar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
'set up errorhandling
On Error GoTo ErrorHandler ' Enable error-handling routine.
Application.EnableCancelKey = xlErrorHandler 'Cancel key will trigger errorhandling
'Initialise the conversation
Temp = InputBox("Which Session is HUB in? (Valid answers, A,B,C etc)")
ChannelNumber = DDEInitiate("IBM5250", "Session" & Temp)
'First check that we are in correct screen
' in this case from position 94 (94/80 = line 1, position 14)
Temp = Application.DDERequest(ChannelNumber, "EPS(13,3,1)")
If Temp(5) <> "C34" Then
MsgBox "You are Not in the Right Screen!" & vbCrLf & _
"Transfer Aborted!", vbCritical + vbOKOnly, "Not in C34 Screen"
ErrorHandler: ' Error-handling routine.
End If

'**************************************************************************************
Function CreateVDUTrans(VDU As Object, Transaction As Integer, ChannelNumber) As String
'**************************************************************************************
Dim Screen1, Screen2, Branch
Dim FE As String
Dim TabB As String, ConvDDE_a As String, ConvDDE_b As String
'The purpose of this function is to read one transaction record from Excel and
'transfer it to HUB
FE = "field exit"
TabB = "tab field"
Branch = """" & VDU.Range("A" & Transaction) & """," & FE & ","
Screen1 = VDUAccessFnc(WrkShtID:=VDU, TransCount:=Transaction, ColumnNo:="B", _
TrueVal:=TabB, FalseVal:=FE) & ","
Screen2 = VDUAccessFnc(WrkShtID:=VDU, TransCount:=Transaction, ColumnNo:="C", _
TrueVal:=TabB, FalseVal:=FE) & ","
'Add it all up to prepare to upload to HUB
ConvDDE_a = (Screen1 & " " & Screen2)
ConvDDE_b = "[SENDKEY(ChannelNumber, 275, ConvDDE_a)]"
End Function


This is what i wrote on Excel to execute on PCOM IBM 5250.
The first one is to find out if i am on correct screen if yes, then execute the second funtion.
On excel sheet A1 has a number value. I have divided that into 2 on B and C. Where Screen1 is mentioned on B7 and Screen2 is mentioned on C7.
I want to send the data on B7 and C7 to the said screen on Pcom. But i am unable to send it to the 275 (03/033) On status bar.
Please help or guide me to a helpfull forum for a quick responce.

Re: Run an mainframe VB macro from Excel

PostPosted: Mon May 13, 2013 5:46 pm
by NicC
Use Google

Re: Run an mainframe VB macro from Excel

PostPosted: Mon May 13, 2013 7:09 pm
by dick scherrer
Hello,

Suggest you both ask these questions of PCOM support.

As this is a PC application/situation (rather than something on the mainframe), a PC or PCOM "forum" will be better able to help.