Web Query Problem - HELP!

[j844929]

Hi,

I'm trying to import all of the EUROSTOXX 50 spot prices from the following URL:

http://www.finanzen.net/aktien/realtimekurse.asp?inindex=11

What's happening is that the live updating Bid and Ask prices in the bottm table aren't displayed in the spreadsheet after running the web query. I assume this is probably because their properties are different to static data. I've tried all sorts of thing like changing properties in the web query etc, all to no avail. I've also tried the following script, but when it pastes to the spreadsheet, the code tends to fall over:

Sub Spot_Data_Import()

On Error GoTo Err

Application.ScreenUpdating = False

Dim Chosen_Date, Period, CopyRow, Bid, Offer, Spot As String


'This code imports the required Bid-Offer Data

    Dim IE As Object
    Sheets("Spot_Data").Select
    Cells.Select
    Selection.ClearContents ' erase previous data
       ActiveSheet.Range("A1").Select
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
' should work for any URL
        .navigate "http://www.finanzen.net/aktien/realtimekurse.asp?inindex=11"

        Do Until .readyState = 4: DoEvents: Loop
'wait until the live streaming data appears    
Application.Wait Now + TimeValue("00:00:05")

    End With

  
    IE.ExecWB 17, 0 '// SelectAll
    IE.ExecWB 12, 2 '// Copy selection
  
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
    IE.Quit

MsgBox "Spot price updated!"
Exit Sub

Application.EnableEvents = True


Err:
   
  
  Err.Clear
        Select Case MsgBox("The following error has occured: " & vbCr & vbCr & _
                "Either the website wasn't able to provide a figure," & vbCr & vbCr & _
                "or the import format was incorrect." & vbCr & vbCr & _
                "Do you want to try again?", _
                vbYesNo, "Error")
         
                Case vbYes: Call Spot_Data_Import
                End Select
Exit Sub

End Sub

Can anyone help me improve the vba above or is anyone able to point me in the right direction for the vba to import the Bid and Ask prices via a web query?


Any assistance would be very much appreciated!

Tim

 

[j844929]

Anyone ?

 

[shadow12345]

What about this?

Sub Test()
    Dim IE As Object
     
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.finanzen.net/aktien/realtimekurse.asp?inindex=11" ' should work for any URL
        Do Until .ReadyState = 4: DoEvents: Loop
             
            x = .document.body.innertext
            x = Replace(x, Chr(10), Chr(13))
            x = Split(x, Chr(13))
            Range("A1").Resize(UBound(x)) = Application.Transpose(x)
             
            .Quit
        End With
         
    End Sub

(Norrie's Code not mine)

 

[j844929]

Seems I have two people to thank - you and Norie!

It works, all I have to do is sort the text to columns and I'm there.

Cheers.

Tim