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!

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

Fetch data from webpage

Status
Not open for further replies.
kalle82

kalle82

Technical User
Apr 2, 2009
163
SE
Hi!

My question is, how can i extract data from a webpage and then show it in my userform, for later use?

I'm using this code down below to generate a search result.

--------------------------------------
Sub getnavetinfo()

Dim numTDs
Dim C

Dim namn2
Dim adress
Dim fastighet


Dim ie As Object
Set ie = CreateObject("internetexplorer.application")

ie.Visible = True

ie.navigate " & TextBox3.Value

Start = 0
StopTime = 20000000
While Start < StopTime
Start = Start + 1
Wend

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

namn2 = (InStr(C, "<TD>Förnamn:</TD>") - 20)
adress = (InStr(C, "<TD>Folkbokföringsadress:</TD>") - 20)
fastighet = (InStr(C, "<TD>Fastighet:</TD>") - 20)

TextBox18.Value = namn2
TextBox19.Value = adress
TextBox20.Value = fastighet
End Sub

-------------------------------------

I just end up with -20 in my textbox...?

What I want to do now is to extract the results.

the source for the search result looks like this:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="Content-Language" content="sv" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />
<title>Personbild</title>
<link rel="stylesheet" type="text/css" href="styles/personsok.css" />
<script language="javascript" type="text/javascript"
src="scripts/personsok.js"></script>
</head>

<body bgcolor="#ffffcc">


<table border="0" cellspacing="0">
<tr>
<td width="1060" rowspan="2" valign="top"><a
href="" target="_blank"><img border="0"
src="images/skvlogo.gif" width="117" height="23"
alt="Skatteverket" /></a><img border="0" src="images/blank.gif"
width="143" height="1" alt="" /></td>
<td width="80" class="nav"><span class="nlsp"
name="bakatsp" id="bakatsp">&raquo;</span>
<a href="personsok.jsp" class="nl"
name="bakat" id="bakat"
onfocus="doFocus('bakat');
return sSt('Klicka för att gå tillbaka till senaste '
+ 'sökningen')"
onblur="doBlur('bakat');
return sSt('')"
onmouseover="doMouseOver('bakat');
return sSt('Klicka för att gå tillbaka till senaste '
+ 'sökningen')"
onmouseout="doMouseOut('bakat');
return sSt('')"
onclick="history.back();
return false;">Bak&aring;t</a></td>
<td width="80" class="nav"><span class="nlsp"
name="nysoksp" id="nysoksp">&raquo;</span>
<a href="personsok.do?nySok=true" class="nl"
name="nysok" id="nysok"
onfocus="doFocus('nysok');
return sSt('Klicka för att göra en ny sökning')"
onblur="doBlur('nysok');
return sSt('')"
onmouseover="doMouseOver('nysok');
return sSt('Klicka för att göra en ny sökning')"
onmouseout="doMouseOut('nysok');
return sSt('')">Ny s&ouml;kning</a></td>
<td width="80" class="nav"><span class="nlsp"
name="hjalpsp" id="hjalpsp">&raquo;</span>
<a href="help/HjalpPersonbild.html"
target="hjalpf" class="nl" name="hjalp" id="hjalp"
onfocus="doFocus('hjalp');
return sSt('Klicka för att visa hjälptexten')"
onblur="doBlur('hjalp');
return sSt('')"
onmouseover="doMouseOver('hjalp');
return sSt('Klicka för att visa hjälptexten')"
onmouseout="doMouseOut('hjalp');
return sSt('')"
onclick="
return visaHjalp('help/HjalpPersonbild.html')">Hj&auml;lp</a></td>
</tr>
<tr>
<td width="80" height="10"><img border="0" src="images/blank.gif"
width="80" height="1" alt="" /></td>
<td width="80" height="10"><img border="0" src="images/blank.gif"
width="80" height="1" alt="" /></td>
<td width="80" height="10"><img border="0" src="images/blank.gif"
width="80" height="1" alt="" /></td>
</tr>
</table>


<h1>Personbild</h1>


<table border="0" cellspacing="0">




<tr>

<td colspan="2" class="upplys">

Senaste ändring för personen i Navet: 2008-09-22 kl. 21:23:21

</td>

</tr>
<tr>
<td width="150" height="10"><img border="0" src="images/blank.gif"
width="150" height="1" alt="" /></td>
<td width="1150" height="10"><img border="0" src="images/blank.gif"
width="350" height="1" alt="" /></td>
</tr>
<tr>
<td class="ledtext">Personnummer/<br />
Samordningsnummer:</td>
<td class="data">

123456-7890    M

</td>
</tr>
<tr>
<td class="ledtext">F&ouml;rnamn:</td>
<td class="data">

Sture berit Apan



</td>
</tr>
<tr>
<td class="ledtext">Mellannamn:</td>
<td class="data">

 



</td>
</tr>
<tr>
<td class="ledtext">Efternamn:</td>
<td class="data">

Johlsson



</td>
</tr>
<tr>
<td class="ledtext">Aviseringsnamn:</td>
<td class="data">

 

</td>
</tr>
<tr>
<td class="ledtext">H&auml;nvisningspersonnr:</td>
<td class="data">

 

</td>
</tr>
<tr>
<td height="10" colspan="2"></td>
</tr>
<tr>
<td class="ledtext"> </td>
<td class="datavik">

Personen är

</td>
</tr>
<tr>
<td class="ledtext">Datum:</td>
<td class="data">

2008-09-22

</td>
</tr>
<tr>
<td height="10" colspan="2"></td>
</tr>
<tr>
<td class="ledtext">Folkbokf&ouml;ring/<br />senaste folkbokf&ouml;ring</td>
<td class="ledtext"> </td>
</tr>
<tr>
<td class="ledtext">     L&auml;n:</td>
<td class="data">

14    Västra Götaland

</td>
</tr>
<tr>
<td class="ledtext">     Kommun:</td>
<td class="data">

70    Skene

</td>
</tr>
<tr>
<td class="ledtext">     F&ouml;rsamling:</td>
<td class="data">

20    Haga

</td>
</tr>
<tr>
<td class="ledtext">     Fastighet:</td>
<td class="data">

BERG 21:6

</td>
</tr>
<tr>
<td height="10" colspan="2"></td>
</tr>
<tr>
<td valign="top" class="ledtext">Folkbokf&ouml;ringsadress:</td>
<td class="data">



Grushålet 6<br />


444 55


Skene


</td>
</tr>
<tr>
<td height="10" colspan="2"></td>
</tr>
<tr>
<td valign="top" class="ledtext">S&auml;rskild postadress:</td>
<td class="data">

 

</td>
</tr>
<tr>
<td height="10" colspan="2"></td>
</tr>
<tr>
<td valign="top" class="ledtext">Utl&auml;ndsk s&auml;rskild
postadress eller adress f&ouml;r utvandrad:</td>
<td valign="top" class="data">

 

</td>
</tr>
<tr>
<td height="10" colspan="2"></td>
</tr>
<tr>
<td class="ledtext">Civilst&aring;nd:</td>
<td class="data">

Ogift

</td>
</tr>
</table>


<table border="0" cellspacing="0">
<tr>
<td width="100" height="20"><img border="0" src="images/blank.gif"
width="100" height="1" alt="" /></td>
<td width="770" height="20"><img border="0" src="images/blank.gif"
width="85" height="1" alt="" /></td>
<td width="180" height="20"><img border="0" src="images/blank.gif"
width="65" height="1" alt="" /></td>
<td width="105" height="20"><img border="0" src="images/blank.gif"
width="105" height="1" alt="" /></td>
<td width="105" height="20"><img border="0" src="images/blank.gif"
width="105" height="1" alt="" /></td>
<td width="40" height="20"><img border="0" src="images/blank.gif"
width="40" height="1" alt="" /></td>
</tr>
<tr>
<td colspan="6" class="upplys">Relationer</td>
</tr>



<tr>
<td height="10" colspan="5"></td>
</tr>
<tr>
<td valign="top" class="ledtext">Personnummer</td>
<td valign="top" class="ledtext">Namn</td>
<td valign="top" class="ledtext">Relationstyp</td>
<td valign="top" class="ledtext">V&aring;rdnad from</td>
<td valign="top" class="ledtext">Avliden</td>
</tr>
<tr>
<td height="10" colspan="5"></td>
</tr>






<tr>
<td valign="top" class="data">



<a href="personsok.do?idPersSelect="
onmouseover="return sSt('Klicka för att gå till personbilden')"
onmouseout="return sSt('')">

</a>


</td>

<td valign="top" class="data">




</td>
<td valign="top" class="data">

Moder

</td>
<td valign="top" class="data">

 

</td>
<td valign="top" class="data">

 

</td>
</tr>





<tr>
<td height="5" colspan="5"></td>
</tr>




<tr>
<td valign="top" class="data">



<a href="personsok.do?idPersSelect="
onmouseover="return sSt('Klicka för att gå till personbilden')"
onmouseout="return sSt('')">
521209-2331
</a>


</td>

<td valign="top" class="data">




</td>
<td valign="top" class="data">

Fader

</td>
<td valign="top" class="data">

 

</td>
<td valign="top" class="data">

 

</td>
</tr>







</table>




I ahve tried
 
Sort by date Sort by votes
kalle82,
The result of [tt](InStr(C, "<TD>Förnamn:</TD>") - 20)[/tt] is -20 because [tt]<TD>Förnamn:</TD>[/tt] Doesn't exist in the HTML source. [tt]Instr()[/tt] requires an exact match so [tt](InStr(C, "<td class=""ledtext"">F&ouml;rnamn:</td>") - 20)[/tt] should give you the result your looking for.

You may find it easier to parse the data you want by utilizing the TABLE structure. An example can be found in this thread
Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote



What application is your VBA in?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I solved everything using this code!

Sub getnavetinfo()

Dim namnet
Dim startnamn
Dim slutnamn

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")


ie.Visible = False

ie.Navigate " & TextBox3.Value

start = 0
StopTime = 20000000
While start < StopTime
start = start + 1
Wend

numTDs = ie.Document.getElementsByTagName("td").Length
C = ie.Document.body.innerHTML

startnamn = InStr(1, C, "rnamn:") + 28

slutnamn = InStr(1, C, "Mellannamn:") - 37

namnet = Mid(C, startnamn, slutnamn - startnamn + 1)

Thanks again!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
843
remeng
remeng
Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
790
strongm
strongm
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
653
strongm
strongm
monyi
  • Locked
  • Question
Extracting Data from XML Node
Replies
0
Views
244
monyi
monyi
remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
527
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top