Possibly 2 easy questions...

[Crash171]

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?

'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
            
            
[highlight #8AE234]           Sess0.Screen.Select 16,19,16,31
           Sess0.Screen.Copy
           xlApp.BRange("B2").Paste[/highlight]
'--------------------------------------------------------------------------------
'        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

 

[SkipVought]

Hi,

You have the Excel sheet row from your loop.

So...

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.

 

[SkipVought]

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.

 

[Crash171]

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:

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!!!

 

[SkipVought]

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

 

[Crash171]

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?

 

[SkipVought]

Sorry I left part of it out...

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

 

[Crash171]

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?

 

[SkipVought]

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.

 

[Crash171]

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.

 

[SkipVought]

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

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