Macro to extract from a table on website, web query from import data will not work.

[jsegraves]

Hi,
I have found some answers to my question, but I dont understand the whole code.

I need to go to: http://online.wsj.com/mdc/public/page/2_3021-forex.html and import all the data into excel every morning. I need the currency rate table, but the export to excel wont work for this. So I am trying to write a macro to do this, but I get lost looking at everyone else code. I can write the macro up to the point it opens an IE browser and goes to the site. (I really dont need this part because I just want it to import the data). How would the code look for this site?

Thanks,
Jill

 

[jsegraves]

Anyone? I just dont understand how to tell the macro to select the table, copy the info and put it into excel.

Any help is appreciated.

Thanks,
Jill

 

[sumuwin]

Hi

here's one way if you don't want to click VIEW SPREADSHEET

The page seems to be quite slow so I have written the code to download the webpage to a file (c:\FXSample.htm) and then read this file instead of directly from the webpage.

'*********************************************
'Requires reference to MS HTML object library
'*********************************************

Sub getFXTable()

'First download page to C:\FXSample.htm
Dim myurl As String
myurl = "http://online.wsj.com/mdc/public/page/2_3021-forex.html"

Dim myfile As String
myfile = "C:\FXSample.htm"

'Call functions
Call fncGetSource(myurl)
Call fncWriteSource(myurl, myfile)

'Use FXSample.htm as your webpage
myurl = myfile

Dim myobj As New MSHTML.HTMLDocument
Dim doc As MSHTML.HTMLDocument
Dim mytable As MSHTML.HTMLTable
Dim mytablecell As MSHTML.HTMLTableCell


Set doc = myobj.createDocumentFromUrl(myurl, vbNullString)

Do While doc.readyState <> "complete"
    DoEvents
Loop

Set mytable = doc.all.tags("table").Item(3)

Set rng = Sheets("Sheet1").Range("A1")
For Each r In mytable.Rows
    For Each c In r.Cells
        rng.Value = "'" & c.innerText
        Set rng = rng.Offset(0, 1)
        i = i + 1
    Next c
    
    myrow = myrow + 1
    Set rng = rng.Offset(1, -i)
    i = 0
Next r

Set doc = Nothing


'Delete FXSample.htm
Kill myfile


End Sub


'***************************************************
'This function will get the web page source
'****************************************************
Public Function fncGetSource(sUrl As String) As String
    
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", sUrl, False
    oXHTTP.send
    fncGetSource = oXHTTP.responseText
    Set oXHTTP = Nothing
    
End Function

'***************************************************
'This function will write the web page source to file
'****************************************************

Public Function fncWriteSource(sUrl As String, sFile As String) As Boolean

    
    FileNum = FreeFile
    
    'write the web page source to the temp file
    Open sFile For Output As FileNum
    Print #FileNum, fncGetSource(sUrl)
    Close FileNum

    fncWriteSource = True

End Function

The process should only take a couple of seconds and the file will be deleted when finished.


atb
sumuwin

 

[jsegraves]

Sumuwin,
You are awesome! This is so great. I had to go to the reference dialog box and check the HTML object library, but that was really the only thing I had to do.

Question: How would I add the date that is displayed at the top of the table. I would like to use this as the date reference so I know when the site produced these rates. For example if you go to the site: http://online.wsj.com/mdc/public/page/2_3021-forex.html the date is Thurday Sept 23, 2010 because the market isnt closed yet. Can you explain to me how to add it? I want to understand what you did. I am not sure how to add this part to the look up. I tested to see if that was Table 1 but I got the wrong result. So I am not sure. Sorry for all the questions still new to VBA coding.

Thanks,
Jill

 

[sumuwin]

Hi Jill

if you right click on the webpage you should have a Source, View Source or similar option which will allow you to view the HTML of the page.

Looking at the source I found the following string which seems to hold the date your after.

******** language="javascript" type="text/javascript">var tableDate="09/23/2010";*********>

I then used the code below to search the document's outer HTML for "tableDate=" and then pick up the date.

         'Add new code immediately after this section
               Do While doc.readyState <> "complete"
                    DoEvents
               Loop

'*****New Code
mystr = doc.body.outerHTML
'Search for "tabledate="
If InStr(mystr, "tableDate=") > 0 Then
    'Find start position of "tabledate=" then add 11 chars to get
    'start of date - date is 10 chars long
    mypos = InStr(mystr, "tableDate") + 11
    mydate = Mid(mystr, mypos, 10)
End If

Sheets("Sheet1").Range("A1") = mydate

You will also need to change the output range to A2

Set rng = Sheets("Sheet1").Range("A2")

Hope this is the right date!


atb
sumuwin

 

[jsegraves]

Sumuwin

It totally worked! You saved me tons of time and thank you for explaining how you found the info on the date and table. I will definately used this in the furture for other projects. Thank you so much again!

 

[John_w]

There's no need for all this HTML parsing, because the web page has a 'VIEW AS SPREADSHEET' link which Excel can download directly. Copy that link, then File - Open - paste the link (URL) and Excel opens the Text Import Wizard. The data is tab-delimited. Then copy the sheet or specific rows to your workbook. Do these steps with the macro recorder if you want to automate it.

 

[sumuwin]

I think it's a bit arrogant of you to determine the OP's need.

I pointed out in my first post that the View Spreadsheet link could be clicked.

Your post was totally unnecessary.



sumuwin

 

[jsegraves]

Hi John,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thank you for your reply. I was aware of the view as spreadsheet option, but there are a few more things that I need to have happen within the workbook. So it is much easier to have the data downloaded to existing workbook. Also I wanted to remove the steps of having to open the webpage click the view as spreadsheet and copy the info into another work book. The totally goal is to minimized tedious tasks and make this task simpler for a non excel person to use. <o></o>

 

[John_w]

I think it's a bit arrogant of you to determine the OP's need.

I pointed out in my first post that the View Spreadsheet link could be clicked.

Your post was totally unnecessary.

Sorry if my post offended you, but I don't see how I have determined the OP's need any more than you have. We are both answering the same request with different ideas; I merely described a simpler technique which I think also satisfies the OP's need.

My post didn't say click the View Spreadsheet link. Perhaps I should have posted the code which is generated by the steps I described (slightly edited):

Sub Get_WSJ_FX_data()
    
    Dim thisWb As Workbook
    
    Set thisWb = ActiveWorkbook
    
    Workbooks.OpenText Filename:= _
        "http://online.wsj.com/mdc/public/npage/2_3045-forex-forexcsv.html", origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
        
    With thisWb.Sheets("Sheet1")
        Sheets(1).UsedRange.Copy .Range("A1")
    End With
    
    ActiveWorkbook.Close SAVECHANGES:=False
    
End Sub

PS maybe you won't thank me for pointing out that your code downloads the web page twice because there are 2 calls to fncGetSource(myurl).