Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from web page into excel
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" wrote: 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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from web page into excel
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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from web page into excel
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" wrote: 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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from web page into excel
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 !! "Joel" wrote: 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" wrote: 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 ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from web page into excel
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 ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from web page into excel
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.aspx?Redir=/gekko/common/researchennieuws/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=c4d00</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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i import data from a web page into excel 2007? | Excel Worksheet Functions | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
how to import data from a web page | Excel Discussion (Misc queries) | |||
Import data from a web page | Excel Discussion (Misc queries) | |||
data before page break lost during import | Excel Discussion (Misc queries) |