Copying webpage from IE not working

[XLSM Belgium]

Hi,

I modified a code snippet from the internet to work for my needs, but the code keeps giving errors.

The code is this:

Option Explicit

Private Sub Berekenen_Click()
    Dim IE As Object

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate ThisWorkbook.Sheets("terugverdientijd").Range("L13").Value
        While IE.busy
            DoEvents
        Wend


    
    Sheets("terugverdientijd").Range("L14:L66") = ""
    IE.ExecWB 17, 0 '// SelectAll
    IE.ExecWB 12, 2 '// Copy selection
    Sheets("terugverdientijd").Range("L14").Select
    Sheets("terugverdientijd").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    Range("A1").Select
    
    IE.Quit
    Set IE = Nothing


End Sub

Basically in cell L13 a URL is formed with some parameters from the sheet.
Example URL is this: http://users.skynet.be/betberge/pvd...ff=-0.48&az2=0&roof2=0&peakw2=0&temp_coeff2=0

This URL opens in IE, everything is selected (and I assume copied), but then I get a error 1004 on pastespecial.

I have no idea why.

Sometimes it randomly does work (like 10% of the time)...

Also, there is some text in a flash box (in the example url it's this: )

Donderdag, 15 juni 2017
Zonsopgang: 05:32, Hoogste zonnestand: 62.4° om 13:46, Zonsondergang: 22:01
Theoretisch berekende clear-sky opbrengst: 29727 Wh (6.3 Wh/Wp)
Max clear-sky vermogen: 3803 Watt om 11:30

This never gets copied over (presumably because of the flash thingy), and I would really love to have that information too...

Any ideas on these 2 problems?

 

[NMa91]

First Problem:

While IE.busy Or IE.readyState <> 4
            DoEvents
        Wend
        Application.Wait (Now() + TimeValue("0:0:01"))

 

[XLSM Belgium]

Thank you for your answer.
I tested the change, but the behavior is still the same, runtime error 1004...

 

[XLSM Belgium]

First problem solved, I implemented the change NMa91 suggested, and changed the pastespecial to html.

Option Explicit

Private Sub Berekenen_Click()
    Dim IE As Object


    Set IE = CreateObject("InternetExplorer.Application")
    'IE.Visible = True
    IE.Navigate ThisWorkbook.Sheets("terugverdientijd").Range("L13").Value
        While IE.busy Or IE.readyState <> 4
            DoEvents
        Wend
        Application.Wait (Now() + TimeValue("0:0:01"))


    
    Sheets("terugverdientijd").Range("L14:Q40") = ""
    IE.ExecWB 17, 0 '// SelectAll
    IE.ExecWB 12, 2 '// Copy selection
    Sheets("terugverdientijd").Range("L14").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    
    IE.Quit
    Set IE = Nothing
    Range("A1").Select
End Sub

Works ok now.

Any idea on how to get the text from the flash graph?

 

[XLSM Belgium]

Since first problem is solved, I will create a new topic for the second problem.