×
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

Possibly 2 easy questions...

Possibly 2 easy questions...

Possibly 2 easy questions...

(OP)
My goal is to have this macro take the first item in Column A from excel (which is in A2), put it into Attachmate, run a few "sendkeys", then return a selection or text from Attachmate into Column B (and one into Column C as well) on the same row. Then it should move onto the next item in Column A.

So far I have been able to open Excel and send the first item into Attachmate (from Column A). The loop seems to work as well. Most of this code I was able to create from searching this site.

My questions are....

1. How do I designate column B and then C as part of the macro?
Perhaps I create each one as a named object?? The same way I did "MyRange"?


2. How do I move the text from Attachmate to Excel? I would prefer to do this without using the clipboard so that I can do other work while this runs. Is that possible?



CODE -->

'Declare the Excel Object
        Dim xlApp As Object, xlSheet As Object, MyRange As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="C:\DATA.xlsx"
        Set xlSheet = xlApp.ActiveSheet
        Set myRange = xlApp.ActiveSheet.Range("A:A")
  
Dim Row As Long
        With xlApp.ActiveSheet
           Set myRange = .Range("A2:A2000").Resize(xlApp.CountA(.Range("A2:A2000")))
        End With
        For Row = 1 To myRange.Rows.Count
           Sess0.Screen.Moveto 24,08
           Sess0.Screen.SendKeys ("leatt<tab>e")
           Sess0.Screen.PutString myRange.Rows(Row).Value, 24, 28
           Sess0.Screen.SendKeys ("<ENTER>")
           Do Until Sess0.Screen.WaitForCursor (05,67)
              DoEvents
            Loop
           Sess0.Screen.MoveTo 13,03
           Sess0.Screen.Sendkeys("x<ENTER>")
           Do Until Sess0.Screen.WaitForCursor (13,19)
              DoEvents
            Loop
           Sess0.Screen.PutString MyRange.Rows(Row).Value, 13, 19
           Sess0.Screen.Sendkeys("x<ENTER>")
           Do Until Sess0.Screen.WaitForCursor (13,19)
              DoEvents
            Loop
            
            
           Sess0.Screen.Select 16,19,16,31
           Sess0.Screen.Copy
           xlApp.BRange("B2").Paste
'--------------------------------------------------------------------------------
'        PASTE REJECT CODES INTO COLUMN B
'--------------------------------------------------------------------------------

           Sess0.Screen.Select 07,69,07,78

'--------------------------------------------------------------------------------
'        PASTE Data Date INTO COLUMN C
'--------------------------------------------------------------------------------
        Next Row
        

        
        
'--------------------------------------------------------------------------------
' Below kills the Excel process
'--------------------------------------------------------------------------------
        
        xlApp.Quit
        Set xlApp = Nothing 
	System.TimeoutValue = OldSystemTimeout 
End Sub 

RE: Possibly 2 easy questions...

Hi,

You have the Excel sheet row from your loop.

So...

CODE

XlSheet.cells(row, "B").value = sess0.screen.getstring(16, 19, 13)
XlSheet.cells(row, 3).value = Sess0.screen.getstring(7, 69, 10) 

Notice I used 2 different notations for the column in the Cells() object. Personally I woul not used COPY n PASTE. Try using GetString() as a method in your screen, or the Area() object.

RE: Possibly 2 easy questions...

I'm glad you're using the WaitForCursor loop method rather than waiting an arbitrary length of time before driving away at the intersection without any regard to cross traffic that must pass before being able to proceed safely. Notice how well I mixed my metaphors.

RE: Possibly 2 easy questions...

(OP)
That worked, it did move the text into row 1 (cells b1 and c1), and not row 2, which is what row A starts on they (I have headers in the spreadsheet)

For Column A I set the range like this:

CODE -->

Set myRange = .Range("A2:A2000").Resize(xlApp.CountA(.Range("A2:A2000"))) 

Do I need to do something like this for Columns B and C? Or perhaps modify that line of code?


Oh and I learned the WaitForCursor loop from you!!!!! So thanks again for that!!!

RE: Possibly 2 easy questions...



XlSheet.cells(my range.row, "B").value = sess0.screen.getstring(16, 19, 13)

RE: Possibly 2 easy questions...

(OP)
Oh! I understand that! Neat!! Thanks for your help yet again!


So this leads me to one more question....

Would you mind giving me short an "Area() object" example or explanation? Do is mark where a letter in the desired text is, then the area will end at the next bank space?


RE: Possibly 2 easy questions...


Sorry I left part of it out...

XlSheet.cells(my range.row + row - 1, "B").value = sess0.screen.getstring(16, 19, 13)

RE: Possibly 2 easy questions...

(OP)
Alright, that worked. If you don't mind I would like to understand what we just did. Why the "row+row-1"? What did that do?

RE: Possibly 2 easy questions...

You set myrange as a range object in Your Excel sheet.

The row property of any range is the first row of the range.

Your row variable (I'd suggest to not use the word 'row' as a variable as it is like a reserve word--I'd use lRow to indicate that it is ace pared As Long) loops from 1 to your myrange row count.

Hence the - 1.

RE: Possibly 2 easy questions...

(OP)
Oh! I understand that! Thanks again for all of your help!

I'll take sometime today to change the name of "row", that may make it simpler for myself going forward.

RE: Possibly 2 easy questions...

The Area object has 4 arguments: row1, col1, row2, col2

You can grab or assign from 1 character to the entire screen and anything between

CODE

oScrn.Area(3,3,3,3) '1 character @ row3, col 3
oScrn.Area(1,1,24,80) 'entire 24 x 80 screen 

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