excel get data from web, but with redirecting URL

stevearama

New Member
Joined
Aug 31, 2019
Messages
5
Can anyone help me to use the advanced options on excels DATA > Get Data > From Web menu, to overcome a redirecting URL?
The data I wish to scrape is from this url PAJ Oil Statistics Weekly
However, that URL redirects to here PAJ Oil Statistics Weekly .. which asks me to accept some condition.

Below I have some VBA code that will get me to the correct ULP, it works by first opening IE with the first ULP, then navigating to the second. This works fine, but I wish to implement this inside the Excel GET DATA from Web inteface so that I can scrape the data into excel

Hopefully that question is clear. Thank you for your guidance

VBA Code:
Sub WebLogin()
Dim a As String
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .navigate "https://stats.paj.gr.jp/en/pub/index.php"
        Do Until .readyState = 4
            DoEvents
        Loop
        .navigate "https://stats.paj.gr.jp/en/pub/current_en_n2.html"
    End With
End Sub
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
You could do it entirely with VBA; the example below uses the Chrome driver.

VBA Code:
Sub Starama()
Dim driver As New ChromeDriver, pt
driver.get "https://stats.paj.gr.jp/en/pub/index.php"
Set pt = driver.FindElementByXPath("/html/body/div[1]/div/a[1]")
DoEvents
pt.Click
DoEvents
Set pt = driver.FindElementByXPath("//u")
pt.Click
DoEvents
MsgBox "You should see the table page at this point."
Set pt = driver.FindElementByXPath("//div[3]/table[2]/tbody/tr[3]/td/table/tbody")
MsgBox pt.FindElementsByTag("tr").Count, 64, "Number of table rows"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,509
Messages
5,548,486
Members
410,839
Latest member
MrSumo85
Top