Automatic download of csv files

I was recently asked if I could assist with preparing a spreadsheet to automatically download .csv files from an internet location, with the filename incorporating the date of issue.  The process turned out to be quite straightforward, and works well even with huge files (860,000+ rows), so I am sharing it here.  The file may be downloaded from Web-Download1.xlsb, and includes a link to a csv file with some ancient stock data for trial purposes.

The download macro carries out the following steps:

  • Read the filename
  • Open the file
  • Read the data to a Variant array
  • Close the file
  • Erase old data
  • Resize the data range for the new data
  • Write the new data to the spreadsheet
Sub ImportFromWeb()
Dim FullName As String, ImpName As String, DataRange As Variant, NumRows As Long, NumCols As Long

FullName = Range("fullname").Value

  Workbooks.Open Filename:=FullName
  DataRange = ActiveCell.CurrentRegion.Value
  NumRows = UBound(DataRange)
  NumCols = UBound(DataRange, 2)
  ImpName = Application.ActiveWorkbook.Name
  Workbooks(ImpName).Close SaveChanges:=False

  With Range("importrange")
  .Resize(NumRows, NumCols).Name = "importrange"
    End With

  Range("importrange").Value = DataRange

End Sub

The screenshot below shows the generation of the full filename and path, including the specified date:

Generation of filename

And the resulting data, import to sheet2:

Data imported from Internet


This entry was posted in Excel, VBA and tagged , , , . Bookmark the permalink.

19 Responses to Automatic download of csv files

  1. Bill Harvey says:

    So, now I can thank Doug for this effort and suggest one change that I have made and not got round to sending back:.

    It is more convenient to have the date as day month year in separate cells formatted as text from which the text format can be assembled. I am aiming my spreadsheet at a relatively naive user and need to minimise the opportunity for error in his input. Formatting the cells as text means you can type in the 03 for 3rd of the month and it all stays there.

    The reason for 864000 rows is that is the number of tenths of a second in a day and we are recording position of the four corners of a tuned mass damper at that frequency. The spreadsheet I am building is designed to allow the user to pic a short period of the detail and look at that. The natural frequency of the bridge is about 0,5 Hz nd it takes about 5 or 6 cycles of buildup to get the damper going then another 5 or 6 to damp it out again.

    I will post a suitable graph when I get a minute.



  2. George says:

    Thanks for sharing this. Fantastic macro.

    I would like to ask if it can be modified from a start date to an ending date. Can you make it download files this way?

    And a last one. If it can be modified e.g. to download 10 .csv at once in different sheets in the same workbook this way.



  3. Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog

  4. Philipp says:


    thanks for sharing this tip.
    My problem is that the data my call returns is not comma separated but separated by the | character:

    |United States|18|
    |Netherlands, The|17|

    Is there a way to modify the import to process data structured like this?

    Thanks for your support!


  5. Patrick says:

    Thank you for your great work. I’ve made some changes to get full Cash Flow, Balance Sheet, Income Statement and Key Ratios of a desired quote from into Excel. Thought this will take me weeks. Thanks to you, less then an hour🙂


  6. Phoebe says:

    Hi, thank you so much for your work. I used the code to pull data from federal reserve and it worked flawlessly. Yet, I was trying to use the same code for the CPI website, and I could not get it to work.


    I used the direct link to download CSV file. The link will download automatically when I open it in browsers, yet when I run the macro in excel, I have the following errror:

    Run-time error ‘1004’:
    Method ‘Open’ of object ‘Workbooks’ failed

    I’m not sure what the problem is and any help would be appreciated!

    Thank you so much!


    • dougaj4 says:

      Phoebe – I don’t think I can help with automatic download from the Web. For the macro to work you need to enter the full path to a csv file somewhere on the Web. It looks like the site you linked to automatically generates a csv file and downloads it, but I can’t see any way to do that from the spreadsheet.


  7. vijay says:

    Filename part 1
    day 03 Wensday
    month 08 31
    year 16
    Filename part 2 .csv
    Full Filename

    i have imported data but its importing only first two lines in sheet 2
    The Nifty witnessed an intraday movement of about 105.85 points.The closing index was 0.91 % down as compared to the previous closing index.

    i need that full page csv file


  8. Franco says:

    Hi, thanks for posting this code, it is very helpful.

    I am very new to VBA coding and I copied the code and the values on the excel sheet exactly as they are and I could not get this to work. Are there any values that I should change, like sheet names or workbook names, to get it to work?

    Thank you,


    • dougaj4 says:

      Sorry for the delay in responding.
      The easiest thing to do is to use the original file saved with a new name.
      If you want to copy the code to another file you will also need to set up two named ranges. Open the name manager in the original file to get names and locations.


  9. vijay says:

    hi sir i need to download the file in same sheet can you help me, i have download different links in same work book so i need to import in same sheet .


    • dougaj4 says:

      If I understand what you want to do correctly, the simplest thing to do would be to copy the imported data elsewhere on the spreadsheet, to the right of the import range, then you can import the next set of data.

      If that does not answer your question, could you give more details of what you want to do.


  10. vijay says:

    Filename part 1
    day 16 Tuesday
    month Feb 29 02
    year 2016
    Filename part 2 .csv
    Full Filename

    MY worksheet name is OIDATA and i need to download the data in same work sheet


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s