++ Web automation

F

fi.or.jp.de

Hi all,

I have web interactive macro, which logs into the web.

It click serach button and result will be populated in excel but
I got one small problem here if there is no search result it will
pop window saying "no results found", then I have to click OK button
to continue the macro.
how should click ok automatically.

Please help me.

Here's the code i have used ( URL is internal )

Option Explicit
Function web_test ()
Dim ie As InternetExplorer
Dim URL As String
Dim Entid As Object, pwd As Object, form As Object
Dim pwd1 As Object, form1 As Object
Dim Fname As Object
Dim Acn As String, Acs As String
Dim Srch As Object
Dim status As Object, gender As Object, org As Object
Dim clr As Object
Dim I As Integer, Rng As Integer
Dim CID As String
Dim form2 As Object

Acn = ActiveWorkbook.Name
Acs = ActiveSheet.Name

Set ie = CreateObject("internetexplorer.application")

URL = "https://xyz.aspx "
ie.Visible = True

ie.Navigate URL

Do While ie.ReadyState <> 4 Or ie.Busy = True
DoEvents
Loop

Set Entid = ie.Document.getelementsbyname("txtPersonnelNumber")
Set pwd = ie.Document.getelementsbyname("txtPassword")
Set form = ie.Document.getelementbyid("btnLogIn")

Entid.Item(0).Value = "fiorjpde"
pwd.Item(0).Value = "********"
form.Click

Do While ie.ReadyState <> 4 Or ie.Busy = True
DoEvents
Loop

Rng = Workbooks(Acn).Sheets(Acs).Cells(Rows.Count, "A").End(xlUp).Row

For I = 2 To Rng

CID = Trim(Cells(I, "A").Value)

Set Fname = ie.Document.getelementsbyname("txtCID")
Fname.Item(0).Value = CID

Set Srch = ie.Document.getelementbyid("btnSearch")
Srch.Click

Do While ie.ReadyState <> 4 Or ie.Busy = True
DoEvents
Loop

Set status =
ie.Document.getelementbyid("dtgCandidateDefaultSearchResult_ctl03_lbldtgStatus")
Cells(I, "B").Value = status.innertext

Set gender =
ie.Document.getelementbyid("dtgCandidateDefaultSearchResult_ctl03_lblGender")
Cells(I, "C").Value = gender.innertext

Set org =
ie.Document.getelementbyid("dtgCandidateDefaultSearchResult_ctl03_lbldtgPrevOrg")
Cells(I, "d").Value = org.innertext

Set clr = ie.Document.getelementbyid("btnClear")
clr.Click

Do While ie.ReadyState <> 4 Or ie.Busy = True
DoEvents
Loop

Next I

End Function
 
R

ron

Hi all,

I have web interactive macro, which logs into the web.

It click serach button and result will be populated in excel but
I got one small problem here if there is no search result it will
pop window saying "no results found", then I have to click OK button
to continue the macro.
how should click ok automatically.

Please help me.

Here's the code i have used ( URL is internal )

Option Explicit
Function web_test ()
Dim ie As InternetExplorer
Dim URL As String
Dim Entid As Object, pwd As Object, form As Object
Dim pwd1 As Object, form1 As Object
Dim Fname As Object
Dim Acn As String, Acs As String
Dim Srch As Object
Dim status As Object, gender As Object, org As Object
Dim clr As Object
Dim I As Integer, Rng As Integer
Dim CID As String
Dim form2 As Object

Acn = ActiveWorkbook.Name
Acs = ActiveSheet.Name

Set ie = CreateObject("internetexplorer.application")

URL = "https://xyz.aspx"
ie.Visible = True

ie.Navigate URL

Do While ie.ReadyState <> 4 Or ie.Busy = True
   DoEvents
Loop

Set Entid = ie.Document.getelementsbyname("txtPersonnelNumber")
Set pwd = ie.Document.getelementsbyname("txtPassword")
Set form = ie.Document.getelementbyid("btnLogIn")

Entid.Item(0).Value = "fiorjpde"
pwd.Item(0).Value = "********"
form.Click

Do While ie.ReadyState <> 4 Or ie.Busy = True
   DoEvents
Loop

Rng = Workbooks(Acn).Sheets(Acs).Cells(Rows.Count, "A").End(xlUp).Row

For I = 2 To Rng

    CID = Trim(Cells(I, "A").Value)

Set Fname = ie.Document.getelementsbyname("txtCID")
Fname.Item(0).Value = CID

Set Srch = ie.Document.getelementbyid("btnSearch")
Srch.Click

Do While ie.ReadyState <> 4 Or ie.Busy = True
    DoEvents
Loop

Set status =
ie.Document.getelementbyid("dtgCandidateDefaultSearchResult_ctl03_lbldtgSta­tus")
Cells(I, "B").Value = status.innertext

Set gender =
ie.Document.getelementbyid("dtgCandidateDefaultSearchResult_ctl03_lblGender­")
Cells(I, "C").Value = gender.innertext

Set org =
ie.Document.getelementbyid("dtgCandidateDefaultSearchResult_ctl03_lbldtgPre­vOrg")
Cells(I, "d").Value = org.innertext

Set clr = ie.Document.getelementbyid("btnClear")
clr.Click

Do While ie.ReadyState <> 4 Or ie.Busy = True
   DoEvents
Loop

Next I

End Function

It's hard to say exactly what will work when you can't look at the
site, but I had the same problem when I was extracting information
from a website using an Excel macro. Sometimes when I opened the
website a pop-up window would appear, so I needed to find a way to
have the macro click the "OK' button on the pop-up if and when it
appeared. I used the following bit of code to click every button on
the webpage. From this I was able to

rr = ie.Document.all.Length - 1

For yy = 0 To rr
ie.Document.all.Item(yy).click
Next

determine which value of "yy" clicked the pop-up "OK" button. Once I
knew "yy", I was able to determine that

classname = "ui-icon ui-icon-closethick"

for the pop-up "OK" button. I also found that the phrase "Create a
price alert, free" was present in the source code only when the pop-up
window appeared. I then inserted the following code into my macro and
it was able to click the pop-up "OK" button and close the pop-up when
it appeared.

my_var = ie.Document.body.innerhtml

If instr(my_var, "Create a price alert, free", vbtext compare)>1 then
rr = ie.Document.all.Length - 1

For yy = 0 To rr
If ie.Document.all.Item(yy).classname = "ui-icon ui-icon-
closethick" Then
ie.Document.all.Item(yy).Click
End If
Next
else:
end if

Perhaps something similar will work for you...ron
 
F

fi.or.jp.de

thanks for the reply

Its still unresolved


It's hard to say exactly what will work when you can't look at the
site, but I had the same problem when I was extracting information
from a website using an Excel macro.  Sometimes when I opened the
website a pop-up window would appear, so I needed to find a way to
have the macro click the "OK' button on the pop-up if and when it
appeared.  I used the following bit of code to click every button on
the webpage.  From this I was able to

    rr = ie.Document.all.Length - 1

    For yy = 0 To rr
         ie.Document.all.Item(yy).click
    Next

determine which value of "yy" clicked the pop-up "OK" button.  Once I
knew "yy", I was able to determine that

classname = "ui-icon ui-icon-closethick"

for the pop-up "OK" button.  I also found that the phrase "Create a
price alert, free" was present in the source code only when the pop-up
window appeared.  I then inserted the following code into my macro and
it was able to click the pop-up "OK" button and close the pop-up when
it appeared.

my_var =  ie.Document.body.innerhtml

If instr(my_var, "Create a price alert, free", vbtext compare)>1 then
     rr = ie.Document.all.Length - 1

     For yy = 0 To rr
            If ie.Document.all.Item(yy).classname = "ui-icon ui-icon-
closethick" Then
                    ie.Document.all.Item(yy).Click
            End If
     Next
else:
end if

Perhaps something similar will work for you...ron- Hide quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top