Web Query Problem - HELP!

j844929

Active Member
Joined
Aug 18, 2002
Messages
423
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:

Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
What about this?

Code:
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top