comparing from excel to screen

[limeg95]

Hi!
i am checking against excel to screen if account matches in screen corresponding value into excel
the below code works fine, but it takes more than half an hour. is it fastest way to do that? thanks in advance


Do
For i = 2 To 3000
Account = Cells(i, "E").Text

MyScreen.MoveTo 4, 15

MyScreen.SendKeys ("F")
MyScreen.SendKeys (" " + Account)
MyScreen.SendKeys ("<Enter>")

MyRw = MyScreen.Row

fBN = Trim(MyScreen.Getstring(MyRw, 7, 5))
LBN = Trim(MyScreen.Getstring(MyRw, 13, 5))



Cells(i, "A") = fbn
Cells(i, "B") = lbn
Next

Loop Until UCase(MyScreen.Getstring(24, 1, 10)) = "***********"
Exit Sub

 

[SkipVought]

Hi,

Unless you need the variables for stuff you'r not showing, there is no need...

    Do
        For i = 2 To 3000
            MyScreen.SendKeys ("F " & Cells(i, "E").Text)
            MyScreen.SendKeys ("<Enter>")
            
            Cells(i, "A") = Trim(MyScreen.Getstring(4, 7, 5))
            Cells(i, "B") = Trim(MyScreen.Getstring(4, 13, 5))
        Next
    
      Loop Until UCase(MyScreen.Getstring(24, 1, 10)) = "***********"
    Exit Sub

Your code leads me to believe you are coding in Excel VBA, and not Extra VB.

Skip,

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

 

[limeg95]

Thanks a lot. but still i need one thing
if i have cell duplicate no need to check in screen but the same value write in excel how to do

 

[SkipVought]

    Dim Acct, PrevAcct
    
    Do
        For i = 2 To 3000
            Acct = Cells(i, "E").Text
            
            If PrevAcct <> Acct Then
                MyScreen.SendKeys ("F " & Cells(i, "E").Text)
                MyScreen.SendKeys ("<Enter>")
                
                Cells(i, "A") = Trim(MyScreen.Getstring(4, 7, 5))
                Cells(i, "B") = Trim(MyScreen.Getstring(4, 13, 5))
            End If
            
            PrevAcct = Acct
        Next
    
      Loop Until UCase(MyScreen.Getstring(24, 1, 10)) = "***********"

Skip,

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

 

[limeg95]

Skip
iam writing the code in excel only. but the screen has 33950 lines
the excel has only 3000 lines. still find is take long time

 

[SkipVought]

but the screen has 33950 lines[/code]

what system's screen are you scraping?

Have you disclosed all the code in your Do Loop?

Do you have any system delay built into your Extra access?

Please answer ALL these questions.

Skip,

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

 

[limeg95]

there is no delay but do loop do i need to mention screen lines each page 6 to 24 and lines 33933. find in screen

 

[limeg95]

insdie the dataset

 

[SkipVought]

Your screen has 24 lines. So how do you get 33,000+ lines? Does not compute!!!

Please include ALL your code.

Skip,

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

 

[limeg95]

skip
again thanks a lot for your valuable time
each page has 24 lines with 4 columns
there are more than 100 pages

 

[SkipVought]

Hence the long run time.

I'd suggest shutting down Excel and reopening or even rebooting, before running.

I have a job the scrapes about 200 screens, each with 1 to 10 pages of 19 rows of data. The process begins, processing each screen in about 3 seconds but when I get to the last screens it takes about 20 seconds. Excel is know for memory leaks.

Skip,

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

 

[limeg95]

skip
i tried today it took 25 minuts. teh screen has 600 pages.
do i get any solution. again thanks a lot for helping me

 

[limeg95]

skip
could you please clarify
each time after find do i go to moveto 4, 15 and enter
becauase the cursor find first row and then it stands there.
need to go 4,15 and fthen find again,

 

[SkipVought]

You refuse to post your code. Therefore, I can be of no help to you.

Skip,

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

 

[limeg95]

skip
i am not refusing
this is the whole code i provided already
Do
For i = 2 To 3000
Account = Cells(i, "E").Text

MyScreen.MoveTo 4, 15

MyScreen.SendKeys ("F")
MyScreen.SendKeys (" " + Account)
MyScreen.SendKeys ("<Enter>")

MyRw = MyScreen.Row

fBN = Trim(MyScreen.Getstring(MyRw, 7, 5))
LBN = Trim(MyScreen.Getstring(MyRw, 13, 5))



Cells(i, "A") = fbn
Cells(i, "B") = lbn
MyScreen.MoveTo 4, 15
MyScreen.Sendkeys("<EOF>") ' clear the position

Next

Loop Until UCase(MyScreen.Getstring(24, 1, 10)) = "***********"
Exit Sub

 

[limeg95]

this is the full code
Application.ScreenUpdating = False
Dim g_HostSettleTimE
Dim Myarea, MyArea1
Dim Myday1
Dim MyRw
Dim Sheet1 As Object
Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set MyScreen = Sess.Screen
g_HostSettleTimE = 1000 ' milliseconds


Sess.Connected = True


If (Sess Is Nothing) Then
MsgBox "Could not create the Sessions collection object. Stopping macro playback."
Stop
End If


Do
For i = 2 To 3000
Account = Cells(i, "E").Text

MyScreen.MoveTo 4, 15

MyScreen.SendKeys ("F")
MyScreen.SendKeys (" " + Account)
MyScreen.SendKeys ("<Enter>")
g_HostSettleTimE
MyRw = MyScreen.Row

fBN = Trim(MyScreen.Getstring(MyRw, 7, 5))
LBN = Trim(MyScreen.Getstring(MyRw, 13, 5))

g_HostSettleTimE

Cells(i, "A") = fbn
Cells(i, "B") = lbn
MyScreen.MoveTo 4, 15
MyScreen.Sendkeys("<EOF>") ' clear the position

Next

Loop Until UCase(MyScreen.Getstring(24, 1, 10)) = "***********"
Exit Sub

 

[limeg95]

Ski]
thanks a lot . there is a time issue, it works excellant. thanks a lot

 

[vzachin]

you can adjust g_HostSettleTimE