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 bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possibly 2 easy questions...

Status
Not open for further replies.
Crash171

Crash171

Programmer
Sep 5, 2014
30
US
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
            
            
[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

 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!!!
 
Upvote 0 Downvote


XlSheet.cells(my range.row, "B").value = sess0.screen.getstring(16, 19, 13)
 
Upvote 0 Downvote
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?


 
Upvote 0 Downvote

Sorry I left part of it out...

XlSheet.cells(my range.row + row - 1, "B").value = sess0.screen.getstring(16, 19, 13)
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Silver180
  • Locked
  • Question
Export 2 bits of data from Extra to Excel 2015
Replies
11
Views
457
SkipVought
S
Biznez
  • Locked
  • Question
Combine 2 statements into one line
Replies
0
Views
177
Biznez
Biznez
smiler44
  • Locked
  • Question
Dim as date, with a few other questions?
Replies
4
Views
205
smiler44
smiler44
makavity
  • Locked
  • Question
VBA Object for Reflection for the Web 2011
Replies
0
Views
173
makavity
makavity
jaynlola
  • Locked
  • Question
Can't get EBM macro to work from Extra sessions to Word 2007 Doc
Replies
6
Views
248
jaynlola
jaynlola

Part and Inventory Search

Sponsor

Back
Top