Open & Import CSV Data from a URL

[PoggiPJ]

I need some help writing a macro that will open a csv file from a URL, then import the data contained in that csv file into one of the worksheets. I will already know the complete URL. I do not need to download or save the CSV file, just pull the data into my workbook.

I would like to make this as transparent to the user as possible. Basically, the user will click on a command button to initiate the process, and be informed once it has completed, barring any error alerts.

Can anyone point me in the right direction, perhaps offer some similar code that i can modify? Thank you.

 

[gth826a]

Tried using the code below, updated to match my needs, but I get a Status 403 response error from the site.
The file (.csv) is of stock quote data from Google (URL below):
qurl = "https://www.google.com/finance/historical?output=csv&q=" & ticker

Any ideas why?

Have a go with this in a new workbook so as not to mess up your data.

Red text is the stuff you need to change, file URL, destination cell, etc.

No worksheet selection included, code is for the active sheet.

Not sure how well this would work as I couldn't find a web based .csv file so had to compromise.

Let me know what happens, I'll look over any problems later.

JB

Sub mycsvimport()
Application.ScreenUpdating = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.ferc.gov/docs-filing/eqr/soft-tools/sample-csv/transaction.csv" _
        , Destination:=Range("$A$1"))
        .Name = "transaction"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
        (20, 1), Array(21, 1), Array(22, 1)), TrailingMinusNumbers:=True
        Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub