×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Macro for putting data in Extra and pulling string out

thread99-1577881

Macro for putting data in Extra and pulling string out

Macro for putting data in Extra and pulling string out

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

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 = "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

RE: Macro for putting data in Extra and pulling string out

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

RE: Macro for putting data in Extra and pulling string out



Hi,

So what's the problem?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

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

RE: Macro for putting data in Extra and pulling string out

(OP)
This is what i've came up with so far

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 = "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

RE: Macro for putting data in Extra and pulling string out



Debug & step to determine what value is ExtraDat.

And try this...

CODE


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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

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

CODE

If MyDat = "" Then Exit Sub
to

CODE

End Sub

RE: Macro for putting data in Extra and pulling string out



don't you want this?

CODE

MyDat = .cells(x,21)
because the data is in colum A???

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

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

RE: Macro for putting data in Extra and pulling string out



There you go!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

(OP)
ok after this

CODE

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?

RE: Macro for putting data in Extra and pulling string out



Area Size?

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

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

RE: Macro for putting data in Extra and pulling string out



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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

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

RE: Macro for putting data in Extra and pulling string out


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

If MyDat = "" Then Exit Sub

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out




You might want to use Exit For.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Macro for putting data in Extra and pulling string out

(OP)
Thx for your help, got it.  

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