miten tuoda tietoja verkkosivulta Exceliin?
on tunnettua, että World Wide Web sisältää valtavasti hyödyllistä tietoa. Meidän on kuitenkin tuotava tiedot Microsoft Exceliin ennen kuin teemme minkäänlaista analyysiä. On olemassa kaksi menetelmää, joita voit käyttää tällaisen tehtävän suorittamiseen.
Hanki ulkopuolista tietoa netistä käsin
Oletetaan, että haluat ladata tuloja parhaista rahanteosta kertovista elokuvista tältä sivulta, Tässä näytetään, miten se tehdään helposti.
avaa Microsoft Excel ja klikkaa data-välilehteä Get External Data-ryhmässä webistä. Kun uusi Web-kysely-valintaikkuna ilmestyy, kopioi verkko-osoite (http://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) osoitekenttään ja napsauta Go-painiketta. Excel alkaa ladata verkkosivua Kuvan 1.1 mukaisesti. Klikkaa Ei, Jos saat Script Virhevaroitus laatikko, joka on samanlainen kuin alla kuvassa. Laatikko katoaa ja sillä ei ole vaikutusta tuontiprosessiin.
Kuva 1.1
uuden Web-kyselyn oikeassa yläkulmassa olevassa keltaisessa laatikossa on nuoli. Klikkaamalla sitä, voit määrittää, näytetäänkö samanlaisia kuvakkeita ennen taulukoita vai ei. Esimerkiksi kuvan 1.2 vasemmassa paneelissa taulukon vieressä ei ole nuolikuvaketta. Ja siellä on kuvake (oikeassa paneelissa) kun klikkaat nuolinäppäintä näyttää kuvakkeet.
Kuva 1.2
klikkaa valittavien taulukoiden vieressä olevaa nuolikuvaketta. Kuvakkeet ja taulukot muuttuvat ja samanlainen kuin kuvassa 1.3 vasemmassa paneelissa. Kun olet napsauttanut tuonti; tuonti Data-valintaikkuna kysytään. Täytä alue (sarakkeesta A sarakkeeseen h meidän tapauksessamme), johon haluat laittaa tiedot ja klikkaa OK.
Kuva 1.3
tiedot tuodaan Exceliin, Kun napsautat Ok: ta. Jos napsautat hiiren kakkospainikkeella mitä tahansa taulukon solua ja valitset Päivitä, Excel menee ulos ja vetää viimeisimmät tiedot verkkosivulta.
Kuva 1.4
voit myös asettaa kyselytietosi päivitettäviksi haluamallasi tavalla. Napsauta hiiren kakkospainikkeella mitä tahansa taulukon solua ja valitse Data Range Properties. Pyydetyssä ulkoisen Data Range Ominaisuudet-valintaikkunassa voit muuttaa Päivitysohjauksen asetuksia tyylin muuttamiseksi. Voit esimerkiksi määrittää kyselyn päivitettäväksi 60 minuutin välein tai kun tiedosto avataan.
Kaavi tietoja käyttämällä VBA-ohjelmointia
VBA-ohjelmointia voidaan raaputtaa tietoa verkkosivulta. Se on paljon hyödyllisempää, kun taas vaikeaa, kun verrataan ensimmäiseen lähestymistapaan. Lisäksi, sinun täytyy ymmärtää, mitä HTML on ennen kuin opit kaapia tietoja käyttämällä VBA ohjelmointi. Suosittelen, että opit perustiedot HTML tältä sivustolta, jos et tiedä mitään tai vähän HTML. Ja Täältä, voit oppia lähes kaiken liittyvät tietojen kaavinta käyttäen VBA. Tämä artikkeli näyttää vain kaksi esimerkkiä.
Kaavi tiedot yhdeltä sivulta
Oletetaan, että haluamme poimia yrityksen nimen, sähköpostiosoitteen ja yhteyshenkilön nimen tältä sivulta. Jos avaat tämän web-sivun, huomaat, että alareunassa on yhteystietolohko. Kuvassa 2.1 on kontaktilohko ja vastaava lähdekoodi. Punaisissa laatikoissa olevat tiedot ovat sitä, mitä haluamme tarvita. Ja ne, joissa on vihreä alleviivaus, ovat juuri sitä, mitä meidän pitää poimia.
kuva 2.1
seuraava koodi voi auttaa sinua poimia edellä pyydetyt tiedot ja laittaa ne ensimmäiseen laskentataulukkoon.
| lähdekoodi |    |
Sub Retrieve_Click ()"Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Sovellus") ' ei nähdä selainikkuna.Visible = False ' Avaa verkkosivu.Navigoi "http://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736 "' Wait while IE is loadingDo While IE.readyState < > 4 tai TS.Busy = True DoEvents Loop ' Retrieve company name, email address & contact informationSet contactobj = IE.asiakirja.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML MsgBox htext If InStr (htext, "<P>yrityksen nimi: ") Then ThisWorkbook.Laskentataulukot (1).Solut (1, 1) = Split(Split (htext, " <p>yrityksen nimi: ")(1), "<br") (0) End If if InStr(htext, "mailto:") Then ThisWorkbook.Laskentataulukot (1).Solut (2, 1) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) lopeta If If InStr (htext, "<p>Name: ") niin ThisWorkbook.Laskentataulukot (1).Solut (3, 1) = Split(Split (htext, " <p>Nimi: ")(1), "<br") (0) lopeta, jos ThisWorkbook.Laskentataulukot (1).Solut (4, 1) = ts.Paikantakaa Tämä Työkirja.Tallenna sarja IE = ei mitään asetettua kontaktobj = Ei mitään loppua Sub
”TS.asiakirja.getElementsByClassName (”yhteystiedot-tiedot block tumma”) ” voit saada kaikki elementit luokan nimi – yhteystiedot-tiedot block tumma. Tällä sivulla luetellaan lähes kaikki ominaisuudet ja menetelmät, joita voidaan käyttää HTML-elementeissä. Voit valita sopivan omaan ongelmaasi.
innerhtml-ominaisuuden avulla voit asettaa tai palauttaa HTML-elementin sisällön. Meidän tapauksessamme se palautti elementin sisällön luokkanimellä ”yhteystiedot-yksityiskohdat block dark” Ja asetti sen muuttuvaksi htextiksi. Sisällön (Kuva 2.2) voi pyytää ”Msgbox htext”.
Kuva 2.2
näet, että teksti on hyvin jäsennelty. Siksi voimme käyttää SPLIT toiminto poimia mitä tarvitaan. Esimerkiksi, jos otetaan ” <p>yrityksen nimi:”erottimena ja kaavana” jako (htext, ”<p>yrityksen nimi:”) (1) ”voi palauttaa koko tekstin jälkeen”<P>yrityksen nimi:”. Tähän vasta palautettuun tekstiin voidaan ottaa ”<br” erottimeksi ja saada teksti ennen ensimmäistä ”<br” ja se on yrityksen nimi. Yhteenvetona SPLIT-toiminto on joustava työkalu, jonka avulla voit purkaa lähes kaiken. Muita hyödyllisiä toimintoja ovat LEN, INSTR, LEFT, RIGHT, MID ja REPLACE. En keskustele tässä yksityiskohtaisesti.
kun klikkaat OK-painiketta kuvassa 2.2, pyydetyt tiedot voidaan tuoda verkosta Excel-laskentataulukkoon. Esimerkiksi solu A1 sisältää yrityksen nimen, kun taas solu A4 sisältää yrityksen verkkosivun osoitteen.
Kuva 2.3
lisäämällä alla olevan koodin ennen kuin tallennat työkirjan, voit lisätä hyperlinkin soluun A4.
| lähdekoodi | |
'Lisää hyperlinkki tähän työkirjaan.Laskentataulukot (1).Hyperlinkki.Lisää Tämä Työkirja.Laskentataulukot (1).Solut (4, 1), Tämä Työkirja.Laskentataulukot (1).Solut(4, 1)
jos napsautat solua A4, voit palata verkkosivulle. Tämä on hyödyllistä varsinkin, kun sinun täytyy hakea tietoja paljon yrityksiä. Koska voit klikata mitä tahansa hyperlinkkiä käydäksesi uudelleen vastaavalla sivulla ja lisätäksesi tai päivittääksesi tietoja manuaalisesti tarkastelun aikana myöhemmin.
Kuva 2.4
vuorovaikutuksessa www-sivujen kanssa
yllä olevassa esimerkissä vain havainnollistetaan, miten tietoja voidaan hakea yhdeltä staattiselta web-sivulta. Mutta useammin kuin usein, meidän on Pura ovat vuorovaikutuksessa web-sivuja, jotta hakea suuren määrän tietoja. Katso kuva 3.1. Se näyttää, miten päästä web-sivun yllä olevassa esimerkissä. Näkee, että toimialoja on paljon ja jokaiselle toimialalle on paljon yrityksiä. Esimerkiksi maatalousalan yrityksiä on 651. Mitä meidän pitäisi tehdä, jos haluamme poimia kaikkien yritysten yhteystiedot kaikilta toimialoilta?
kuva 3.1
no, avainasia on, että miten saada VBA vuorovaikutuksessa web-sivuja kuin mitä teemme manuaalisesti. Otetaan esimerkiksi S. W. I. S: n etu. Yleisesti toivomme, että Excel voi klikata Agribusiness (yläpaneeli kuvassa 3.1) ja siten laukaista IE ohjata meidät toiselle web-sivulle. Toisella web-sivulla (pohja paneeli kuvassa 3.1), Excel voi klikata S. W. I. S etu, ja sitten IE ohjaa meidät sivulle kuten kuvassa 2.1 niin, että voimme hakea yhteystiedot S. W. I. S etu.
syötettyäsi seuraavan koodin Visual Basic-muokkaimeen ja lähetettyäsi koodin näet, että IE on avattu. Ensimmäinen sivu ilmestyy ja toinen sivu. Tässä opit, miten saada avattavasta luettelosta Elementti, valitse vaihtoehto, ja käynnistää tapahtuman, kun olet valinnut vaihtoehdon. ”m = TS.asiakirja.getElementsByTagName (”optio”).Pituus-1″ antaa sinulle yhteensä useita vaihtoehtoja. Tätä voidaan käyttää seuraavassa silmukassa.
| lähdekoodi | |
Sub retrieve () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Sovellus") ' Katsotaanpa selainikkuna.Visible = True ' Avaa verkkosivu.Navigoi "http://www.austrade.gov.au/international/buy# "' Wait while IE is loadingDo While IE.Varattu Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka sovellus.Odota (nyt + TimeValue("00:00:10")) 'Osa 1-Valitse pudotusvalikosta ja laukaista tapahtuma, Kun olet valinnut yhden vaihtoehdon selectobj = IE.asiakirja.getElementsByTagName ("select") m = IE.asiakirja.getElementsByTagName ("optio").Pituus - 1 selectobj (0).selectedIndex = 1 selectobj (0).Firevent ("onchange") 'Wait while IE is loadingDo While IE.readyState < > 4 tai TS.Varattu = Todellinen Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka sovellus.Odota (nyt + TimeValue("00:00:10")) End Sub
tämä osa koodia voi ohjata sinut web-sivun kuvassa 2.1 jälkeen Excel klikkaa etunimi. Kaikkien yritysten nimet sisältyvät elementtiin, jonka luokkanimi on”Name”. Searchobj on kokoelma ja searchobj (i)voi palauttaa (I+1) TH objekti. Esimerkiksi searchobj(1).Klikkaa voi voit käydä web-sivun Ridley CORPORATION (Melbourne).
| lähdekoodi | |
'Osa 2-Valitse yrityksen nimi searchobj = IE.asiakirja.getElementsByClassName ("Name") searchobj (0).Klikkaa ' odota kun IE on loadingDo kun IE.readyState < > 4 tai TS.Varattu = True DoEvents Loop
lopuksi, tässä on koko koodi, joka voi näyttää prosessin avaaminen IE, selaamalla verkkosivuja, ja talteen tietoja. Saadut tiedot ovat samat kuin kuvassa 2.4.
| lähdekoodi | |
Sub Retrieve () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Sovellus") ' Katsotaanpa selainikkuna.Visible = True ' Avaa verkkosivu.Navigoi "http://www.austrade.gov.au/international/buy# "' Wait while IE is loadingDo While IE.Varattu Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka sovellus.Odota (nyt + TimeValue("00:00:10")) 'Osa 1-Valitse pudotusvalikosta ja laukaista tapahtuma, Kun olet valinnut yhden vaihtoehdon selectobj = IE.asiakirja.getElementsByTagName ("select") m = IE.asiakirja.getElementsByTagName ("optio").Pituus - 1 selectobj (0).selectedIndex = 1 selectobj (0).Firevent ("onchange") 'Wait while IE is loadingDo While IE.readyState < > 4 tai TS.Varattu = Todellinen Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka sovellus.Odota (nyt + TimeValue("00:00:10")) 'Osa 2-Valitse yrityksen nimi searchobj = IE.asiakirja.getElementsByClassName ("Name") searchobj (0).Klikkaa ' odota kun IE on loadingDo kun IE.readyState < > 4 tai TS.Busy = True DoEvents Loop ' Part 3-Retrieve company name, email address & contact informationSet contactobj = IE.asiakirja.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML If InStr (htext, "<P>yrityksen nimi: ") sitten ThisWorkbook.Laskentataulukot (1).Solut (1, 1) = Split(Split (htext, " <p>yrityksen nimi: ")(1), "<br") (0) End If if InStr(htext, "mailto:") Then ThisWorkbook.Laskentataulukot (1).Solut (2, 1) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) lopeta If If InStr (htext, "<p>Name: ") niin ThisWorkbook.Laskentataulukot (1).Solut (3, 1) = Split(Split (htext, " <p>Nimi: ")(1), "<br") (0) lopeta, jos ThisWorkbook.Laskentataulukot (1).Solut (4, 1) = ts.Paikannusurl ' Lisää hyperlinkki tähän työkirjaan.Laskentataulukot (1).Hyperlinkki.Lisää Tämä Työkirja.Laskentataulukot (1).Solut (4, 1), Tämä Työkirja.Laskentataulukot (1).Solut (4, 1) loppuun osa
itse asiassa, mitä meidän täytyy tehdä, on poimia yhteystiedot kaikille yrityksille kaikilta toimialoilta, siksi meidän täytyy tehdä käyttää silmukkalauseke tämän tehtävän suorittamiseen. Seuraavassa on täydellinen koodi. Ja voit myös löytää koodin hakea yhteystiedot kaikille yrityksille.xlsm jonka voit ladata tämän artikkelin lopussa.
| lähdekoodi | |
Sub Retrieve () For idex = 2-18 'Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Sovellus") ' Katsotaanpa selainikkuna.Visible = False ' Avaa verkkosivu.Navigoi "http://www.austrade.gov.au/international/buy# "' Wait while IE is loadingDo While IE.Varattu Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka sovellus.Odota (nyt + TimeValue("00:00:10")) idexn = idex - 1 ' Part 1-Select dropdownSet selectobj = IE.asiakirja.getElementsByTagName ("select") m = IE.asiakirja.getElementsByTagName ("optio").Pituus - 1 selectobj (0).selectedIndex = idexn selectobj (0).Firevent ("onchange") 'Wait while IE is loadingDo While IE.readyState < > 4 tai TS.Varattu = Todellinen Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka sovellus.Odota (nyt + TimeValue("00:00:10")) wurl = TS.LocationURL tot = TS.asiakirja.getElementsByClassName ("Searctotal") (0).innerHTML pg = Int (tot / 25) + 1 Max = (tot Mod 25) - 1 'Osa 2 - Select Class = "Name"a = 2 For j = 1 To pg If j = 1 Then IE.Navigoi (wurl) Else IE.Navigoi (wurl & "&pg= " & j) lopeta If Do while IE.Varattu Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka jos j <> pg niin I = 1-24 Set searchobj = IE.asiakirja.getElementsByClassName ("Name") searchobj (i).Klikkaa ' odota kun IE on loadingDo kun IE.readyState < > 4 tai TS.Busy = True DoEvents Loop ' Part 3-Retrieve company name, email address & contact informationSet contactobj = IE.asiakirja.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML this workbook.Laskentataulukot (idex).Solut (a, 1) = J ThisWorkbook.Laskentataulukot (idex).Cells(a, 2) = A - 1 If InStr (htext, "<P>Company Name: ") ThisWorkbook.Laskentataulukot (idex).Solut (a, 3) = Split(Split (htext, " <p>yrityksen nimi: ")(1), "<br") (0) End If if InStr(htext, "mailto:") Then ThisWorkbook.Laskentataulukot (idex).Solut (a, 4) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) lopeta If If InStr (htext, "<p>Name: ") niin ThisWorkbook.Laskentataulukot (idex).Solut (a, 5) = Split(Split (htext, " <p>Nimi: ")(1), "<br") (0) lopeta, jos ThisWorkbook.Laskentataulukot (idex).Solut (a, 6) = TS.Paikannusurl IE.GoBack tehdä kun IE.Varattu Sovellus.Odota Päivämäärälisäys ("s", 1, Nyt) Loop a = a + 1 Seuraava I Else For I = 0 to Max Set searchobj = IE.asiakirja.getElementsByClassName ("Name") searchobj (i).Klikkaa ' odota kun IE on loadingDo kun IE.readyState < > 4 tai TS.Busy = True DoEvents Loop ' Part 3-Retrieve company name, email address & contact informationSet contactobj = IE.asiakirja.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML this workbook.Laskentataulukot (idex).Solut (a, 1) = J ThisWorkbook.Laskentataulukot (idex).Cells(a, 2) = A - 1 If InStr (htext, "<P>Company Name: ") ThisWorkbook.Laskentataulukot (idex).Solut (a, 3) = Split(Split (htext, " <p>yrityksen nimi: ")(1), "<br") (0) End If if InStr(htext, "mailto:") Then ThisWorkbook.Laskentataulukot (idex).Solut (a, 4) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) lopeta If If InStr (htext, "<p>Name: ") niin ThisWorkbook.Laskentataulukot (idex).Solut (a, 5) = Split(Split (htext, " <p>Nimi: ")(1), "<br") (0) lopeta, jos ThisWorkbook.Laskentataulukot (idex).Solut (a, 6) = TS.Paikantakaa Tämä Työkirja.Laskentataulukot (idex).Hyperlinkki.Lisää Tämä Työkirja.Laskentataulukot (idex).Cells (a, 6), ThisWorkbook.Laskentataulukot (idex).Solut (a, 6) TS.GoBack tehdä kun IE.Varattu Sovellus.Odota Päivälisäys ("s", 1, nyt) silmukka A = A + 1 seuraavaksi lopetan, jos Tämä työkirja.Tallenna Seuraava J asettaa IE = ei mitään asettaa contactobj = Ei mitään seuraava idex loppuun Sub
ainoa asia, joka minun täytyy selittää on esitetty kuvassa 3.2. Yhdessä verkossa voi listata vain 25 yritystä. Kun yrityksiä on yhteensä yli 25, sivuja on enemmän kuin yksi. Kuva 3.2 osoittaa, että on olemassa sääntö saada osoitteen sivujen jälkeen ensimmäisen sivun. Se on seurausta ensimmäisen sivun yhtymäosoitteesta ”&pg= ” ja todellisesta sivunumerosta. Ja kaikkien ennen viimeistä sivua olevien sivujen kokonaisnumero on 25. ”ELI.asiakirja.getElementsByClassName (”Searctotal”) (0).innerHTML ” voi palauttaa yritysten kokonaismäärän toimialalla. Meidän tapauksessamme se on 651. ”Int (tot / 25) + 1” voi saada kokonaissivumäärän. Ja ”Max = (tot Mod 25) – 1” voi palauttaa maksimimäärän yrityksiä viimeisellä sivulla. Lopetan tähän ja jätän sinut miettimään, miten soveltaa tätä ajatusta koodiin. Se on paljon parempi tapa tarttua koodiin. Voit jättää kommentin, jos sinulla on kysyttävää.
Kuva 3.2
Tässä näkyy osa lopullisesta Excelistä. Kaikkien yhden toimialan yritysten yhteystiedot on koottu yhteen laskentataulukkoon.
Kuva 3.3
- miten poimia tietoja verkkosivuilla Excel automaattisesti?
- Tuo tiedot (lause, kappaleet, taulukot, Kommentit) Wordista Exceliin
- 6 parasta Excel VBA – Ohjelmointikirjaa (aloittelijoille & edistyneille käyttäjille)
- Opi Excel VBA-ohjelmointi & makrot (Free Tutorial-Step by Step)
- Excel VBA-Koodausvinkit
- mitä voit tehdä VBA
- Johdatus VBA-makroihin
Lataa työtiedosto
Lataa työtiedosto alla olevasta linkistä.
Pull-Data-from-Web-to-Excel.rar