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?
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?
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 
Talk To Other Members
RE: Possibly 2 easy questions...
You have the Excel sheet row from your loop.
So...
CODE
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...
RE: Possibly 2 easy questions...
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...
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...
RE: Possibly 2 easy questions...
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...
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...
You can grab or assign from 1 character to the entire screen and anything between
CODE