This is not a good question. A good question would probably have been answered in minutes.
You can assume that any one who visits the Excel-VBA page of Stack Overflow with the intention of answering questions is familiar with Excel VBA. You should not assume that they are familiar with anything else. You assume a potential answerer is familiar with the word “ticker” and knows the parameters for a Yahoo finance download.
Your title is “CSV text downloading all in one line instead of new lines”. You say: “For some reason the CSV file is downloading in the same line as supposed to beginning a new line for everyday in the share price history.” In fact, the title and the statement are untrue.
Having run your code and looked at the saved file, the problem is obvious. Perhaps if I have given some thought to your wish to use the Input# statement, I would have guessed the cause immediately.
Some features of Windows go back to the days of MS-DOS. In particular, NotePad and Input# only work with MS-DOS style text files. In those days, output devices worked like typewriters: CR meant return to beginning of current line and LF meant advance one line. All text files used CRLF (Carriage return followed by Line feed) as the line delimiter because this meant they could be output directly to printers, consoles, etc.
Most parts of Windows still use CRLF as the default line delimiter but will accept LF which is the standard everywhere else. NotePad and Input# do not.
If you have opened the downloaded file with a text editor that accepts LF as a delimiter, you would have seen that the details for each day are on their own line. You might not have known why but you would not have asked such a misleading question.
What should you do?
Choice 1
Add something like this to your routine:
Dim InxLine As Long
Dim Line() As String
Line = Split(http.responseText, vbLf)
For InxLine = 0 To UBound(Line)
Debug.Print Line(InxLine)
Next
This code simply output each line to the Immediate Window. You will have to replace Debug.Print Line(InxLine) with appropriate code to process the lines.
Choice 2
If you want to save the CSV file, you can keep your existing routine unchanged and add something like this:
Sub TestOpen()
Dim WbkThis As Workbook
Set WbkThis = ThisWorkbook
Workbooks.Open ("C:\Users\Tony\Desktop\goog.csv")
' goog.csv is now the active workbook which have a single worksheet named "goog"
Sheets("goog").Move After:=WbkThis.Sheets(WbkThis.Worksheets.Count)
' because the only sheet within workbook goog.csv has been removed, the workbook is
' automatically closed.
End Sub
I have downloaded a CSV file for Google to my desktop. This macro opens that CSV file as a workbook and moves the worksheet containing the data to the workbook containing the macro. The worksheet “goog” is ready to process as required.
Choice 3
If it is really important that the routine create a file that uses CRLF as a line terminator:
Add the following to your existing routine after the download and before the write to disc:
Dim FileText As String
FileText = Replace(http.responseText, vbLf, vbCr & vbLf)
Replace:
ostream.Type = 1
ostream.Write http.responseBody
by:
ostream.Type = 2
ostream.Writetext FileText
This will give a file that NotePad and other ancient programs can split into lines.
