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]

John W,
I tried your code and it seemed to work, but now all of a sudden I get Subscript is out of range? I dont understand why it is doing that because it just worked. I tried to delete the code and copy and paste your code back in and now i get the error. Can you take a look and let me know?

Thank you.

 

[jsegraves]

Figured it out. I forgot and renamed Sheet1 so I had to change the code.

Thank you.

 

[jsegraves]

Ok here is an interesting issue with John W code. It works on my computer that has Excel 2007, but when I sent the file to the person that will be doing it he has excel 2003. When he clicks the button he gets a message box that pops up and says 400. Why is this doing that? I added a few macros at the end that I wanted to run and if anyone has suggestions to combine it so it is all one step let me know.

Sub Get_WSJ_FX_data()
 
    Dim thisWb As Workbook
 
    Set thisWb = ActiveWorkbook
 
    Workbooks.OpenText Filename:= _
        "[URL]http://online.wsj.com/mdc/public/npage/2_3045-forex-forexcsv.html[/URL]", 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("Rates sheet")
        Sheets(1).UsedRange.Copy .Range("B1")
    End With
 
    ActiveWorkbook.Close SAVECHANGES:=False
 
Call delete_last_line
Call deselect_na
Call hide
 
End Sub

Thanks!

 

[jsegraves]

Ok here is the updates Code:

I figured it out that there was an issue with the autofilter, but why does it work in 2007 and not 2003? The goal of the auto filter is to display all the data except the #N/A. If you have a way to write to code to just delete it and not use auto filter that is fine too.

Sub Get_WSJ_FX_data()
    
    Dim thisWb As Workbook
    On Error GoTo Errorcatch
    Set thisWb = ActiveWorkbook
    
     
   'This is to clear data from the last time it was downloaded'
            Selection.AutoFilter
             Columns("B:U").Select
                Selection.Clear
    
    Workbooks.OpenText Filename:= _
        "[URL]http://online.wsj.com/mdc/public/npage/2_3045-forex-forexcsv.html[/URL]", 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("Rates sheet")
        Sheets(1).UsedRange.Copy .Range("B1")
    End With
    
    ActiveWorkbook.Close SAVECHANGES:=False
    
'This is to clear the last two line that are not needed'
                Range("A75:G80").Select
                   Selection.ClearContents
                        Range("A6").Select
'This is to show only the codes we want to see'
   Selection.AutoFilter
    ActiveSheet.Range("$A$6").AutoFilter Field:=1, Criteria1:=Array("AR", _
        "AU", "BR", "CA", "CH", "CZ", "DK", "EG", "EU", "GB", "HK", "ID", "IN", "JP", "KR", "MX", "MY" _
        , "PH", "SE", "SG", "TH", "TW"), Operator:=xlFilterValues
'This is to Hide the data the is useful but not viewed. I didnt want to delete the data so I just hid it'
                    Columns("D:H").Select
                        Range("H1").Activate
                        Selection.EntireColumn.Hidden = True
                        Range("A6").Select
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub

Thanks!

 

[jsegraves]

Got the answer: Criteria1:="<>#N/A"