Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

This is a discussion on Macro to extract from a table on website, web query from import data will not work. within the Excel Questions forums, part of the Question Forums category; Hi, I have found some answers to my question, but I dont understand the whole code. I need to go ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    32

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

    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

  2. #2
    New Member
    Join Date
    Nov 2008
    Posts
    32

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

    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

  3. #3
    New Member
    Join Date
    Sep 2009
    Posts
    38

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

    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.

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

  4. #4
    New Member
    Join Date
    Nov 2008
    Posts
    32

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

    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

  5. #5
    New Member
    Join Date
    Sep 2009
    Posts
    38

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

    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.

    Code:
             '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
    Code:
    Set rng = Sheets("Sheet1").Range("A2")

    Hope this is the right date!


    atb
    sumuwin

  6. #6
    New Member
    Join Date
    Nov 2008
    Posts
    32

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

    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!

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,075

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

    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.

  8. #8
    New Member
    Join Date
    Sep 2009
    Posts
    38

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

    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

  9. #9
    New Member
    Join Date
    Nov 2008
    Posts
    32

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

    Hi John,
    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.

  10. #10
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,075

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

    Quote Originally Posted by sumuwin View Post
    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):
    Code:
    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).

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com