Extracting information from a Webpage using VBA

[JensKKK]

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

http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?db=gene&cmd=Rtrieve&dopt=Graphics&list_uids=22848

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 "

http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?db=gene&cmd=Retrieve&dopt=Graphics&list_uids="

& 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.

 

[JensKKK]

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