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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,546
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,119,277
Messages
5,577,177
Members
412,773
Latest member
ely6678
Top