Assistance with Excel VBA code to post entries in Attachment

[irvingamo]

I am new to writing excel macros. I am currently working with code that I have used from another project. Where I'm encountering a problem, is the screen in Attachmate where the entries are to be keyed will only allow 10 lines then the PF8 key need to be pressed in order for a new screen to appear. After the PF8 key is pressed, i need to loop but start copying from excel where it left off and not key the same 10 entries again. Help:-( The code i am attempting to use is below.

Do While Worksheets(WkshtName).Cells(Counter, 1).Value <> ""
Screen.SendKeys Worksheets(WkshtName).Cells(Counter, 1).Value
Screen.SendKeys Worksheets(WkshtName).Cells(Counter, 2).Value
Screen.SendKeys Worksheets(WkshtName).Cells(Counter, 3).Value
Screen.SendKeys "<tab>"
Screen.SendKeys Worksheets(WkshtName).Cells(Counter, 4).Value
Screen.SendKeys Worksheets(WkshtName).Cells(Counter, 5).Value
Screen.SendKeys Format(Worksheets(WkshtName).Cells(Counter, 6).Value, "0.00")
Screen.SendKeys "<tab>"


Counter = Counter + 1
LoopCounter = LoopCounter + 1

If LoopCounter = 10 Or Worksheets(WkshtName).Cells(Counter, 1).Value = "" Then
LoopCounter = 0

If (Screen.WaitForString(20, 6, 14)) = "ACTION" Then Exit Do
Screen.SendKeys ("<PF8>")
Screen.WaitHostQuiet (1000)
Do While (Screen.WaitForCursor(9, 12))
Loop

Else
AppActivate ("Microsoft Excel")
MsgBox "Error"
End If

 

[SkipVought]

hi,

I would suggest that you establish the screen control logic loop. You must know ALL the messages that a transaction can return and what each messagge means with respect to what pf key or other key to send.

After you get that logic solid, then add the logic for reading data from Excel and populating the screen. This loop will execute within the control structure loop that you will have established previously.

The other major consideration regards the fact that the screen reacts asynchronously with your VB code. Waiting a set period of time, is akin to determining that as you drive your car, each time you reach a traffic light, you will wait 10 seconds and then go. In some cases, the light will be green when you reach it, and waiting is unnecessary, or as you wait, the light turns red, at which time you proceed thru the red light. A set wait time is a recipe for loosing data and running slow!

There are several ways to TEST if the control has returned to the screen. The method that I have found the most effective is to move the cursor away from the screen's rest coordinates and then wiat for the cursor to return to the screen's rest coordinates.

with oScreen
    .MoveRelative 1, 1, 1
    .SendKeys ("<enter>")
    Do Until (.WaitForCursor(RestRow, RestCol))
        DoEvents
    Loop
end with

Once you have established your screen control logic, post back with your code and for help loading the screen from Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}