Web Data Import to excel file

[kahmad]

Good day,
I have an excel file which has different sheets for The ROE.
All I need is a process or a macro which on the press of a button or upon
opening of the file, reads when was the last update date of the file, opens
oanda.com- Fx history page, downloads the data for Each currency in the file and paste it in the relevant columns/Rows.
I am not able to do it with a simple web query as the process should be intelligent enough to Know the last date of update from the excel file, put in the dates in the Oanda website, then wait for the result and then paste it back to the excel file.
I have different sheets for different currencies so it will have to keep changing the Currencies as well.

Best regards

 

[mjbeam]

This should get you started. If you bring up the http://oanda.com/convert/fxhistory webpage and view the source code for the page there are several Java objects of interest. These are the ones you will need for your application:

date1 The start date
date The end date. Probably don't need to worry about this one since the default is today
exch2 The 3 letter currency code for the currency to convert. For example USD
expr2 The 3 letter currency code for the currency to convert to

The rest of the objects already have defaults set that should work.

Go to the VBA editor and create a userform and put a webbrowser control on it. Then add the following code to your project:

Dim navdone As Boolean
Dim mdata As String

Private Sub UserForm_Activate()

    Do
        DoEvents
    Loop Until navdone = True
    
    Do
        DoEvents
    Loop Until InStr(WebBrowser1.Document.documentelement.innerhtml, "FXHistory ©1997-2003 by OANDA Corporation") <> 0

    Currency1 = "USD"
    Currency2 = "AUD"
    
    Startdate = "08/01/03"
    
    WebBrowser1.Document.all("exch2").Value = Currency1
    WebBrowser1.Document.all("expr2").Value = Currency2
    WebBrowser1.Document.all("date1").Value = Startdate

    navdone = False
    WebBrowser1.Document.all("SUBMIT").Click
    
    Do
        DoEvents
    Loop Until navdone = True
    
    Do
        DoEvents
    Loop Until InStr(WebBrowser1.Document.documentelement.innerhtml, "Conversion Table:") <> 0
    
    mydata = WebBrowser1.Document.documentelement.innerhtml

    'Add code to parse your currency exchange data from the mydata string here

End Sub

Private Sub UserForm_Initialize()

    navdone = False
    
    WebBrowser1.Navigate "http://oanda.com/convert/fxhistory"

End Sub

Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant)

    navdone = True

End Sub

-Mike

 

[chs245]

I'm having problems replicating this example in Excel. I manage to load the URL in a webbrowser control , but I can't catch the event fire for navigatecomplete ? Any ideas why that could be ?

thx,

Oliver

 

[marcodomingues]

How can i copy the result to the excel

 

[marcodomingues]

can some one help me

 

[NateO]

Hello Oliver,

I'm having problems replicating this example in Excel. I manage to load the URL in a webbrowser control , but I can't catch the event fire for navigatecomplete ? Any ideas why that could be ?

Example 1:
http://mrexcel.com/board2/viewtopic.php?t=25039&start=10

Example 2:
http://mrexcel.com/board2/viewtopic.php?t=75432

I like to test both the readystate and busy properties. The ActiveX control also has these properties.

 

[chs245]

Hello Oliver,

I'm having problems replicating this example in Excel. I manage to load the URL in a webbrowser control , but I can't catch the event fire for navigatecomplete ? Any ideas why that could be ?

Example 1:
http://mrexcel.com/board2/viewtopic.php?t=25039&start=10

Example 2:
http://mrexcel.com/board2/viewtopic.php?t=75432

I like to test both the readystate and busy properties. The ActiveX control also has these properties.

Hi Nate

I'm still struggling with catching events related to the webcontrol in Excel. I use the following simple code, with a webcontrol (WebBrowser1) in Sheet1. The page loads correctly, but i do not get the message window "Download Complete", which should be fired after the page loads.
Could you have a look at where my mistake lies ?

many thanks,

OLiver


Code:

Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, ByVal URL As Variant)
MsgBox ("DOWNLOAD COMPLETE")
End Sub

Sub UserForm_Initialize_new()
http_link = "http://my.yahoo.com"
Sheet1.WebBrowser1.Navigate2 http_link

End Sub