Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro for putting data in Extra and pulling string out

Status
Not open for further replies.
ptw78

ptw78

Technical User
Joined
Mar 5, 2009
Messages
155
Location
US
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
 
Sort by date Sort by votes
This code opens up the excel workbook, but does not put anything into Extra or pull anything from it.
 
Upvote 0 Downvote


Hi,

So what's the problem?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote


Debug & step to determine what value is ExtraDat.

And try this...
Code: 
[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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
OK here's what happens when I step in.
It goes straight from
Code: 
If MyDat = "" Then Exit Sub
to
Code: 
End Sub
 
Upvote 0 Downvote


don't you want this?
Code: 
MyDat = .cells(x,[s]2[/s][b]1[/b])
because the data is in colum A???

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote


There you go!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote


Area Size?
Code: 
Sess0.Screen.Area(3,17,3,21).Value = ""


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote


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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Any reason why the message box doesn't pop up? I can't seem to figure out why it doesn't.
 
Upvote 0 Downvote

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

If MyDat = "" Then Exit Sub

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote



You might want to use Exit For.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thx for your help, got it.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FreCard
  • Locked
  • Question Question
Extra macro example
Replies
3
Views
885
SkipVought
S
S
  • Locked
  • Question Question
Reflection Workspace extract all screen/pages
Replies
0
Views
957
SSS27
S
CADD6953
  • Locked
  • Question Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
2K
SkipVought
S
Mike.and.H
  • Locked
  • Question Question
How to display date in EXTRA! X-treme window status bar?
Replies
0
Views
500
Mike.and.H
Mike.and.H
CADD6953
  • Locked
  • Question Question
Macro for Excel to Attachmate and Back Again
2
Replies
34
Views
3K
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top