Extra Macro Help

[ptw78]

Trying to make another macro with and excel file.
Col A in excel will have days of the month(I'm using june) the days will start on row 3 and end at row 33. Col B in excel will be blank. In extra at 4,25 I will need to put the date from Col A, then move to 6,12 and put an "F" in there, hit enter, then move to 11,19 in Extra. I will need to get the data in 11,19 and put that in Col C in excel, starting at row 3 and ending at row 33. I need it to loop through for all the days. Then I will need to do the same thing instead of putting an "F" at 6,12 in extra I will need to put an "A" and get the data, put that in Col E in excel and loop through all the dates and get the data in 11,19. Here is what I have, but it's way off.

Sub Main
    
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "E:\Macros\MthlyReferralsTotal.xls"
        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file
        '---------------------------------
        'assumption
        'data begins in row 3, column a,c,d,e,f
        'where column a is date
        'column c,d,e,f are the names
        '----------------------------------
begrw = 3  
endrw = 33       
col = 3   
cola= 2            
        with obj.worksheets("June '09")
        
        for x = begrw to endrw      'obj.ActiveSheet.Rows.Count    'this will navigate
                                                    'column a with the dates
                                                    
        for y = col to 5                            'this will navigate
                                                    'column c,d,e,f
        MyDat = .cells(x,1)
        If MyDat = "" Then Exit Sub
        
           

        
        '-----send data to Extra-------
        
           
        Sess0.Screen.PutString MyDat,4,25     
        Sess0.Screen.Moveto 6,12
        Sess0.Screen.Sendkeys "F"      
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1
                
               
                
                'wait for response
               Do
                  DoEvents
                Loop Until Sess0.Screen.WaitForCursor(4, 25)
            '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (11,19,7) 'area getting data from
        
        
        
                
                
        Sess0.Screen.PutString MyDat,4,25     
        Sess0.Screen.Moveto 6,12
        Sess0.Screen.Sendkeys "A"      
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1       
                
                       Do
                  DoEvents
                Loop Until Sess0.Screen.WaitForCursor(4, 25)     
                   
        '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (11,19,7) 'area getting data from
                                                    
        
        '-----and place data in Excel-------
  
'cola = cola + 1  
        .cells(x,col) = ExtraDat  'this places the information in the same sheet
                                   'column c,d,e,f,g
        '----------------------------
        
        next y  'next column
cola=2         'this brings the data back to column c,d,e,f
               'for data input
        next x  'next row
 
msgbox "Macro Done"       
        
        end with


End Sub

 

[vzachin]

here is one way.
step through it to see if this works

Sub Main
    
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "C:\test.xls"
        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file
        '---------------------------------

begrw = 3
endrw = 33

   with obj.worksheets("ptw070109") 
   
    for x = begrw to endrw  'or use obj.ActiveSheet.Rows.Count
                            'instead of endrw    
    
    Dat = .range("a" & x)    'dates are in  column "A", 
                             'where "x" represents the row
    
    If Dat = "" Then 
    msgbox "Macro Done" 
    Exit Sub : end if
    
    sess0.screen.putstring Dat,4,25    
    sess0.screen.putstring "F",6,12
    Sess0.Screen.MoveRelative 1, 1
    Sess0.Screen.Sendkeys("<enter>")
    Do
    DoEvents
    Loop Until Sess0.Screen.WaitForCursor(4, 25)
    
    FDat = Sess0.Screen.GetString (11,19,7)  'this is data for column "C"
    
    sess0.screen.putstring Dat,4,25  
    sess0.screen.putstring "A",6,12
    Sess0.Screen.MoveRelative 1, 1
    Sess0.Screen.Sendkeys("<enter>")
    Do
    DoEvents
    Loop Until Sess0.Screen.WaitForCursor(4, 25)
    
    ADat = Sess0.Screen.GetString (11,19,7)   'this is data for column "E"
    
    msgbox FDat   'this is for informational
    msgbox ADat   'purposes only
    
    .range("c" & x) = FDat   '  "c" is column C
    .range("e" & x) = ADat   '  "e" is column E
    
    next x
        
    end with
    msgbox "Macro Done"             
End Sub