PDA

View Full Version : VBA to copy from a web browser



samuelimtech
05-22-2014, 04:26 AM
Hi all first of all thanks for the help.
Basically I have a list of 500 or so links (all in column A sheet1)
what Im after is a piece of code that will open them one at a time, search the page for a specific word ("local authorit", mispelt on purpose) if the word is found then highlight the link cell any colour.
the way I thought best to acheive this is to;
1) create Loop
2) open link
3)select all copy from webpage
4) paste special text to sheet 2
5) search for word in sheet 2, if its found then
6) rename sheet to something else (something variable as theyre may be multiple)
7)create new sheet2
next
8)if not found then
next

Im comfortable with most of that but i have no idea how to copy from a web page.

I appreciate any help or a complete different suggestion if you know of a better way to achieve this.


thanks

Kyle234
05-22-2014, 09:03 AM
Can you post any of the links?

ashleyuk1984
05-22-2014, 11:43 AM
Yes this can be done.

No need to copy the text to excel, you can just simply store the source code and then look through all the innertext tags looking for "local authorit".

Would be handy to see 2 or 3 of the links so that we can code it correctly first time without going back and forward.

You'll probably want something like this.


Sub LocalAuth()
Dim ieDoc As HTMLDocument
Dim IE As Object
Dim LastRow As Long
Dim x As Long

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To LastRow
IE.Navigate Range("A" & x).Value
Do While IE.ReadyState <> 4 Or IE.Busy = True
DoEvents
Loop
Set ieDoc = IE.document
Set tagName = IE.document.getElementsByTagName("p")
For Each Tag In tagName
If InStr(1, Tag.innerText, "local authorit", vbTextCompare) > 0 Then
Range("A" & x).Interior.Color = 65535
Exit For
End If
Next Tag
Next x

End Sub