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

Trouble getting Macro to Loop 1

Status
Not open for further replies.
link99sbc

link99sbc

Technical User
Apr 8, 2009
141
US
I'm trying to get this macro to loop a specified
number of times. I tried some examples in Excel help
but none worked. I have over 40,000 rows on the
spreadsheet. I want it to loop 25 or 50 or maybe 100
times starting on the ActiveCell.

This code works fine (1 row at a time)

Sub Check()


ActiveCell.Offset(1, 0).Activate
Selection.Copy

Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System")
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
Set Field = System.ActiveSession.Screen.Area(3, 8, 3, 16)
Set Field1 = System.ActiveSession.Screen.Area(4, 75, 4, 78)
Set Field2 = System.ActiveSession.Screen.Area(4, 75, 4, 78)
Set Field3 = System.ActiveSession.Screen.Area(4, 75, 4, 78)
Set Field4 = System.ActiveSession.Screen.Area(4, 75, 4, 78)
Field.Select
Field.Delete
Field.Value = ActiveCell

SendKeys "%{TAB}"
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.MoveTo 3, 24
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("081")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
ActiveCell.Offset(0, 1) = Field1.Value

Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.MoveTo 3, 24
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("082")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
ActiveCell.Offset(0, 2) = Field2.Value

Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.MoveTo 3, 24
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("083")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
ActiveCell.Offset(0, 3) = Field3.Value

Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.MoveTo 3, 24
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("084")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Do While Sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
ActiveCell.Offset(0, 4) = Field4.Value




End Sub
 
Sort by date Sort by votes



Step thru the code: F8

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I stepped through it and found the problem

Do Until oScrn.WaitForCursor(3, 24)
DoEvents
Loop

It was doing a continuous loop here
because after enter is pressed the home
position of the cursor is 3,8 not 3,24.

After I changed it - works great. I'm Learning!
Thanks so much.
 
Upvote 0 Downvote


Wonderful!

Sorry for the blind alleys.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Next problem is the list is compiled from a data base.
The account numbers are 7 digits
1234567
The field this number goes in on attachmate
is actually 2 fields
123 4567
The only way this macro works is if I seperate
the numbers first with a space.
Is there an easy way to do this since I have
over 40,000 rows????
123 4567
123 4568
123 4569


And suppose I want to put the data from attachmate
5 columns over from the active cell
would I change this line
For iCol = 5 To 8
oScrn.PutString "08" & iCol, 3, 24 '081-084
oScrn.MoveRelative 1, 1, 1

 
Upvote 0 Downvote
Oh it would be
ActiveCell.Offset(5Off, iCol) = _
oScrn.Area(4, 75, 4, 78).Value

Correct?
 
Upvote 0 Downvote



Code: 
            oScrn.PutString Left(ActiveCell.Offset(lOff, 0).Value,3), 3, 8  'acctnbr
            oScrn.PutString Right(ActiveCell.Offset(lOff, 0).Value,4), 3, [b]12[/b]  'acctnbr
I guessed at column 12 for the right 4.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
It's putting the first 3 but not the last four
 
Upvote 0 Downvote
I know why
There are 6 or 7 spaces after the 7 digits
so the line is putting spaces in the last
4 on attachmate.
 
Upvote 0 Downvote



what column does the last 4 go in?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote



What is the VALUE of

Right(ActiveCell.Offset(lOff, 0).Value,4)

???

Have you stepped thru the code to observe the values at every step?

YOU ought to be able to determine where the problem is occuring. Its called 'debugging!'

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I did the step through with no errors
Since there are 7 spaces after the last digit
I changed this line
oScrn.PutString Right(ActiveCell.Offset(lOff, 0).Value,11), 3, 12 'acctnbr
from 4 to 11
Works fine now.

YOU DA MAN!

Thanks again!
 
Upvote 0 Downvote
What would I change if I want to add a fifth column
to include the account name (3,34,3,63)

I've been playing with for 2 hours with no luck.
 
Upvote 0 Downvote


Code: 
    If lRowCount > 0 Then
        Do While lOff <= lRowCount
            oScrn.PutString ActiveCell.Offset(lOff, 0).Value, 3, 8  'acctnbr
            For iCol = 1 To 4
                oScrn.PutString "08" & iCol, 3, 24            '081-084
                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<Enter>")
                Do Until oScrn.WaitForCursor(3, 24)
                    DoEvents
                Loop
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(4, 75, 4, 78).Value
            Next[b]
            ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(3,34,3,63).Value
            [/b]lOff = lOff + 1
        Loop
    End If



Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I believe I tried that Friday. The problem was it kept overwriting on the same row. I thought maybe it had someting to do with the moverelative. The activecell doesn't seem to be moving. Maybe I'm confused. I'll try again Monday. I know when I entered it one time I got an error on "1off" but maybe I didn't do it right. I'll let you know tomorrow.
 
Upvote 0 Downvote


Yes, the activecell does NOT move.

Rather the lOff variable references the proper ROW OFFSET relative to the ActiveCell.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Must be an excel glitch. I entered again just like I did friday and got error on 1Off just like friday. "Unexpected end statement"
So I copied it all to notepad, entered it there then copied it all back to the module and overwrote it. Now it works fine. Thanks again for your time.

For a learning process is there a way to make my original macro at the top of this post loop in a similar way?
 
Upvote 0 Downvote


For a learning process is there a way to make my original macro at the top of this post loop in a similar way?
Click to expand...
Huh? Isn't that what we did? I do not understand your question.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I mean't the way it's written, can it be looped.

As far as your code:
I guess i'm not grasping what some of the lines of code do.
I was trying to expand it to get 081-084 and 091-094.
but everything I try just locks up the excel file.
Maybe if I had another example I could understand it better.
081-084 and 091-094 would require 8 columns
 
Upvote 0 Downvote



For 081-084 and 091-094...
Code: 
    If lRowCount > 0 Then
        Do While lOff <= lRowCount
            oScrn.PutString ActiveCell.Offset(lOff, 0).Value, 3, 8  'acctnbr
            For iCol = 1 To [b]8 
              select case iCol
                case <= 4
                   oScrn.PutString "08" & iCol, 3, 24
                case else
                   oScrn.PutString "09" & iCol, 3, 24
              end select[/b]
                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<Enter>")
                Do Until oScrn.WaitForCursor(3, 24)
                    DoEvents
                Loop
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(4, 75, 4, 78).Value
            Next
            ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(3,34,3,63).Value
            lOff = lOff + 1
        Loop
    End If

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Mike.and.H
  • Locked
  • Question
Syntax problem in AttachMate macro working with MS Word
Replies
4
Views
255
Mike.and.H
Mike.and.H
ej2rms13
  • Locked
  • Question
Do Until Loop Syntax
Replies
10
Views
368
SkipVought
S
nyrtfleury14
  • Locked
  • Question
Attachmate Extra macro moving to Reflection
Replies
1
Views
233
SkipVought
S
imanoob24
  • Locked
  • Helpful tip
Need help with my codes- extra! macro
Replies
0
Views
136
imanoob24
imanoob24
Remyaram
  • Locked
  • Question
VB6 to open extra sessions
Replies
11
Views
360
Remyaram
Remyaram

Part and Inventory Search

Sponsor

Back
Top