Confused with Put in VBA

[zaska]

Hi ,

The Following is a Part of a VBA Code

Dim c As Range
For Each c In Range(RANGE_WITH_SYMBOLS) '//11 cells with symbols
        If Len(c.Value) > 0 Then
            ReDim Preserve arrURL(0 To i)
            arrURL(i) = "http://www.nseindia.com/content/indices/histdata/"
            arrURL(i) = arrURL(i) &[B][COLOR=Red] UCase(c.Value)[/COLOR][/B]
            arrURL(i) = arrURL(i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy") & ".csv"
            i = i + 1
        End If
    Next c

Set oXMLHTTP = New XMLHTTP
    hFile = FreeFile
    Open strLocalFile For Binary As #hFile
    For i = 0 To UBound(arrURL)
        oXMLHTTP.Open "GET", arrURL(i), False
        oXMLHTTP.send
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop
        
        bArray = oXMLHTTP.responseBody
        
    [B]    [COLOR=Red]Put #hFile, , arrURL(i)[/COLOR][/B]
        Put #hFile, , bArray
              
       Next i

In the Above Code I want to put only the UCase(c.Value) in column "A" not the entire arrURL(i) string.

How can i Modify this code to get the result and alos is it possible to modify the bArray too?

Thanks

 

[zaska]

Thank you for the kind help

But this code didn't work for me.

can i request you one thing.

Plz make changes to barray in the below code to solve the problem

Public Sub downloadNse()
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 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 = "B1"
    Const RANGE_WITH_SYMBOLS As String = "A1:A19"
    Const SAVE_DIRECTORY As String = "C:\Macros\NSEIndices\" 'end with forward slash
    '-------------------------------------------------------------------------------
    
    dtmDate = Range(CELL_WITH_DATE).Value '//Date
    strLocalFile = SAVE_DIRECTORY & Format(dtmDate, "dd-mm-yyyy") & "_.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
      
        
        Put #hFile, , arrURL(2, i)
        Put #hFile, , bArray
              
       Next i

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

End Sub

PS don't think that i dismissed your code...i will try both and choose the one without problems.

thank you verymuch norie

 

[Norie]

zaska

Why do you insist on getting some sort of 'fix' for barray?

Especially when there might be other approaches that might avoid the need for using it in the first place.

Also, what do you mean by 'doesn't work'?

That's a pretty vague statement and could mean a lot of things.

Were there errors?

Were there no errors, but the code didn't do what you want at all?

No errors, but the code didn't quite do what you want?

One last thing, why 2 loops?

Do you really need all the URLS in an array?

The are basically the same anyway with only the symbol changing.

Couldn't you just open the output file, loop through the symbols writing the data to the file and then close the output file?