Multiple Variable PutString from Excel to Attachmate

[tps14334]

I took this code (from

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

and tried expanding on it to do a-bit more. Here is the code I currently have sans a majority of the variables:

        Const g_HostSettleTime as integer = 1

        Global System As Object,Sess0 As Object,Sessions As Object,Screen As Object

        Dim X as integer,Y as integer,Z as integer,Y1 as integer,Y2 as integer,Y3 as integer,Y4 as integer
        Dim Line1 as string,Line2 as string,Line3 as string,Line4 as string

Sub Trap_Alert()
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Do
        If InStr(Sess0.Screen.GetString (1,1,70),"ATTENTION") = 0 Then Exit Sub
        Sess0.Screen.Sendkeys("<Enter>"):Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
        Loop
End Sub

Sub Main()
' Get the main system object
    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 the default wait timeout value
    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If
' Get the necessary Session Object
    Set Sess0 = System.ActiveSession
    If (Sess0 is Nothing) Then
        Msgbox "Could not create the Session object.  Stopping macro playback."
        STOP
    End If
    If Not Sess0.Visible Then Sess0.Visible = TRUE
        Set Screen = Sess0.Screen
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
    
'--------------------------------------------------------------------------------
'Declare the Excel Object
        Dim xlApp As Object, xlSheet As Object, Row As Long
        Dim Status As Object, Action As Object, HouseKey As Object, AddrLn1 As Object
                        
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="File Location"
        Set xlSheet = xlApp.activesheet
        Set Status = xlApp.activesheet.Range("A:A")
        Set Action = xlApp.activesheet.Range("B:B")
        Set HouseKey = xlApp.activesheet.Range("C:C")
        Set AddrLn1 = xlApp.activesheet.Range("D:D")

        With xlApp.ActiveSheet
        
           Set Status = .Range("A4:A65536").Resize(xlApp.CountA(.Range("A4:A65536")))
           Set Action = .Range("B4:B65536").Resize(xlApp.CountB(.Range("B4:B65536")))
           Set HouseKey = .Range("C4:C65536").Resize(xlApp.CountC(.Range("C4:C65536")))
           Set AddrLn1 = .Range("D4:D65536").Resize(xlApp.CountD(.Range("D4:D65536")))
                           
        End With        
        
        
        '*** Action Time ***
        For Row = 1 To Status.Rows.Count
        
           Sess0.Screen.SendKeys("<CLEAR>"):Trap_Alert
           Sess0.Screen.SendKeys("<HOME>" + "HIU A"):Trap_Alert
           Sess0.Screen.SendKeys "<ENTER>":Trap_Alert
           
           Sess0.Screen.PutString ("7000"),2,47:Trap_Alert
           Sess0.Screen.PutString AddrLn1.Rows(Row).Value, 4, 7:Trap_Alert
                      
           xlSheet.Cells(row + 3,48).value = Sess0.Screen.GetString(1,7,14):Trap_Alert
                     
           If time > #10:00:00 AM# and time <#4:00:00 PM# then
           Pause (10)
       End if
           
        Next Row
 
End Sub

I am trying to take a lot columns from Excel and make them dynamic/tangible variables to use in EXTRA! to interact with my billing system. When trying to run the code the file is correctly opened in Excel with a active window but stops running with a error prompt like this:

EXTRA! Basic Error
Microsoft Office Excel
Line number: 74
Stopping macro playback.

I hope that makes sense and that someone can help shed some light on this problem.

Thank you

 

[tps14334]

So I've spent a little more time with this trying to figure out how to get it to stage each column as different variables. For some reason I was thinking that the script was running faster than the applications were performing and put in some delays but that failed with the same error (as above). I get the feeling the script cannot juggle multiple variables from the same instance/session, but have no idea how to test that let alone resolve it.

Still seeking help from anyone who can offer input.

Thanks!

 

[tps14334]

A little more poking and brain stretching has now gotten this script, at least this part of it, working. Needed to drop the resize feature/class/command/???? on additional variables for columns other than my control.

 

[Chotor]

I would try to debug the code (for example, make MsgBox ("code ok so far") every now and then) to find out exactly where your code fails.

I'm guessing you're using a call to an Excel function incorrectly/unsupported method.

 

[CompuDork]

why dont you use some arrays to store your data