Importing Web Data into Excel

mskli

New Member
Joined
Feb 20, 2017
Messages
23
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.


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


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

Well-known Member
Joined
Feb 26, 2007
Messages
6,169
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    29.8 KB · Views: 3
  • Capture2.JPG
    Capture2.JPG
    42.4 KB · Views: 3
  • Capture3.JPG
    Capture3.JPG
    57 KB · Views: 2

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,128
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,169
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Worf. Seems to have it now.
 

mskli

New Member
Joined
Feb 20, 2017
Messages
23
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,128
Sorry for the delay...

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,864
Messages
5,627,338
Members
416,242
Latest member
Kas O

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top