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
 

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,129
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
23
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
4,129

ADVERTISEMENT

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
23
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
23

ADVERTISEMENT

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
4,129
Your installation looks fine. I am using Excel 2016 32-bit. Try my test workbook:

test
 

mskli

New Member
Joined
Feb 20, 2017
Messages
23
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
23
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,129,553
Messages
5,636,983
Members
416,953
Latest member
broexc

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