The function below will download a CSV file from a given URL (pass it to the function as a parameter), and then parse it and dump it into a new worksheet in the current workbook (Excel).

You'll need to add a reference to Microsoft VBScript Regular Expressions 5.5 in the VB Editor.

' Script written by David Barrett
' Copyright 2009
' http://www.cedit.biz/
' This script is licensed under the Creative Commons
' Attribution 2.5 Licence
' http://creativecommons.org/licenses/by/2.5/
' 
' You are free to use it for both personal and
' commercial purposes, so long as full attribution
' is given to the author (David Barrett).
'
' This notice must not be removed
'
'
Public Sub DownloadCSV(sURL As String)
   Dim oHttp As Object
   Dim oRegExp As RegExp
   Dim oMatches As MatchCollection
   Dim oMatch As Match
   Dim sCSV As String
   Dim aCSVLines() As String
   Dim aCSVItems() As String
   Dim sCSVField As String
   Dim ws As Worksheet
   Dim i As Long, j As Long
   
   ' Download the file
   Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
   Call oHttp.Open("GET", sURL, False)
   Call oHttp.Send("")
   sCSV = oHttp.ResponseText
   Set oHttp = Nothing
   
   ' We now have the exported file, create new worksheet and dump into it
   Set ws = ActiveWorkbook.Worksheets.Add
   ' Split the file into lines
   aCSVLines = Split(sCSV, vbCrLf)
   
   ' Create regular expression object to parse CSV lines
   Set oRegExp = New RegExp
   With oRegExp
      .Pattern = "(""([^""]*|""{2})*""(,|$))|""[^""]*""(,|$)|[^,]+(,|$)|(,)"
      .Global = True
      .MultiLine = False
   End With
   
   ' Now loop through each line and dump to worksheet
   For i = 0 To UBound(aCSVLines)
      j = 1
      Set oMatches = oRegExp.Execute(aCSVLines(i))
      For Each oMatch In oMatches
         sCSVField = oMatch.Value
         If Right(sCSVField, 1) = "," Then
            ' Strip trailing comma
            sCSVField = Left(sCSVField, Len(sCSVField) - 1)
         End If
         If (Left(sCSVField, 1) = """") And (Right(sCSVField, 1) = """") Then
            ' Strip quotes
            sCSVField = Mid(sCSVField, 2, Len(sCSVField) - 2)
         End If
         ws.Cells(i + 1, j).Value = sCSVField
         j = j + 1
      Next
   Next
End Sub