Looping Help needed with date

[zaska]

Hello,

I have the following macro which runs based on the date in Cell "G2" of sheet1. For Each date in Cell G2 ,the below macro downloads one single file for that date.

Can anyone help me how to implement loop in the macro so that i could download files between two dates. Say i want to download files from 01022012 to 22022012.

Right now i am changing the date in Cell G2 each time and running the macro for each date..if any loop is implemented it would save me lot of time.

Thanks
Zaska

Public Sub NseIndices()
Dim arrURL() As String
Dim dtmDate As Date
Dim c As Range
Dim i As Long
Dim s As String
Dim bArray() As Byte
Dim hFile As Integer
Dim strLocalFile As String
Dim sTemp As String
Dim iPtr As Long

 
Dim oXMLHTTP As MSXML2.XMLHTTP '(reference to C:\Windows\System32\msxml2.dll for 32 bit systems)

    '-------------------------------------------------------------------------------
    'ENTER CONSTANTS HERE - NOTHING ELSE *SHOULD* NEED TO BE CHANGED
    Const CELL_WITH_DATE As String = "G2"
    Const RANGE_WITH_SYMBOLS As String = "N1:N19"
    Const SAVE_DIRECTORY As String = "E:\Macros\Output\" 'end with forward slash
    '-------------------------------------------------------------------------------
    
    dtmDate = Range(CELL_WITH_DATE).Value '//Date
    strLocalFile = SAVE_DIRECTORY & "NIN" & Format(dtmDate, "ddmmyyyy") & ".csv"

    For Each c In Range(RANGE_WITH_SYMBOLS) '//11 cells with symbols
        If Len(c.Value) > 0 Then
            ReDim Preserve arrURL(1 To 2, 0 To i)
            arrURL(1, i) = "http://www.nseindia.com/content/indices/histdata/"
            arrURL(1, i) = arrURL(1, i) & UCase(c.Value)
            arrURL(2, i) = UCase(c.Value)
            arrURL(1, i) = arrURL(1, i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy") & ".csv"
            i = i + 1
        End If
    Next c
    
    'download the file from the web to the hardrive
    'loop through symbols in turn
    Set oXMLHTTP = New XMLHTTP
    hFile = FreeFile
    Open strLocalFile For Binary As #hFile
    For i = 0 To UBound(arrURL, 2)
        oXMLHTTP.Open "GET", arrURL(1, i), False
        oXMLHTTP.send
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop
        
        bArray = oXMLHTTP.responseBody
       

        sTemp = ""

        For iPtr = LBound(bArray) To UBound(bArray)

          sTemp = sTemp & Chr(bArray(iPtr))

        Next iPtr

        sTemp = Replace(sTemp, "Date", "")

        sTemp = Replace(sTemp, "Open", "")

        sTemp = Replace(sTemp, "High", "")

        sTemp = Replace(sTemp, "Low", "")

        sTemp = Replace(sTemp, "Close", "")

        sTemp = Replace(sTemp, "Shares Traded", "")

        sTemp = Replace(sTemp, "Turnover (Rs. Cr)", "")

        If Left(sTemp, 2) = Chr(34) & Chr(34) Then

          sTemp = Mid(sTemp, 3)

        End If

        Do While Left(sTemp, 3) = "," & Chr(34) & Chr(34)

          sTemp = Mid(sTemp, 4)

        Loop

        If Left(sTemp, 1) = Chr(10) Then sTemp = Mid(sTemp, 2)

        ReDim bArray(Len(sTemp) - 1)

       

        Put #hFile, , arrURL(2, i) & "," & sTemp

 
              
       Next i

Handler:
On Error Resume Next
Close #hFile
Set oXMLHTTP = Nothing

End Sub

 

[carpy1985]

Make this sub a function and then create a new macro with a for each loop that passes the workbook date into the function.