Open & Import CSV Data from a URL

PoggiPJ

Active Member
Joined
Mar 25, 2008
Messages
330
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Have you tried recording one while you do the process manually?

That's probably the easiest way to get the desired result.

Let me know if you need any help with it, shouldn't take too long.

JB
 
Upvote 0
No, haven't tried that. I want do do it behind the scenes. Opening the file, importing the data, etc. I think it will require some special functions that I am not able to "record".
 
Upvote 0
Ok, not a problem, you can do it all hidden it's just a question of using the right method.

Will the import need to be entered to a new sheet, overwrite an existing one or append to an existing one?
 
Upvote 0
Hi Jason,
The import will replace data already in the worksheet. The worksheet contains about 12000 rows. Each row is a part number, the columns being description, price breaks, and other such data.
 
Upvote 0
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



Rich (BB code):
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
 
Upvote 0
Thank you Jason.
I've copied the code, but I'd also like to learn a little bit about what's going on here. I need to lookup and understand what the QueryTables, and TextToColumns are actually doing. This is great help. Thank you. I will be back. I just need a little sleep first. :cool:
 
Upvote 0
Jason. I have placed a sample csv file on an old website. The URL is http://www.qoroad.org/sample%201.csv Your code sample worked flawlessly. Thank you.

I haven't been able to find anything on QueryTables, but I have learned about TextToColumns.
 
Last edited:
Upvote 0
I'm a little buried under things at the moment Peter, but I'll do an expaination of the code for you when I get home later.

Good to hear it worked so well :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top