Macro copying website

[Max_hugo]

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:

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:

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?

 

[John_w]

In the VBA editor, click Tools -> References and tick Microsoft Internet Controls. Then replace these lines:

    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")

with:

    Dim IE As InternetExplorerMedium
    Set IE = New InternetExplorerMedium

 

[Max_hugo]

I am receiving a message after the change

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