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:
And the resulting data, import to sheet2:
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.
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.
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog
thanks for sharing this tip.
My problem is that the data my call returns is not comma separated but separated by the | character:
Is there a way to modify the import to process data structured like this?
Thanks for your support!
This looks like it is worth a try:
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 🙂
Thanks for the comment, and glad it helped!
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!
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.
Filename part 1 https://www.nseindia.com/archives/equities/mkt/MA
day 03 Wensday
month 08 31
Filename part 2 .csv
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
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
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:
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.
thank you very much
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?
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.
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 .
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.
Filename part 1 http://www.nseindia.com/content/nsccl/fao_participant_oi_
day 16 Tuesday
month Feb 29 02
Filename part 2 .csv
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
Hi Vijay, see:
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.
thank you i have understood ….
Thank you very much Sir, I was trying this before 3 month and finely you solve my problem.
hi sir once again i need help
the file is zip format but it has only one csv file inside and i need to copy in my download sheet help me