Excel to Extra! Question 1

[Losman500]

I'm stuck and reading other related posts is just confusing me more.

I have a spreadsheet with 3 columns (ID | P/N | QTY) with a variable number of rows. My Extra! screen can take up to 4 records per screen for example:

1st record ID (row 10 col 3), P/N (row 10 col 8), QTY (row 11 col 10)
2nd record is 4 screen rows down. ID (row 14 col 3, P/N (row 14 col 8). Qty (row 15 col 10)
3rd record is 4 screen rows down. ...etc

I want the macro to go down the first 4 rows in Excel and put the corresponding column data in the appropriate Extra fields when it gets to the 4th record then sendkeys (<Enter>) to ready the extra screen for the next 4 records and continue with the next 4 excel rows until the cells are empty

I don't have any code so far other defining the appropriate objects.

PS I'm using Excel to write the Macro.

 

[SkipVought]

Hi,

Please post the code that you do have and we'll go from there.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Assuming that your data is...
[tt]
1. on Sheet1,
2. starting in A1
3. one row of heading
4. all data is contiguous
[/tt]
Your Excel loop may look something like this. Check the Extra Screen Object to conform with your code...

dim r as range, iRow as integer

with Sheets("Sheet1")
  irow = 10
  for each r in .Range(.Cells(.[A2], .[A2].end(xldown))
     oScrn.PutString r.value, irow, 3
     oScrn.PutString r.Offset(0,1).value, irow, 8
     oScrn.PutString r.Offset(0,2).value, irow+1, 10

     irow = irow+4

     if irow < 18 then irow=10
  next
End with

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Assuming that your data is...
[tt]
1. on Sheet1,
2. starting in A1
3. one row of heading
4. all data is contiguous
[/tt]
Your Excel loop may look something like this. Check the Extra Screen Object to conform with your code...

dim r as range, iRow as integer

with Sheets("Sheet1")
  irow = 10
  for each r in .Range(.Cells(.[A2], .[A2].end(xldown))
     oScrn.PutString r.value, irow, 3
     oScrn.PutString r.Offset(0,1).value, irow, 8
     oScrn.PutString r.Offset(0,2).value, irow+1, 10

     irow = irow+4
[b]
     if irow > 18 then 
        irow=10
        oScrn.Sendkeys<"YourProcessKey">
     end if[/b]
  next
End with

and my test was going the wrong way.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Losman500]

Thanks Skip,

This is what I have so far (working only for 1 column for now) and by no means finished. And yes your assumptions are correct.

I will give your code a shot, and again Thanks.

Sub FACTSQuote()

 Dim Session As Object
 Dim Sess0 As Object
 Dim System As Object
 Set System = CreateObject("Extra.system")
 If (System Is Nothing) Then
 MsgBox "Could not Create Session"
 Stop
 End If
 Set Session = System.Sessions
 Set Sess0 = System.Activesession
 If UCase(Sess0.Screen.GetString(1, 2, 4)) = "FEBM" And UCase(Sess0.Screen.GetString(1, 7, 1)) > "" Then
    Dim ObjExcel As Object
    Dim MyRange As Object
    Dim ActiveWorkbook As Object
    Dim xlSheet As Object
    
    
    Application.Visible = True
    Application.DisplayAlerts = False 'Turn off Warning Messages
    Set xlSheet = Application.ActiveWorkbook.ActiveSheet
    Set MyRange = Application.ActiveWorkbook.ActiveSheet.Range("B:B")
    Dim Row As Long
    Dim Lrow As Long
    'Sess0.WindowState = xMINIMIZED
    Lrow = 10
    For Row = 2 To MyRange.Rows.Count
    Sess0.Screen.moveto 10, 8
    Sess0.Screen.PutString MyRange.Rows(Row).Value, Lrow, 8
    Lrow = Lrow + 4
    Next Row
    
    Else
    MsgBox "YOU MUST BE IN FEBM WITH A VALID P/N TO RUN MACRO"
    End If
    End Sub

 

[Losman500]

Almost there, I'm getting a Syntax Error on:

for each r in .Range(.Cells(.[A2], .[A2].end(xldown))

Here is my updated code to with the right Extra objects:

Sub FACTSQuote()

 Dim Session As Object
 Dim Sess0 As Object
 Dim System As Object
 Set System = CreateObject("Extra.system")
 If (System Is Nothing) Then
 MsgBox "Could not Create Session"
 Stop
 End If
 Set Session = System.Sessions
 Set Sess0 = System.Activesession
 If UCase(Sess0.Screen.GetString(1, 2, 4)) = "FEBM" And UCase(Sess0.Screen.GetString(1, 7, 1)) > "" Then
    Dim xlSheet As Object
    Dim r As Range, iRow As Integer
    Set xlSheet = Application.ActiveWorkbook.ActiveSheet

With xlSheet
  iRow = 10
  for each r in .Range(.Cells(.[A2], .[A2].end(xldown))
     Sess0.PutString r.Value, iRow, 3
     Sess0.PutString r.Offset(0, 1).Value, iRow, 8
     Sess0.PutString r.Offset(0, 2).Value, iRow + 1, 10

     iRow = iRow + 4

     If iRow > 18 Then
        iRow = 10
        Sess0.SendKeys ("<enter>")
     End If
  Next
End With
    Else
    MsgBox "YOU MUST BE IN FEBM WITH A VALID P/N TO RUN MACRO"
    End If
    End Sub

 

[SkipVought]

My notes in COMMENTS...

'    Dim ObjExcel As Object
    Dim MyRange As Object
'    Dim ActiveWorkbook As Object
    Dim xlSheet As Object
    Dim Row As Long
    Dim Lrow As Integer
    
    Application.Visible = True
    Application.DisplayAlerts = False 'Turn off Warning Messages
    
    Set xlSheet = ActiveSheet
'    Set MyRange = xlSheet.Range("B:B")  that is not your actual range
    Set MyRange = xlSheet.Range(xlSheet.[B2], xlSheet.[B2].End(xlDown))
'assuming that this is a contiguous range of values
    
    'Sess0.WindowState = xMINIMIZED
    Lrow = 10
    For Row = 1 To MyRange.Rows.Count '"your Row" is with respect to MyRange, not the Sheet
'        Sess0.Screen.MoveTo 10, 8
        Sess0.Screen.PutString MyRange.Cells(Row, "B").Value, Lrow, 3
        Sess0.Screen.PutString MyRange.Cells(Row, "C").Value, Lrow, 8
        Sess0.Screen.PutString MyRange.Cells(Row, "D").Value, Lrow + 1, 10
        Lrow = Lrow + 4
        
        If Lrow > 18 Then
            Sess0.Screen.SendKeys "<Enter>"
            Lrow = 10
        End If
    Next Row

You don't need to MOVE the cursor on the screen, because you are Putting the data where you want to.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Losman500]

Yeah that's what happens when you steal code from other posts without fully understanding it. I cleaned it up but I now get a Runtime Error 91 / Object Variable or With Variable not Set

I'm coding in Excel if that matters...code below:

Sub FACTSQuote()
 Dim Session As Object
 Dim Sess0 As Object
 Dim System As Object
 Set System = CreateObject("Extra.system")
 If (System Is Nothing) Then
 MsgBox "Could not Create Session"
 Stop
 End If
 Set Session = System.Sessions
 Set Sess0 = System.Activesession
 If Sess0.Screen.GetString(1, 2, 4) = "FEBM" Then

    Dim MyRange As Object
    Dim xlSheet As Object
    Dim Row As Long
    Dim Lrow As Integer
    
    Application.Visible = True
    Application.DisplayAlerts = False 'Turn off Warning Messages
    
    Set xlSheet = ActiveSheet
    Set MyRange = xlSheet.Range(xlSheet.[A2], xlSheet.[A2].End(xlDown))
'assuming that this is a contiguous range of values
    
    Lrow = 10
    For Row = 1 To MyRange.Rows.Count '"your Row" is with respect to MyRange, not the Sheet
        Sess0.Screen.PutString MyRange.Cells(Row, "B").Value, Lrow, 3
        Sess0.Screen.PutString MyRange.Cells(Row, "C").Value, Lrow, 8
        Sess0.Screen.PutString MyRange.Cells(Row, "D").Value, Lrow + 1, 10
        Lrow = Lrow + 4
        
        If Lrow > 18 Then
            Sess0.Screen.SendKeys "<Enter>"
            Lrow = 10
        End If
    Next Row
    
    Else
     MsgBox "YOU MUST BE IN FEBM WITH A VALID P/N TO RUN MACRO"
    End If
    End Sub

 

[SkipVought]

Error on WHAT statement?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Losman500]

Error on

 If Sess0.Screen.GetString(1, 2, 4) = "FEBM" Then

If I Remove that IF statement then the same error will occur on

Sess0.Screen.PutString MyRange.Cells(Row, "B").Value, Lrow, 3

I guess it has something to do with the Sess0 object

 

[SkipVought]

Tweeked a few things and got this to write to my TN3270 Extra emulator....

Sub FACTSQuote()
    Dim MyRange As Range
    Dim xlSheet As Worksheet
    Dim Row As Long
    Dim Lrow As Integer
    Dim System As ExtraSystem
    Dim Sess0 As ExtraSession
    Dim oScrn As ExtraScreen
    
    Set System = CreateObject("Extra.system")
    
    If (System Is Nothing) Then
        MsgBox "Could not Create Session"
        Stop
    End If
    
    Set Sess0 = System.Activesession
    Set oScrn = Sess0.Screen
    
    If oScrn.GetString(1, 2, 4) = "FEBM" Then
        
        Application.Visible = True
        Application.DisplayAlerts = False 'Turn off Warning Messages
        
        Set xlSheet = ActiveSheet
        Set MyRange = xlSheet.Range(xlSheet.[A2], xlSheet.[A2].End(xlDown))
    'assuming that this is a contiguous range of values
        
        Lrow = 10
        For Row = 1 To MyRange.Rows.Count '"your Row" is with respect to MyRange, not the Sheet
            oScrn.PutString MyRange.Cells(Row, "A").Value, Lrow, 3
            oScrn.PutString MyRange.Cells(Row, "B").Value, Lrow, 8
            oScrn.PutString MyRange.Cells(Row, "C").Value, Lrow + 1, 10
            Lrow = Lrow + 4
            
            If Lrow > 22 Then
                oScrn.SendKeys "<Enter>"
                Lrow = 10
            End If
        Next Row
        
    Else
     MsgBox "YOU MUST BE IN FEBM WITH A VALID P/N TO RUN MACRO"
    End If
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Losman500]

It didn't like that either. Error: User Defined type not identified.

Weird because I have other macros that write to Extra running from Excel 2003 with the same Object statements from before and they work.

I don't want you to go crazy. You pointed me to the right direction with the loop and I will keep hacking at it. If I get it to work I will post my finding.

Thanks again Skip.

 

[SkipVought]

Do you have a reference in Tools > References to an Attachmate Extra! Object Library?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's my Excel data
[tt]
ColA ColB ColC
a1 b1 c1
a2 b2 c2
a3 b3 c3
a4 b4 c4
[/tt]
Then here's my screen, starting in row 10 before the ENTER
[tt]
a1 b1
c1


a2 b2
c2


a3 b3
c3


a4 b4
c4

[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Losman500]

Funny you mention that, on my other macros that work from excel I've never have to reference the Extra! Object Library.

On this case I did reference it and still got the same error.

Your excel data and screen mapping sample is exactly what I need. If you go back to my second post with my original code (the one with all the unnecessary object statements) that worked; although it was pulling only from the B column. So I'm going to use that as my starting point along with your updated code and post my findings.

 

[Losman500]

I just tried running another excel macro that always worked and I got the same error. I think it had to do with me running 2 sessions while I was testing the your macro.

I restarted my machine and now it runs fine. Still needs a few tweaks but no errors

I thank you Skip for you patience and knowledge and have another star for you collection