Download Excel Workbook from IE copy and paste into existing Workbook

Hi all,
i'm attempting to use VBA to have internet explorer open, navigate to a website open the excel workbook copy & paste the sheet into an existing open workbook (essentially replacing all data with most recent data) and close the workbook (the one from which data is coming from) i've gotten to the point where i have the option to open/save/cancel the download but not sure where to go from there. i've also attached the workbook that i want it to go in. basically just copy and paste into "Rate" worksheet overwriting previous data.

any help is appreciated!

Sub APORTable()
   

    Dim oIE As InternetExplorer
    Dim oExcel As Excel.Application

    Set oIE = CreateObject("InternetExplorer.Application")
    oIE.Navigate ("http://www.ffiec.gov/ratespread/YieldTableFixed.CSV")
    
    Do While oIE.Busy: DoEvents: Loop
    Do While oIE.readyState <> 4: DoEvents: Loop
    
    oIE.Visible = True
    
End Sub

Hello kboy1289,

Here is faster an easier way to it.

Sub ImportCSVFromWeb(ByVal URL As String)

    ' Summary: Imports a CSV file from the web to tthe active worksheet.
    ' Written: December 16, 2013
    ' Author:  Leith Ross
    
    Dim i As Long
    Dim n As Long
    Dim r As Long
    Dim Text As String
    Dim TextLine As String
    Dim TextArray As Variant
    
        With CreateObject("MSXML2.XMLHTTP")
            .Open "GET", URL, True
            .send
            While .readyState <> 4: DoEvents: Wend
            Text = .responsetext
        End With
        
        Text = GetServerResponse(URL)
        
        Do
            i = n + 1
            n = InStr(i, Text, Chr(10))
            If n = 0 Then Exit Do
            
            TextLine = Mid(Text, i, n - i)
            
            If InStr(1, TextLine, ",") Then
                TextArray = Split(TextLine, ",")
                Range("A1").Offset(r, 0).Resize(1, UBound(TextArray)).Value = TextArray
            Else
                Range("A1").Offset(r, 0).Value = TextLine
            End If
            
            r = r + 1
        Loop
            
End Sub

Sub GetYieldTable()

    Call ImportCSVFromWeb("http://www.ffiec.gov/ratespread/YieldTableFixed.CSV")

End Sub

Hello kboy1289,

Did you run the macro GetYieldTable first?

Did you receive an error message?

@ Marc L,

Nice solution. I had forgotten "Workbook.Open" would take a URL as a valid file option.