How do you get Extra to Excel

[kamfl610]

Okay, here's the problem. I'm creating a macro in excel to screen script data into Extra. No Problem there. Problem is now, i might get an error message when I hit enter because the data isn't correct, etc. How do I capture that message (I've tried the GetString thing and I don't think it's working) and write it to my excel spreadsheeet that I have open. Basically I want to write the error messages in the last column of my spreadsheet to let the user know that it didn't process. thanks for the help!

 

[IknowMe]

What is the error message and where does it show up on your extra screen?

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[kamfl610]

The message on my screen shows up in positions 1,2. I've tried this in my code:

strerror = MyScn.GetString(1, 2, 80)
strmessage = Msgbox("This is the error: " & strerror, vbokonly)

and i get absolutely nothing. Also, since it's in that state, I can't get the Excel vba macro to stop. I have to manually close out of Extra to get teh macro to stop. Sigh.

 

[kamfl610]

I noticed this thread: thread1-365999

It basically has the same question I'm asking here. Has anyone figured how to copy and paste Extra data to Excel cell?

 

[SkipVought]

Hi,

It's not really copy 'n' paste.

Workbooks("MyBook.xls").Worksheets("MySheet").Range("MyCell").value = oScreen.Area(2,2,2,10).Value

Skip,

_[red][/red]

 

[kamfl610]

Thanks Skip for replying but it still doesn't work. Here's the scenario. I have Excel vba running my Extra (Attachmate 6.4) commands. If I have an custom error (business logic errors) in Extra, a message gets put up on the screen at the top. Example:

TR20 - Error Message - Org code not numeric

When let the code hit <Enter>, the above error message is popped up. I wrote the below code to capture it:

Move2 represents a WaitforCursorMove position. Doesn't reach that position for the next screen, I know something's wrong.

If move2 Then
excelvalue = Range("A1").Offset(9, 6).Value
MyScn.PutString excelvalue
Else
MyScn.Select 1, 2, 1, 80
MyScn.Copy
Range("A1").Offset(9, 34).Select
ActiveSheet.Paste
End If

I want to read it on my Extra Screen, then have the code turn around and go to my excel spreadsheet and put that error message in one of my cells. My problem is that i'm stuck in that extra session and I cannot figure out how to get my code to turn to my excel session and then return back to Extra. Any ideas?

 

[SkipVought]

Does this work?

    If move2 Then
        excelvalue = Range("A1").Offset(9, 6).Value
        MyScn.PutString excelvalue
    Else
        Range("A1").Offset(9, 34).Value = MyScn.Area( 1, 2, 1, 80).Value
    End If

Skip,

_[red][/red]

 

[vzachin]

try this

Msg = Sess0.Screen.WaitForString("TR20", 1, 2, 4)
If Msg Then
ErrorMsg = Sess0.Screen.GetString(1, 2, 43)
Range("G1").Value = ErrorMsg


where "G1" is where you want the data to appear in excel

hth
vzach

 

[kamfl610]

Thanks you all but still nothing. If anyone has any great ideas, please let me know.

 

[SkipVought]

You say NOTHING.

What statements are actually being executed?

Can you step thru this bit of code?

Exactly where is the problem occurring?

Skip,

_[red][/red]

 

[kamfl610]

Thanks Skip for all your help. I'll give you a star for just trying. This is what worked. I was executing the vba code in my worksheet that had all the data. What I had to do was put the vba code in another sheet and open my original excel spreadsheet to actually get the error message copied and pasted into it. So I have an excel workbook called Execute TR Types and the original with all the TR20 transactions. I execute the code inside of TR types. Look at code below:

Public Sub Allotments20()
Dim introwcount As Integer
Dim x As Integer
Dim excelvalue As String
Set System = New ExtraSystem

' Declare variables to contain the OLE objects
Dim objExcel As Object
Dim objWorkBook As Object
Dim objChart As Object

On Error Resume Next

' Attempt to get a reference to an open instance of Excel 97
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
'If GetObject failed, open a new instance of Excel 97
Set objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
MsgBox ("Could not open Excel.")
Exit Sub
End If
End If

' Make Excel visible on the screen
objExcel.Visible = True

' Create a new Workbook
Set objWorkBook = objExcel.Workbooks.Open("C:\Documents and Settings\minter-kyong\My Documents\Screen Scripting Sample.xls")

If objWorkBook Is Nothing Then
MsgBox ("Could not open a new Excel workbook.")
objExcel.Quit
Exit Sub
End If

intRowCnt = Range("A1").CurrentRegion.ROWS.Count

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 Sess0 = System.Sessions.Open("FLAIR.EDP")
Sess0.Visible = True
'Set Sess0 = System.ActiveSession
Dim move
Dim move2
Dim strL2L5 As String
Dim fieldtest
Dim strmessage
Dim result


Set MyScn = Sess0.Screen
move = MyScn.WaitForCursorMove(3)
Application.Wait (Now + TimeValue("0:00:01"))
excelvalue = Range("A1").Offset(0, 1).Value
MyScn.PutString excelvalue
MyScn.SendKeys ("<Enter>")
move2 = MyScn.WaitForCursorMove(12)
Application.Wait (Now + TimeValue("0:00:01"))
excelvalue = Range("A1").Offset(1, 1).Value
MyScn.PutString excelvalue
Sess0.Screen.MoveTo 17, 36
excelvalue = Range("A1").Offset(2, 1).Value
MyScn.PutString excelvalue
MyScn.SendKeys ("<Enter>")
move = MyScn.WaitForCursorMove(6)
Application.Wait (Now + TimeValue("0:00:01"))
MyScn.PutString "1"
MyScn.SendKeys ("<Enter>")
move2 = MyScn.WaitForCursorMove(-20)
Application.Wait (Now + TimeValue("0:00:01"))
MyScn.SendKeys ("<Enter>")
move = MyScn.WaitForCursorMove(3)
Application.Wait (Now + TimeValue("0:00:01"))
excelvalue = Range("A1").Offset(3, 1).Value
MyScn.PutString excelvalue
Sess0.Screen.MoveTo 6, 18
excelvalue = Range("A1").Offset(4, 1).Value
MyScn.PutString excelvalue
Sess0.Screen.MoveTo 6, 30
excelvalue = Range("A1").Offset(5, 1).Value
MyScn.PutString excelvalue
MyScn.SendKeys ("<Enter>")
move2 = MyScn.WaitForCursorMove(16)
Application.Wait (Now + TimeValue("0:00:01"))
MyScn.PutString "20"
Sess0.Screen.MoveTo 22, 80
MyScn.PutString "S"
MyScn.SendKeys ("<Enter>")
move = MyScn.WaitForCursorMove(-16)
Application.Wait (Now + TimeValue("0:00:01"))
excelvalue = Range("A1").Offset(9, 1).Value
strL2L5 = L2L5(excelvalue)
MyScn.PutString L2
Sess0.Screen.MoveTo 6, 8
MyScn.PutString L3
Sess0.Screen.MoveTo 6, 11
MyScn.PutString L4
Sess0.Screen.MoveTo 6, 14
MyScn.PutString L5
Sess0.Screen.MoveTo 6, 18
excelvalue = Range("A1").Offset(9, 2).Value
MyScn.PutString excelvalue
excelvalue = Range("A1").Offset(9, 3).Value
fieldtest = Value(excelvalue, 6, 21)
excelvalue = Range("A1").Offset(9, 4).Value
fieldtest = Value(excelvalue, 6, 24)
excelvalue = Range("A1").Offset(9, 5).Value
fieldtest = Value(excelvalue, 6, 31)
MyScn.SendKeys ("<Enter>")
move2 = MyScn.WaitForString("TR20S", 1, 2)
Application.Wait (Now + TimeValue("0:00:01"))
***This is the part I was having trouble with but this works now. But like I said, I had to move the vba code to another workbook for it to work.
If move2 Then
Sess0.Screen.MoveTo 1, 2
result = Sess0.Screen.GetString(1, 2, 78)
objWorkBook.Worksheets("TR20").Cells(10, 35).Value = result
Else
excelvalue = Range("A1").Offset(9, 6).Value
MyScn.PutString excelvalue
End If


'Set Sessions = Nothing
'Set System = Nothing
'Set Sess0 = Nothing
'Set MyScn = Nothing

End Sub

Thanks for the help!

 

[IknowMe]

You could shorten some of this up for e.x.

excelvalue = Range("A1").Offset(1, 1).Value
MyScn.PutString excelvalue
Sess0.Screen.MoveTo 17, 36
excelvalue = Range("A1").Offset(2, 1).Value
MyScn.PutString excelvalue
MyScn.SendKeys ("<Enter>")

is the same as

MyScn.PutString Range("A1").Offset(1, 1).Value
MyScn.PutString Range("A1").Offset(2, 1).Value, 17, 36
MyScn.SendKeys ("<Enter>")



Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[SkipVought]

Thanks for posting all your code, but that still does not answer the question...

What statements are actually being executed?

Can you step thru this bit of code?

Exactly where is the problem occurring? Is it statement...

If move2 Then

Skip,

_[red][/red]