Data Extraction from Website Using VBA

[Mostafa Kanza]

I am trying to extract temperature data table based for the city that the user inputs.

so far, i am stuck at writting the city into the website and then clicking search.

after that I want to automate instead of fixed Cairo, i want the user input in excel to be the one in the toolbox

after that I would want to extract the table of that specific city into the excel.

thanks a lot in advance

_____________________________


Sub Extract_MKSA()


'Dim path As Stringpath = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles)
'Dim executable As String executable = path.Combine(path, "C:\Program Files (x86)\Google\Chrome\Application")
'Process.Start(executable, "Google")


Dim html As HTMLDocument

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.addressbar = 0
objIE.StatusBar = 0
objIE.Toolbar = 0

objIE.Visible = True
objIE.navigate ("World Weather Information Service")
Do
DoEvents
Loop Until objIE.Readystate = 4
pageSource = objIE.Document.body.outerhtml

objIE.Document.getElementsByClassName("top_searchbox ui-autocomplete-input").Value = "Cairo" // getelementsbyclassname is not working to input "Cairo"
objIE.Document.getElementsByID("q_search").Value = "Cairo" // this inputs "Cairo" still when i click even manually on the search button it seems as if there is nothing in the websites textbox.
'objIE.Document.getElementsByname("submit").Click // the search button has no id and i am trying to click the button to search for cairo and then later worry about how to extract the table into excel.




End Sub


Thanks a lot in advance.

 

[Rijnsent]

Hi Mostafa,
for web scraping, it's generally smarter to look for the data behind the website. Most of the time they pull that in through an API of some kind. When dissecting worldweather I found some sources that you could use. Mainly: a list of all the cities/countries they have and a city weather table.
Try the following:
-in excel, go for Data->Get data->Start Power Query editor
-go for "new query" -> "other source" -> "empty query"
-paste this:

let
    Src = Json.Document(Web.Contents("http://worldweather.wmo.int/en/json/QuickSearch_en.xml")),
    #"Convert to table" = Table.FromList(Src, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 expand" = Table.ExpandRecordColumn(#"Convert to table", "Column1", {"value", "key", "eng"}, {"Column1.value", "Column1.key", "Column1.eng"})
in
    #"Column1 expand"

That should give you a list of all the countries & cities that are available, with their ID/key, which you will need to pick up the data from the individual cities. For one city, that could be:

let
    Src = Json.Document(Web.Contents("http://worldweather.wmo.int/en/json/861_en.xml")),
    city = Src[city],
    forecast = city[forecast],
    forecastDay = forecast[forecastDay],
    #"Convert to table" = Table.FromList(forecastDay, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 expand" = Table.ExpandRecordColumn(#"Convert to table", "Column1", {"forecastDate", "wxdesc", "weather", "minTemp", "maxTemp", "minTempF", "maxTempF", "weatherIcon"}, {"Column1.forecastDate", "Column1.wxdesc", "Column1.weather", "Column1.minTemp", "Column1.maxTemp", "Column1.minTempF", "Column1.maxTempF", "Column1.weatherIcon"})
in
    #"Column1 expand"

Note: the only thing you'd need to do is to change 861 to another number for a new table of information. Use e.g. this tutorial to link it to a cell: Power Query Parameters - using Excel named cells

Cheers,

Koen