Importing Web Data into Excel

mskli

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

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
Hi

The table is inside an iframe. I will be back tomorrow to continue…

VBA Code:
Public driver As New ChromeDriver

Sub Kli()
Dim pt As WebElement
driver.get "http://intrinsicedge.blogspot.com/"
Application.Wait Now + TimeValue("0:00:03")
Set pt = driver.FindElementByXPath _
("//*[@id=""Blog1""]/div[1]/div/div/div[3]/div/div[2]/iframe")
MsgBox pt.tagName
End Sub
 

mskli

New Member
Joined
Feb 20, 2017
Messages
12
just to clarify - each week (Sunday) the table updates and there is a new url. So refresh will not work
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
This code does it. Note that it is necessary to install the Chrome driver, see post #4 of thread below for instructions.

VBA Code:
Public d As WebDriver

Public Sub Table_no_loop()
Dim col As Object, i%, col2 As Object, j%, s$
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"
            col2.item(1).AsTable.ToExcel ThisWorkbook.Worksheets("table").[a1]
            Exit Sub                                                ' table transferred
        End If
    Next
    .Quit
End With
End Sub
import data from Web
 

mskli

New Member
Joined
Feb 20, 2017
Messages
12
I think I did everything right? (Does it matter that I'm using an 64 bit version of excel?)

I get the following error



1590107639838.png


then debug

1590107937311.png



Chrome Version 83.0.4103.61 (Official Build) (64-bit)

chromedriver.exe

1590107719351.png


1590107894780.png


1590107808033.png
 

mskli

New Member
Joined
Feb 20, 2017
Messages
12
Actually when I run it from Excel (not the VBA) I get the following error:

1590108175357.png



Do I need the first code that you did? (post #2)
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
Your installation looks fine. I am using Excel 2016 32-bit. Try my test workbook:

test
 

mskli

New Member
Joined
Feb 20, 2017
Messages
12
Worf:

Same error when I run your spreadsheet.

I'm running Excel 21016 64 bit version (I require 64 bit).

Do you think this is a 32/64 issue? or could it still be an installation issue?




1590238939097.png
 

mskli

New Member
Joined
Feb 20, 2017
Messages
12
I did some research and found that downloading Microsoft.Net Framework 3.5 fixes this error. So I tried it and everything worked great.

thanks so much!

much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,728
Messages
5,446,174
Members
405,389
Latest member
Excel n00b2

This Week's Hot Topics

Top