Macro copying website

Max_hugo

New Member
Joined
May 3, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi, I am currently working on a macro that would open links to pages and then after opening the website would select everything and copy to Excel, e.g. to a page sheet. Here is my macro:

VBA Code:
Sub www()
    Dim mmnt!
    Dim strUrl$
    Dim ie As Object
    strUrl = "http:"
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.navigate strUrl
    While (ie.Busy Or ie.readyState <> 4): DoEvents: Wend
    mmnt = Timer: While Timer - mmnt < 2: DoEvents: Wend
    ie.ExecWB 17, 0
    ie.ExecWB 12, 2
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="HTML", link:=False, NoHTMLFormatting:=True
    ie.Quit
    Set ie = Nothing
End Sub

Unfortunately, the macro does not work on some internal pages. The problem occurs after successfully starting Internet Explorer and loading the html page immediately receives the error message:

VBA Code:
Run-time error - 2147467259 (80004005)
While (ie.Busy Or ie.READYSTATE <> 4): DoEvents: Wend

I have a question how to deal with it? How to improve the macro?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,539
In the VBA editor, click Tools -> References and tick Microsoft Internet Controls. Then replace these lines:

VBA Code:
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
with:
VBA Code:
    Dim IE As InternetExplorerMedium
    Set IE = New InternetExplorerMedium
 

Max_hugo

New Member
Joined
May 3, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
I am receiving a message after the change
VBA Code:
run-time error '1004' The PasteSpecal method from the Range class failed

[U]The macro has stopped on:[/U]

ActiveSheet.PasteSpecial Format:="HTML", link:=False, NoHTMLFormatting:=True
 

Watch MrExcel Video

Forum statistics

Threads
1,118,852
Messages
5,574,663
Members
412,608
Latest member
Guromir
Top