Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extracting information from a Webpage using VBA

Status
Not open for further replies.

JensKKK

Technical User
May 8, 2007
119
GB
Here is my first approach with getting information from the internet into my Excel spreadsheet. I have bluntly stolen some code from the Tek-Tip Forum to get started.

My aim is to visit a database in the internet


I know how to retrieve entries from the database, but what I don't is how can I

a) compare text on the webpage
b) how can I copy information from the webpage when I found a match

I am looking for this string "Also known as" which is then followed by some information that is interesting for me.
FLJ23712; FLJ25931; FLJ31060; FLJ42882; FLJ45252; KIAA1048; MGC138170; MGC164568; MGC164570; DKFZp686F03202; DKFZp686K16132

I am looking for the the string "Also known as" and then I would like to write all the content after "Also known as" such as FLJ23712; FLJ25931; FLJ31060; FLJ42882; FLJ45252; KIAA1048; MGC138170; MGC164568; MGC164570; DKFZp686F03202; DKFZp686K16132
into my MS Excel workbook.


"My code so far" Well most of it is copied from Tek-Tip Forum. Can open IE and visit different entries in the database.

For i = 1 To 10
'Remove all the controls since we Don't want the user to
'monkey with it
GeneAccesionNumber = Sheets("Content Information").Cells(6 + i, 14)
With objIE
.AddressBar = False
.StatusBar = False
.MenuBar = False
.Toolbar = 0
'Or leave everything alone and just hide the sucker
.Visible = True
.Navigate " & GeneAccesionNumber

End With

'let IE do it's thing and Settle before we touch it
While objIE.Busy
'Do Nothing
Wend
While objIE.Document.ReadyState <> "complete"
'AgaIn Do Nothing
Wend
Next


I know how to retrieve entries from the database, but what I don't is how can I

a) compare text on the webpage
b) how can I copy information from the webpage when I found a match

Also known as
FLJ23712; FLJ25931; FLJ31060; FLJ42882; FLJ45252; KIAA1048; MGC138170; MGC164568; MGC164570; DKFZp686F03202; DKFZp686K16132

I am looking for the the string "Also known as" and then I would like to write all the content after "Also known as" such as FLJ23712; FLJ25931; FLJ31060; FLJ42882; FLJ45252; KIAA1048; MGC138170; MGC164568; MGC164570; DKFZp686F03202; DKFZp686K16132
into my MS Excel workbook.
 
I managed to get it done myself.

This is the code that goes into the for next loop.



If InStr(1, objIE.document.body.innertext, "Also known as", vbTextCompare) > 0 Then

mystr = Mid(objIE.document.body.innertext, InStr(1, objIE.document.body.innertext, "Also known as", vbTextCompare), 1000)
'msg = MsgBox(mystr)
laenge = InStr(1, mystr, "Summary", vbTextCompare)
If laenge = 0 Then
laenge = InStr(1, mystr, "[Top]", vbTextCompare)
End If
If laenge > 15 Then
mystr = Mid(objIE.document.body.innertext, InStr(1, objIE.document.body.innertext, "Also known as", vbTextCompare) + 14, laenge - 15)
End If
'msg = MsgBox(mystr)
Sheets("sheet1").Cells(20 + i, 1) = GeneAccesionNumber
Sheets("sheet1").Cells(20 + i, 2) = Sheets("Content Information").Cells(6 + i, 13)
Sheets("sheet1").Cells(20 + i, 3) = mystr
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top