Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help required in Web to Excel

  Asked By: Willie    Date: Sep 21    Category: MS Office    Views: 596
  

I need a help from you all. I am trying to develop a macro where in , I search
for a particular text in web site.

For Ex:
In my excel sheet, in first three rows, A1, A2 and A3 I have
www.yahoo.com
www.rediff.com
www.google.com

I have to find whether the text "mail" exists in the three web sites mentioned
above by using VBA.

I have tried writing the code, but its only working fine for the value present
in the first row and for the other rows, its giving the result same as the first
row.

considering the above Ex. in www.google.com, i dont find any text called
"mail". but it shows as it exists. at the same time I am trying to copy the
result in B column.

Can some one help me in this pleaseeeeee.

I have copied the code below , which I tried, but I was not successful.

Sub Main()
Dim objIE As Object
Dim strWebSite As String
Dim intReadyState As Integer
Dim order As String
Dim n, a,total As Integer
n = 1
total = Worksheets("sheet1").UsedRange.Rows.Count
Set objIE = CreateObject("InternetExplorer.Application")

While n <= total
strwebsite = Worksheets("sheet1").Range("a" & n).Value


objIE.Visible = 1
objIE.Navigate strWebSite

Do While Not intReadyState = 4
intReadyState = objIE.ReadyState
Loop

If InStr(objIE.document.body.innerhtml, "mail") = 0 Then
Worksheets("sheet1").Range("b" & n).Value = "not exists"

Else
'MsgBox "Text Found"
Worksheets("sheet1").Range("b" & n).Value = "exists”
End If

n = n + 1

Wend

End Sub

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Essie Garza     Answered On: Sep 21

Cool possibilities here. I found some things:
1. intReadyState has to be reset for each web  site
2. wait an additional 1 sec after intReadyState = 4
3. use LCase to make it not case sensitive


Sub Main()
Dim objIE As Object
Dim strWebSite As String
Dim intReadyState As Integer
Dim order  As String
Dim n, a, total  As Integer
n = 1
total = Worksheets("sheet1").UsedRange.Rows.Count
Set objIE = CreateObject("InternetExplorer.Application")
While n <= total
strWebSite = Worksheets("sheet1").Range("a" & n).Value
objIE.Visible = 1
objIE.Navigate strWebSite
intReadyState = 0
Do While Not intReadyState = 4
intReadyState = objIE.ReadyState
Loop
'wait an additional 1 second
PauseTime = 1 'seconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

DocText = objIE.Document.body.innerhtml
DocText = LCase(DocText)
'Call SaveDoc(DocText, "DocText" & n & ".txt")
If InStr(DocText, "mail") = 0 Then
Worksheets("sheet1").Range("b" & n).Value = "not
exists"
Else
Worksheets("sheet1").Range("b" & n).Value = "exists"
End If
n = n + 1
Wend
Set objIE = Nothing
End Sub

Sub SaveDoc(TheDoc, fName)
Path = Application.ThisWorkbook.Path & "\" & fName
fn = FreeFile()
Open Path For Output As fn
Print #fn, TheDoc;
Close #fn
End Sub

 
Answer #2    Answered By: Eleanor Hunt     Answered On: Sep 21

Really those are the cool possibilities. Its working  very much fine  now, with
appropriate result. Thanks a lot for this support.

 
Answer #3    Answered By: Nahal Malik     Answered On: Sep 21

How about this variation of what you have:

Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
sFind = "mail"
For Each oCell In Range("A:A")
If oCell.Value = "" Then Exit For
With objIE
.Navigate oCell.Value
Do Until Not .Busy
DoEvents
Loop
sData = .Document.documentElement.innerHTML
End With
If InStr(sData, sFind) > 0 Then
oCell.Offset(0, 1) = """" & sFind & """ was found!"
Else
oCell.Offset(0, 1) = """" & sFind & """ not found!"
End If
Next oCell
objIE.Quit
Set objIE = Nothing

 
Didn't find what you were looking for? Find more on Help required in Web to Excel Or get search suggestion and latest updates.




Tagged: