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")
.ClearContents
.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
Newton Excel Bach, not (just) an Excel Blog 
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.
Bill
LikeLike
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.
Thanks,
George
LikeLike
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog
Hi,
thanks for sharing this tip.
My problem is that the data my call returns is not comma separated but separated by the | character:
|China|22|
|United States|18|
|Belgium|18|
|Netherlands, The|17|
Is there a way to modify the import to process data structured like this?
Thanks for your support!
LikeLike
This looks like it is worth a try:
http://office.microsoft.com/en-au/excel-help/import-or-export-text-txt-or-csv-files-HP010099725.aspx#BMchange_the_separator_in_all_.csv_text
LikeLike
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 Morningstar.com into Excel. Thought this will take me weeks. Thanks to you, less then an hour 🙂
LikeLike
Thanks for the comment, and glad it helped!
LikeLike
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.
Website: https://fred.stlouisfed.org/graph/?chart_type=line&s%5b1%5d%5bid%5d=CPILFENS&s%5b1%5d%5btransformation%5d=pc1#
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!
LikeLike
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.
LikeLike
Filename part 1 https://www.nseindia.com/archives/equities/mkt/MA
day 03 Wensday
month 08 31
year 16
Filename part 2 .csv
3-8-16
Full Filename https://www.nseindia.com/archives/equities/mkt/MA030816.csv
i have imported data but its importing only first two lines in sheet 2
03-08-16
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
LikeLike
It actually opens the whole file, but only imports the first two lines. I have fixed the routine to copy the data so it should copy everything:
http://interactiveds.com.au/software/web-download3.xlsb
I also changed the formula for the link address to get the date in the right format (I guess you must have done that yourself for your download). Just enter the date as text in cell B3.
I will post an update in the next few days, but if you have any problem with the version linked above, please let me know.
LikeLike
thank you very much
LikeLike
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,
LikeLike
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.
LikeLike
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 .
LikeLike
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.
LikeLike
http://www.nseindia.com/content/nsccl/fao_participant_oi_06082013.csv
Filename part 1 http://www.nseindia.com/content/nsccl/fao_participant_oi_
day 16 Tuesday
month Feb 29 02
year 2016
Filename part 2 .csv
16-Feb-2016
Full Filename http://www.nseindia.com/content/nsccl/fao_participant_oi_16022016.csv
MY worksheet name is OIDATA and i need to download the data in same work sheet
LikeLike
Hi Vijay, see:
http://interactiveds.com.au/software/web-download4.xlsb
The only changes I have made are:
The named range “importrange” has been moved to Sheet1
I have changed the code to read the name of the active sheet and return to that sheet after importing the data.
So you should be able to just change the sheet name to anything you want, and it should still work.
LikeLike
thank you i have understood ….
LikeLike
Thank you very much Sir, I was trying this before 3 month and finely you solve my problem.
LikeLike
hi sir once again i need help
https://www.nseindia.com/content/historical/DERIVATIVES/2016/DEC/fo26DEC2016bhav.csv.zip
the file is zip format but it has only one csv file inside and i need to copy in my download sheet help me
LikeLike