Import data from web page into excel

[rik]

Hi there,
i know this issue has been discussed several times but still i can't solve
my problem. I'm trying to get stock data (renewed every few seconds) into my
excell sheet. I use visual basic in excel 2003.
1. it works using a webquery BUT the data i pick up are not the last shown
on the website (streaming data), it looks like there is a train of data on
the website and the program picks the one that was "actual" 1 minute ago,
not the one shown at that very moment.
2. it works manually with copy and paste, BUT i need the last shown number
at the end of EVERY minute, so this is not an option.
3. i tried to use "sendkeys" (alt tab/copy/alt tab/paste = what i do
manually - see above) but both internet explorer and excell get stuck, only
way to get away is ctrlaltdel ... Even with "DoEvents" added, it won't work.

any ideas please ?

 

[Joel]

You are probably selecting the wrong table at the website. Go back and
perform the webquery manually and make sure you have selected the correct
table on the website. if this doesn't work there are methods of opening an
Internet explorer from VBA and gettting the data through IE commands. if you
are having problems post the Website and I will take a look.

 

[rik]

Hi Joel,
sorry, but i cannot post the site, it's my brooker, access with password
only ...
I tested with the simpliest thing to do and that doesn't even work :
open internet explorer and go to www.google.com
select whatever part of text
open excel,write and run the following :
Sub test()
Sheets("Blad1").Select
SendKeys "a", True
DoEvents
SendKeys "{ENTER}", True
DoEvents
SendKeys "%{TAB}", True
DoEvents
SendKeys "^C", True
DoEvents
SendKeys "%{TAB}", True
DoEvents
SendKeys "^V", True
End Sub

It will block explorer and excel ...
What can i do ?

 

[Joel]

Create a worksheet called DEALERS and run this code to see an example of
calling an Internet explorer will do.

Sub GetDealers()
'Dim PageNumber As Object
CR = Chr(13)
LF = Chr(10)

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

URL = "http://www.nissanusa.com/apps/dealerlocator"
Request = "?zipCode=07508&tool=Home.Locator"

'get web page
IE.Navigate2 URL & Request
Do While IE.readyState <> 4
DoEvents
Loop

'get search button
Set but = IE.document.getElementById("mainSearchButton")
'put distance in listbox on webpage
Set radius = IE.document.getElementById("radius")
radius.Value = "100"


'search again a larger distance
'Select Search button and activate
but.Select
but.Click
Set SearchResults = IE.document.getElementById("searchResults")

On Error Resume Next ' Defer error handling.
Do
Err.Clear
Set PageNumber = IE.document.getElementById("pageNumber")
Pages = PageNumber.Value
DoEvents
Loop While Err.Number <> 0
On Error GoTo 0


With Sheets("Dealers")
.Cells.ClearContents
RowCount = 1

For PageCount = 1 To PageNumber.Length
PageNumber.Value = Format(PageCount, "@")
PageNumber.onchange

For Each Chld In SearchResults.Children

If Chld.innertext = "" Then
Exit For
End If
Set DealerNumberObj = _
Chld.getelementsbytagname("A")
DealerNumberStr = DealerNumberObj.Item(1).pathname
dealerNumber = _
Val(Mid(DealerNumberStr, InStr(DealerNumberStr, "'") + 1))
.Cells(RowCount, "A") = dealerNumber

ColCount = 2
dealer = Chld.innertext
Do While InStr(dealer, CR) > 0
Data = Trim(Left(dealer, InStr(dealer, CR) - 1))

'remove leading CR and LF
Do While Left(Data, 1) = LF Or _
Left(Data, 1) = CR

Data = Mid(Data, 2)
Loop
dealer = Trim(Mid(dealer, InStr(dealer, CR) + 1))
If InStr(Data, "(") > 0 And _
ColCount = 4 Then

Distance = Trim(Mid(Data, InStr(Data, "(") + 1))
Distance = Trim(Left(Distance, InStr(Distance, ")") - 1))
CityState = Trim(Left(Data, InStr(Data, "(") - 1))
.Cells(RowCount, ColCount) = CityState
.Cells(RowCount, (ColCount + 1)) = Distance
ColCount = ColCount + 2
Else
.Cells(RowCount, ColCount) = Data
ColCount = ColCount + 1
End If
Loop

'remove leading CR and LF
Do While Left(dealer, 1) = LF Or _
Left(dealer, 1) = CR

dealer = Mid(dealer, 2)
Loop
.Cells(RowCount, ColCount) = dealer
RowCount = RowCount + 1
Next Chld
Next PageCount
End With
End Sub

 

[rik]

That runned perfectly !
I'll see what i can do with it in my own file. If new problems occur, i'll
come back to you. THANKS so far !!

 

[rik]

I could automatically open my brooker's website and manually log on. Next
time i run the macro i did not need to manually log on again, so that's ok.
But i can't go immediately to the page that i need (after having chosen
manually in the pop down menu i get to the page but the explorer address line
still shows the home address).

That's why i tried with sendkeys (open internet on the right page + manually
select the data i need, and then run macro in excel, jump to explorer+copy,
jump back and paste) but that's no solid solution ...

 

[rik]

Ok, i'm making progress now : i found the url to the webpage which is opened
by the code now. Following testcode does the job :

Sub Getquotes()

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

URL =
"https://www.binck.com/gekko/default...euws/fondsdetails/overzicht.aspx?binc=1068754"
Request = ""

'get web page
IE.Navigate2 URL & Request
Do While IE.readyState <> 4
DoEvents
Loop

Set last = IE.document.getElementByID("1068754|LAST")
Range("A1") = last

End Sub


This code fails at line : Range("A1")=last (failure 1004)
In the sourcecode of the webpage i could find :

</td>
<td align="center" style="width:300" class='cSideCell'>
<div id="smfHeader_PnlSide">
<br><table class="verticaltable" rules="cols" border="1">
<tr>
<th class="thead" align="center" valign="middle" colspan="4"
style="font-size:10pt;"><a
href="/gekko//common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754">FTI SEP 2008</a></th>
</tr><tr class="bkgnd_0">
<td align="left" style="width:68px;">Laatste</td><td align="right"
ID="1068754|LAST" NAME="1068754|LAST" style="width:68px;">356,50<span
class='c4d'>00</span></td><td align="right" ID="1068754|TIME"
NAME="1068754|TIME" style="width:68px;">9:42</td><td align="right"
ID="1068754|LASTVOL" NAME="1068754|LASTVOL" style="width:68px;">1</td>
</tr><tr class="bkgnd_1">
<td align="left">+/-</td><td class="cQuoteUp" align="right"
ID="1068754|DELTAABS" NAME="1068754|DELTAABS">1,50<span
class=c4d>00</span></td><td class="cQuoteUp" align="right"
ID="1068754|DELTAREL" NAME="1068754|DELTAREL">0,42%</td><td
align="right"></td>
</tr><tr class="bkgnd_0">
<td align="left">Slot</td><td align="right" ID="1068754|CLOSE"
NAME="1068754|CLOSE">355,00<span class='c4d'>00</span></td><td align="right"
ID="1068754|CLOSETIME" NAME="1068754|CLOSETIME">17-09-08</td><td
align="right"></td>
</tr><tr class="bkgnd_1">
<td align="left">Open</td><td align="right" ID="1068754|OPEN"
NAME="1068754|OPEN">355,50<span class='c4d'>00</span></td><td align="right"
ID="1068754|OPENTIME" NAME="1068754|OPENTIME">8:00</td><td align="right"></td>
</tr><tr class="bkgnd_0">
<td align="left">Hoog</td><td align="right" ID="1068754|HIGH"
NAME="1068754|HIGH">360,50<span class='c4d'>00</span></td><td align="right"
ID="1068754|HIGHTIME" NAME="1068754|HIGHTIME">9:08</td><td align="right"></td>
</tr><tr class="bkgnd_1">
<td align="left">Laag</td><td align="right" ID="1068754|LOW"
NAME="1068754|LOW">349,55<span class='c4d'>00</span></td><td align="right"
ID="1068754|LOWTIME" NAME="1068754|LOWTIME">9:25</td><td align="right"></td>
</tr><tr class="bkgnd_0">
<td align="left">Bied</td><td align="right" ID="1068754|BID"
NAME="1068754|BID">356,45<span class='c4d'>00</span></td><td align="right"
ID="1068754|BIDTIME" NAME="1068754|BIDTIME">9:42</td><td align="right"
ID="1068754|BIDVOL" NAME="1068754|BIDVOL">4</td>
</tr><tr class="bkgnd_1">
<td align="left">Laat</td><td align="right" ID="1068754|ASK"
NAME="1068754|ASK">356,60<span class='c4d'>00</span></td><td align="right"
ID="1068754|ASKTIME" NAME="1068754|ASKTIME">9:41</td><td align="right"
ID="1068754|ASKVOL" NAME="1068754|ASKVOL">6</td>
</tr><tr class="bkgnd_0">
<td align="left">Volume</td><td align="right" ID="1068754|VOL"
NAME="1068754|VOL" colspan="3" style="border-top:1px solid
#102458;">13.699</td>
</tr>
</table><br><br><table class="verticaltable" rules="cols" border="1">
<tr>
<th class="thead" align="center" valign="middle" colspan="4"
style="font-size:10pt;"><a
href="/gekko//common/researchennieuws/fondsdetails/overzicht.aspx?binc=103242">AEX Index</a></th>
</tr><tr>

etcetc



What did i do wrong ?