VBA Code - running From Access to Extra, If..Then not working

[NWildblood]

Hi, I am currently running some work in Attachmate Extra from some VBA in an Access module. It works fine except for the If Then actions required dependendent on various Extra screen messages. The code in question is below the line:

'actions if messages

I'm sure it's just my If... Then arguments, but cannot get the VB to Enter "Y" where required, once or any further times - can anyone see an obvious oversight in my code ??? Many thanks

________________________________________________________

Sub CreateJobs() 'to run from the sdhmatches spreadsheet produced by the cert engine

Dim oDB As Database 'ACCESS CODE
Dim oRS As DAO.Recordset 'ACCESS CODE
Dim vSomeVal
Dim System As Object, Sessions As Object, Sess As Object, CSS As Object
Dim msg_line As String



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 CSS = GetObject("C:\Documents and Settings\zeicri\Desktop\SMNXS06.edp")
Set Sess = System.ActiveSession
Set oDB = OpenDatabase("S:\CommonARUK01\FinAccouUK01\Fixed Asset\SDH\SDH CERTIFICATIONS\CERTIFICATION DATABASE") 'ACCESS CODE
Set oRS = oDB.OpenRecordset("BP INFORMATION") 'ACCESS CODE

If (Sess Is Nothing) Then
MsgBox "Could not create the Session object. Stopping macro playback."
Stop
End If

If Not Sess.Visible Then Sess.Visible = True


msg_line = Trim(Sess.Screen.GetString(24, 1, Sess.Screen.Cols))


Sess.Screen.SendKeys ("<Home>AJOB<Enter>")
Call Wait(Sess)

If Not oRS.BOF Then
oRS.MoveFirst
Do While Not oRS.EOF

'create job between here

Sess.Screen.SendKeys ("HQJOB<NewLine><Tab>IPT") 'HQJOB
Sess.Screen.SendKeys Trim(oRS.Fields("New Project").Value) 'PROJECT
Sess.Screen.SendKeys ("<NewLine>MX")
Sess.Screen.SendKeys Trim(oRS.Fields("New COW").Value) 'COW - TAB REQUIRED IF NOT 5 CHAR
Sess.Screen.SendKeys ("<Tab>")
Sess.Screen.SendKeys Trim(oRS.Fields("New APC").Value) 'APC
Sess.Screen.SendKeys ("<NewLine><NewLine>")
Sess.Screen.SendKeys Trim(oRS.Fields("New Job Title").Value) 'TITLE
Sess.Screen.SendKeys ("<NewLine>")
Sess.Screen.SendKeys Trim(oRS.Fields("New Job Title").Value) 'DESC
Sess.Screen.SendKeys ("<NewLine>")
Sess.Screen.SendKeys Trim(oRS.Fields("Start Date").Value)
Sess.Screen.SendKeys Trim(oRS.Fields("End Date").Value)
Sess.Screen.SendKeys Trim(oRS.Fields("Req date").Value)
Sess.Screen.SendKeys ("<Delete><EraseEOF><Tab><Delete><EraseEOF>")
Sess.Screen.SendKeys Trim(oRS.Fields("Locn A").Value) '1141
Sess.Screen.SendKeys ("<NewLine><NewLine>")
Sess.Screen.SendKeys Trim(oRS.Fields("Planners EIN").Value) 'EIN
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)

'and here


'actions if messages
If InStr(1, UCase(msg_line), UCase("TW007 - Required by Date less than End Date")) > 0 Then
Sess.Screen.PutString "y", 21, 78
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
End If
Call Wait(Sess)

If InStr(1, UCase(msg_line), UCase("TW015 - Start Date is less than Current Date; Confirm Y/N")) > 0 Then
Sess.Screen.PutString "y", 21, 78
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
End If
Call Wait(Sess)

If InStr(1, UCase(msg_line), UCase("TW021 COW is applicable to both PFC & PROACT; Confirm Y/N")) > 0 Then
Sess.Screen.PutString "y", 21, 78
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
End If
Call Wait(Sess)

If InStr(1, UCase(msg_line), UCase("TW034 - Creating Job for HQ , confirm Y/N")) > 0 Then
Sess.Screen.PutString "y", 21, 78
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
End If
Call Wait(Sess)



'get the job number
oRS.Edit
oRS.Fields("New Job").Value = Trim(Sess.Screen.GetString(22, 30, 6))
oRS.Update
Call Wait(Sess)


oRS.MoveNext


' Sess.Screen.SendKeys ("<Pf9>") 'TO OBSERVE SNAFU ORIGIN
' Call Wait(Sess)


Loop
End If





MsgBox "DONE", vbOKOnly, "End of Batch"
End Sub
________________________________________________
Private Sub Wait(Sess As Object)
Do While Sess.Screen.OIA.XStatus <> 0
DoEvents
Loop
End Sub



"No-one got everything done by Friday except Robinson Crusoe...

 

[SkipVought]

Hi,

Why not...

       If Trim(UCase(msg_line)) =  UCase("TW007 - Required by Date less than End Date") Then
         Sess.Screen.PutString "y", 21, 78
         Sess.Screen.SendKeys ("<Enter>")
         Call Wait(Sess)
       End If

Assuming that msg_line really has the entire string. Break and use the Watch Window to be absolutely certain.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[NWildblood]

Thanks Skip, that's simpler, and you are right the full msg text is

W007 - Required by Date less than End Date; Confirm Y/N

Still not working though. I suspect you might be right about capturing the entire string, however, being a non-coder (bodger) not sure how to use Break and / Watch Window to be absolutely certain... are there any pointers on this no doubt simple VB method ? Many thanks.

"No-one got everything done by Friday except Robinson Crusoe...

 

[SkipVought]

faq707-4594

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[NWildblood]

Thanks SkipV, will check this oput later, appreciate your replies.

"No-one got everything done by Friday except Robinson Crusoe...

 

[NWildblood]

Skip
Thanks, I think you're right about string length, but I have used a simple work around (see below) for now due to time constraints but will definitely coming back to Watch Window tool; thanks for the heads-up.

If Trim(UCase(msg_line)) <> 0 Then

Rgds
OS

"No-one got everything done by Friday except Robinson Crusoe...

 

[SkipVought]

???

 If Trim(UCase(msg_line)) <> 0 Then

That won't work!!!

Trim(UCase(msg_line)) is TEXT!!!

Did you mean...

 If [b]Len[/b](Trim(UCase(msg_line))) <> 0 Then

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[JoeAtWork]

If Trim(UCase(msg_line)) <> 0 Then

In addition to Skip's observation, I can't help but wonder what the point of the UCase is here?

 

[SkipVought]

UCase, like a useless appendage in the evolution of this thread... ;-)

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[NWildblood]

Skip, indeed sloppy of me, thanks.
BTW UCase inherited from my first thread1-1426335, indeed redundant...



"No-one got everything done by Friday except Robinson Crusoe...