×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Extra Macro Help

Extra Macro Help

Extra Macro Help

(OP)
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.

CODE

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

RE: Extra Macro Help

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close