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