kingconsto
New Member
- Joined
- Apr 19, 2017
- Messages
- 32
Hey guys,
I have a macro that inputs into a external system. Once the input has been accepted, the system then kicks out a number and returns it to the excel sheet in column L, next to rate. I am looking to just use the part of the marco that returns the number so I can use it in another macro. Unfortunately, I know nothing about vba and do not know what I'm looking for. Please help!
I have a macro that inputs into a external system. Once the input has been accepted, the system then kicks out a number and returns it to the excel sheet in column L, next to rate. I am looking to just use the part of the marco that returns the number so I can use it in another macro. Unfortunately, I know nothing about vba and do not know what I'm looking for. Please help!
Code:
Sub keystroke()
'
'
'
Dim chnnlno As Single
Dim convDDE As String
Dim counter As Integer
Dim VDU As Object
Dim ctpy As String
Dim convDDE_A As String
Dim pdct As String
Dim valu As String
Dim maty As String
Dim savestatusbar
Dim fe As String
Dim e As String
savestatusbar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Sheets("keystroke").Select
On Error GoTo errorhandler
TabB = "tab field"
TabC = "tab field,"
fe = "field exit,"
e = "enter"
temp = UCase(Left(Trim(InputBox("Which session is HUB in? (Valid answers, A,B,C etc)", "session", "A")), 1))
chnnlno = DDEInitiate("IBM5250", "session" & temp)
temp = Application.DDERequest(chnnlno, "EPS(14,5,1)")
If temp(5) <> "D1771" Then
MsgBox "You are not in D1771!" & vbCrLf & "Transfer Aborted!", vbCritical + vbOKOnly, "invalid Screen"
GoTo errorhandler
End If
temp = Application.DDERequest(chnnlno, "EPS(321,12,1)")
If Trim(temp(5)) = "Counterparty" Then GoTo errorhandler
counter = 4
Do Until Trim(Range("l" & counter)) = ""
counter = counter + 1
Loop
Do Until Range("a" & counter) = ""
If Range("a" & counter) = "NEW" Then
convDDE = "[SENDKEY(" & """NEW""" & "," & TabC & """""" & ")]"
DDEExecute chnnlno, convDDE
DDEExecute chnnlno, "[SENDKEY(enter,wait inp inh)]"
temp = Application.DDERequest(chnnlno, "EPS(321,12,1)")
If Trim(temp(5)) <> "Counterparty" Then GoTo errorhandler
deal_date = Range("g" & counter)
deal_date1 = Right$(deal_date, 2) & Mid$(deal_date, 5, 2) & Mid$(deal_date, 3, 2)
value_date = Range("h" & counter)
value_date1 = Right$(value_date, 2) & Mid$(value_date, 5, 2) & Mid$(value_date, 3, 2)
maturity_date = Range("i" & counter)
maturity_date1 = Right$(maturity_date, 2) & Mid$(maturity_date, 5, 2) & Mid$(maturity_date, 3, 2)
ctpy = """" & Range("b" & counter) & """"
pdct = """" & Range("c" & counter) & """"
pstn = """" & Range("d" & counter) & """," & "field exit"
ccy = """" & Range("e" & counter) & """"
amt = """" & Range("f" & counter) & """," & "field exit"
dta = TabC
corp = """" & 0# & """," & "field exit"
'valu = """" & Range("g" & counter) & """"
'maty = """" & Range("h" & counter) & """"
deald = """" & deal_date1 & """"
valu = """" & value_date1 & """"
maty = """" & maturity_date1 & """"
amt1 = Range("f" & counter)
If amt1 < 0 Then amt = """" & Str$(amt1 * -1) & "-" & """," & "field exit"
intr = Range("k" & counter)
If intr < 0 Then
intr = """" & Str$(intr * -1) & "-" & """," & "field exit"
Else
intr = """" & intr & """," & "field exit"
End If
Rem convDDE_A = "[SENDKEY(" & """*RTL""" & "," & TabC & pdct & "," & ")]"
convDDE_A = "[SENDKEY(" & ctpy & "," & TabC & pdct & "," & pstn & "," & ccy & "," & amt & ")]"
convDDE_B = "[SENDKEY(" & dta & valu & "," & maty & "," & TabC & intr & ")]"
convDDE_C = "[SENDKEY(" & TabC & TabC & TabC & TabC & TabC & deald & ")]"
Rem convDDD_C = "[SENDKEY(" & corp & "," & TabC & TabC & """Y""" & TabC & TabC & """R""" & ")]"
DDEExecute chnnlno, convDDE_A
DDEExecute chnnlno, convDDE_B
DDEExecute chnnlno, convDDE_C
Rem DDEExecute chnnlno, convDDE_C
convDDE_C = "[SENDKEY(" & TabC & TabC & TabC & TabC & TabC & TabC & valu & ")]"
DDEExecute chnnlno, convDDE_C
DDEExecute chnnlno, "[SENDKEY(pf12,wait inp inh)]"
temp = Application.DDERequest(chnnlno, "EPS(300,13,1)")
Range("l" & counter) = temp(5)
If Trim(temp(5)) = "" Then GoTo errorhandler
DDEExecute chnnlno, "[SENDKEY(enter,wait inp inh)]"
Application.StatusBar = "Processing Transaction : " & counter - 4
Range("l" & counter).Select
End If
counter = counter + 1
Loop
Application.StatusBar = "Processing Complete"
MsgBox "Transactions Completed!" & vbCrLf, vbOKOnly, "Completed Screen"
Application.DDETerminate chnnlno
Application.DisplayStatusBar = savestatusbar
Application.DisplayStatusBar = False
End
errorhandler:
MsgBox "Application ended in error - please check and retry!", vbCritical, "Transfer Aborted"
the_end:
Application.DDETerminate chnnlno
Application.DisplayStatusBar = savestatusbar
Application.DisplayStatusBar = False
End Sub