Wonderful, it returns data as 2012-07-05 but we need 20120705.
We have to remove -
???
Here is code for easy ref.
------------
Sub MakeCSV()
Dim fs As Object, a As Object, y As Object, C As Integer, i As Integer, r As Integer, S As String, t As Variant, CellValue As String
'Create a file object for writing
Set fs = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
MkDir ("C:\RT") 'This will create a folder RT in C Drive.
FileName = "C:\RT\MyCSV.txt" 'This file is used to write quotes
Set a = fs.CreateTextFile(FileName, True) 'Here we create the file MyCSV.csv
If NSENOW = "Yes" Then
a.writeline "<TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>, <LOW>,<CLOSE>,<VOL>,<OPENINT>"
MyBook.Sheets("Now").Select 'Selects sheet containing quotations
For r = 7 To Range("A65536").End(xlUp).Row
C = 1
While Not IsEmpty(Cells(r, C))
If C = 1 Then
CellValue = Cells(r, C).Value & ",I," & Format$(Date, "yyyy/mm/dd") ' ' (Insert comma and I after ticker name)
ElseIf C = 3 Then 'Insert Last Traded Price 4 times for O,H,L,C
CellValue = Cells(r, C).Value & "," & Cells(r, C).Value & "," & Cells(r, C).Value & "," & Cells(r, C).Value
ElseIf C = 4 Then
CellValue = Cells(r, C).Value - Vol(r, 1)
Vol(r, 1) = Cells(r, C).Value
' Debug.Print Cells(r, 1).Value & " - " & CellValue & " - " & Vol(r, 1)
Else
CellValue = Cells(r, C).Value '
End If
' CellValue = Cells(r, C).Value '
S = S & CellValue & "," 'Add contents of current cell to string 's' and a comma
C = C + 1
Wend
a.writeline S 'write line
Next r
End If
-------------