Importing Web Data into Excel

[mskli]

Hi:

I have often used the get external data feature that Excel has to get certain web pages or data into Excel. However there are times when the web page isn't all that friendly wrt importing data into Excel.

At the following link there is a table with Merger Arbitrage information that I can't seem to import into Excel. It appears as if the table is embedded. When I port this link - all the other information appears but not the data I require.

The Risk Arbitrage Report

Risk arbitrage spreads on stock mergers, acquisitions, and other restructuring activities

intrinsicedge.blogspot.com

If I right click the table - page source - then I find the following link to the table (scrolling down).

Mergers 20200515 - Google Drive

docs.google.com

I believe this works but it is time consuming and I may as well copy and paste. I'm looking for something that can update it with a simple "refresh"

Is there an easier way to get this data other than simply copying and pasting on a weekly basis?

thanks

 

[alansidman]

Hey Worf,
I followed all the instructions in the web sites you suggested and am getting this error message. Any thoughts here on fixing this. Oh, also, checked the Selenium Folder and it checks out.
Google Chrome is up to date
Version 83.0.4103.61 (Official Build) (64-bit)

Also tried to run your code from your book and received the same error message. Running MS Net Framework 4.8 which is the latest available.


My code I was testing from the web site is also below.

 

[Worf]

Hi Alan

Maybe you have an outdated executable Chrome driver at the Selenium Basic folder.

What is the date of that file? This is the first thing to check so we can be sure it is the correct one.

 

[alansidman]

Chrome Driver is dated 2/8/16.

 

[Worf]

That is old. You need to replace it with the corresponding Chrome 83 driver from the page below:

Downloads - ChromeDriver - WebDriver for Chrome

 

[alansidman]

Thanks Worf. Seems to have it now.

 

[mskli]

Worf:

The macro works great. It currently copies only the text over. Some of the data in the table have links to other web pages. Is it possible to include these links when the web page is copied to Excel?

I'm guess that the following code moves the the table to Excel:

col2.item(1).AsTable.ToExcel ThisWorkbook.Worksheets("table").[a1]

thanks!

 

[Worf]

Sorry for the delay...

Public d As WebDriver

Public Sub Table_n()
Dim col As Object, i%, col2 As Object, j%, s$, te As Object, re As Object, k%, nc%
Const URL As String = "http://intrinsicedge.blogspot.com/"
Set d = New ChromeDriver
With d
    .Start "Chrome"
    .get URL: .Wait 800
    Set col = d.FindElementsByTag("iframe")
    MsgBox col.Count, , "iframe count"
    For i = 1 To col.Count                                          ' loop iframes
        If col.item(i).Attribute("src") Like "*docs.google*" Then
            s = col.item(i).Attribute("src")
            .Quit
            .get s                                                  ' go to docs page
            Set col2 = .FindElementsByTag("iframe")
            .SwitchToFrame col2.item(1)                             ' enter iframe
            Set col2 = .FindElementsByTag("table")
            MsgBox col2.Count, , "table count"
            Set te = col2.item(1).FindElementsByTag("tr")
            Set re = te.item(10).FindElementsByTag("td")
            nc = re.Count
            For j = 2 To col2.item(1).FindElementsByTag("tr").Count - 1
                For k = 1 To nc
                    Set re = te.item(j).FindElementsByTag("td")
                    Cells(j, k) = re.item(k).Text
                    Set re = re.item(k).FindElementsByTag("a")
                    If re.Count > 0 Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(j, k), _
                    Address:=re.item(1).Attribute("href"), TextToDisplay:=CStr(Cells(j, k).Value)
                Next
           Next
           Exit Sub
        End If
    Next
    .Quit
End With
End Sub

 

[mskli]

Worf:

Works great. Thanks so much!