Macro for putting data in Extra and pulling string out

[ptw78]

I'm trying to create a macro that puts a number into Extra at location 3,17 , enter is hit, the screen is displayed data. It then moves to location 5,13, pulls that field which is 10 characters long. And puts that field into excel in Column B, starting at Row 2 and going down. The original number that goes into Extra at location 3/17, is from the same excel sheet in Column A starting at row 2 and going down. Here is what I have.

    Sub Main
    
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\Macros - Reports\Tests\Numbers.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 2
        '----------------------------------
begrw = 2  
 
col = 2   
          
        with obj.worksheets("Sheet1")
        
        for x = begrw to endrw      'obj.ActiveSheet.Rows.Count    
                                                    
     
        MyDat = .cells(x,1)
        If MyDat = "" Then Exit Sub

        
        '-----send data to Extra-------
        
        Sess0.Screen.PutString MyDat,3,17       'area data goes into in extra      
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(3,17)
                
        '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (5,13,10) 'area getting data from
                                                    
        
        '-----and place data in Excel-------
  
        .cells(x,col) = ExtraDat  'this places the information in the sheet
                                   
        '----------------------------
        next x

msgbox "Macro Done"       
        
        end with


End Sub

 

[ptw78]

This code opens up the excel workbook, but does not put anything into Extra or pull anything from it.

 

[SkipVought]

Hi,

So what's the problem?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

It opens up the excel workbook, but doesn't pull any of the data from Column A beginning in Row 2 and put that into Extra at location 3,17. I'm wanting it to then take the data once "Enter" is keyed at location 5,13 and put that into the same workbook in Column B starting at Row 2. So all it's doing now is opening the workbook but not doing anything with data.

 

[ptw78]

This is what i've came up with so far

    Sub Main
    
Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\Macros - Reports\Tests\InvestorLoanNum.xls"
        Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open file
        '---------------------------------
        'data begins in row 2
        '----------------------------------
begrw = 2  
col = 1   
colb = 2         
        with obj.worksheets("Sheet1")
        
        for x = begrw to obj.ActiveSheet.Rows.Count    
                                                    
     
        MyDat = .cells(x,2)
        If MyDat = "" Then Exit Sub

        
        '-----send data to Extra-------
        
        Sess0.Screen.PutString MyDat,3,17       'area data goes into in Extra      
        Sess0.Screen.Sendkeys("<enter>")
        Sess0.Screen.MoveRelative 1, 1
                
                'wait for response
                Do
                    DoEvents
                Loop Until Sess0.Screen.WaitForCursor(3,17)
                
        '-----grab data from Attachmate-----
        ExtraDat = Sess0.Screen.GetString (5,13,10) 'area getting data from
                                                    
        
        '-----and place data in Excel-------
  
        .cells(x,colb) = ExtraDat  'this places the information in the sheet
                                   
        '----------------------------
        next x

msgbox "Macro Done"       
        
        end with


End Sub

 

[SkipVought]

Debug & step to determine what value is ExtraDat.

And try this...

[b]
        Set objWorkbook = obj.workbooks.open(file)[/b]
        '---------------------------------
        'assumption
        'data begins in row 2
        '----------------------------------
begrw = 2  
 
col = 2   
         [b] 
        with objWorkbook.worksheets("Sheet1")[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

OK here's what happens when I step in.
It goes straight from

If MyDat = "" Then Exit Sub

to

End Sub

 

[SkipVought]

don't you want this?

MyDat = .cells(x,[s]2[/s][b]1[/b])

because the data is in colum A???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

The data begins in Column A cell 2, and it will put the data it pulls from Extra in Column B cell 2. So I guess what ever the code would be for those two things.

 

[SkipVought]

There you go!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

ok after this

Sess0.Screen.PutString MyDat,3,17

what would I put into clear the remaining line of that field if there was old data left in there?

 

[SkipVought]

Area Size?

Sess0.Screen.Area(3,17,3,21).Value = ""

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

Can't you use EraseEOF? I've seen that before just dont know how to use it. But at most it's only going to be like 5 spaces that would need to be cleared of old data

 

[SkipVought]

I don't make it a practice to SELECT in anything.

I reference objects using properties & methods.

There are many ways to skin a cat, however. Whatever works for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

Any reason why the message box doesn't pop up? I can't seem to figure out why it doesn't.

 

[SkipVought]

Any reason why the message box doesn't pop up?

If MyDat = "" Then Exit Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You might want to use Exit For.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

Thx for your help, got it.