Link Vba To Terminal Emulator

dodger7 · Sep 6th 2007

Hi guys

im currently writing a piece of code that will link excel with the application I use at work, which is a terminal emulator called TN3270.

an example of my code is as follows:

Code

Sub hive()




edate = Sheet1.Range("B2").Value
bname = Sheet1.Range("C2").Value
bname = Left(bname, 17)
jno = Sheet1.Range("D2").Value




AppActivate ("Session A - tn3270")




For counter1 = 2 To 226


acolumn = "A" & counter1
polno = Sheet1.Range(acolumn).Value


SendKeys "{BREAK}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "IB01 DIS", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))


SendKeys "{TAB 2}", True
SendKeys polno, True
SendKeys "{TAB 2}", True
SendKeys "1", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{UP 2}{LEFT 31}", True
SendKeys "`````", True
SendKeys "^c", True
SendKeys "{F4}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "enter", True
SendKeys "{TAB 2}", True
SendKeys "^v", True
SendKeys "{TAB}", True
SendKeys "6", True
SendKeys "{ENTER}, true"
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "eer1", True
SendKeys edate, True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{TAB 2}", True
SendKeys bname, True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{F4}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "check", True
SendKeys "{TAB 3}", True
SendKeys "6", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "eer1", True
SendKeys edate, True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{TAB 2}", True
SendKeys bname, True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{F4}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "enter", True
SendKeys "{TAB 2}", True
SendKeys "^v", True
SendKeys "{TAB}", True
SendKeys "10", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "1", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{TAB 20}", True
SendKeys jno, True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{F4}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "check", True
SendKeys "{TAB 3}", True
SendKeys "10", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "1", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "{TAB 20}", True
SendKeys jno, True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:01"))


Next counter1


End Sub

Display More

as you will see, theres alot of waiting going on there (almost 19 seconds in this example, which will run loop on average 150-500 times).
Im wondering, is there a way to use half a second timevalue to wait, or even better, wait on the app becoming ready?
if youve not used an emulator then its hard to explain, but when the app is busy theres a "X timer" thing at the bottom.
heres a link of someone who had a similar problem but with a differnt emulator and better explains my problem:

http://www.tek-tips.com/viewthread.cfm?qid=1088478&page=1

Thank you for your time
Jamie

**Ger Plante** · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

At a guess, with reference to "X" I would say your sending commands to an AS/400 (iseries) screen in another window.

I would advise against the sendkeys approach as you wont know / wont be able to control when the AS/400 is finished its last task (when the X is gone).

Depending on what your trying to do, it would be managable to set up your own direct connection and use API's to control the AS/400's behaviour. Having said that I have never done it myself and the API route can be fickle.

Ger

dodger7 · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

Hi Ger

Thanks for your reply. However in the above link I posted, the guy managed to set up a function to wait on the "X" to go away, i.e the app to become ready. Heres the code he used:

Code

Do while MyScn.OIA.Xstatus <> 0 
   Doevents
Loop

If I could do this for my emulator and not the "myscn" then I think it should work. Alternatively, waiting half a second and not the full second would work as the "X" takes a split second to go away. It has never once failed using the 1 second method

Cheers

**Ger Plante** · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

I'm possibly going to shoot myself in the foot here, but I dont think you can wait for anything less than 1 second.

The reason for this is that the WAIT method is waiting for a "time". A legitimate time, that can be evaluated as a time. And as far as I can see, "Time" can not be measured in fractions of a second... which seems so odd... I'm not comfortable saying it

Ger

**Andy Pope** · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

Maybe you could use the SLEEP API which does milliseconds.

I would also put the AppActivate command inside the loop to make sure the window has focus.

dodger7 · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

Hi Andy

Thanks for your reply. However im not familiar with APIs - if you get a chance yould you post me an example?

Thanks
Jamie

PS thanks for the tip - there was a few times when it lost focus and started typing in lotus notes etc!

**Andy Pope** · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

forum search revealed
http://www.ozgrid.com/forum/sh…t=55505&do=filter&fid=750

dodger7 · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

ive done a searh on APIs and came up with this:

Code

Private Declare Sub sapiSleep Lib "kernel32" _
        Alias "Sleep" _
        (ByVal dwMilliseconds As Long)


Sub sSleep(lngMilliSec As Long)
    If lngMilliSec > 0 Then
        Call sapiSleep(lngMilliSec)
    End If
End Sub


Sub iwait()
Const cTIME = 500 'in MilliSeconds
    Call sSleep(cTIME)
End Sub






Sub test()


AppActivate "Session A - tn3270", True


SendKeys "IB01 dis", True
SendKeys "{ENTER}", True
Call iwait
SendKeys "{TAB 2}", True
SendKeys "K1289231000", True
SendKeys "{TAB 2}", True
SendKeys "1", True
SendKeys "{ENTER}", True
Call iwait
SendKeys "{ENTER}", True
etc...


End Sub

Display More

seems to do the job!
Thanks for all input, appreciated

**Andy Pope** · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

Maybe a little simpler.

[vba]Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()

AppActivate "Session A - tn3270", True

SendKeys "IB01 dis", True
SendKeys "{ENTER}", True
Sleep 500
SendKeys "{TAB 2}", True
SendKeys "K1289231000", True
SendKeys "{TAB 2}", True
SendKeys "1", True
SendKeys "{ENTER}", True
Sleep 500
SendKeys "{ENTER}", True
etc...

End Sub
[/vba]

dodger7 · Sep 6th 2007

Re: Linking Vba With Terminal Emulator

cheers Andy

makes sense

rich527 · Jan 22nd 2013

Re: Linking Vba With Terminal Emulator

Hi Dodger7,

i know this post is older but still is it okay if i can ask for the sample code? i tried you code but i've got an error in activating the window for tn3270.

thanks hope you can help me on this.

cytop · Jan 22nd 2013

Re: Link Vba To Terminal Emulator

If you got an error activating the terminal window then check the parameter you're using for the window title. It must exactly match the caption of the terminal window.

Regardless, please do not post questions in threads started by other members. Start your own thread, give it an accurate and concise title that describes your issue and explain your problem fully. If you think this thread can help clarify your issue, you can include a link to this thread by copying the URL from the address bar of your browser and pasting into your message.

Link Vba To Terminal Emulator

Participate now!

Share

Users Viewing This Thread