Confused with Put in VBA

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi ,

The Following is a Part of a VBA Code

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Without testing it myself, I would suggest you change this:-
Code: 
[FONT=Courier New]          arrURL(i) = "http://www.nseindia.com/content/indices/histdata/"[/FONT]
[FONT=Courier New]          arrURL(i) = arrURL(i) &[B][COLOR=red] UCase(c.Value)[/COLOR][/B][/FONT]
[FONT=Courier New]          arrURL(i) = arrURL(i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy") & ".csv"[/FONT]
to this:-
Code: 
[FONT=Courier New]          arrURL(i) = [B][COLOR=red]UCase(c.Value)[/COLOR][/B][/FONT]

And yes, it's possible to modify bArray in whatever way you wish.
 
Upvote 0
Hi,

This is the Complete Code

Code: 
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(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
    
    '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)
        oXMLHTTP.Open "GET", arrURL(i), False
        oXMLHTTP.send
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop
        
        bArray = oXMLHTTP.responseBody
        
        Put #hFile, ,[B][COLOR=Red] arrURL(i)[/COLOR][/B]
        Put #hFile, , bArray
              
       Next i

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

End Sub

As suggested i can't change arrURL(i) because the URL won't get any output..

What i mean to ask is insted of " Put #hFile , , arrURL(i) " I want the UCase(c.value) in the output file in column " A "

At present this code is showing the entire arrURL(i) along with barray in the outfile

Example
http://www.nseindia.com/content/indices/histdata/S&P CNX NIFTY30-05-2011-30-05-2011.csv"Date"

I Only need " S&P CNX NIFTY " which is the UCase(C.Value) in the output excel file.

Hope it's clear now

Thank you
 
Upvote 0
What exactly is that code meant to do?

I realise it's to get some data from a web page/pages but it's not clear why you are using this method.

One reason for that is because the URL you posted (not the one in the code) doesn't work and we don't know any of the values making up the full URL in the code.

Also, I don't think you'll be able to change bArray - as far as I can tell that's what's returned by the XMLHTTP Send.

Do you perhaps mean you want to change what's done with it?
 
Upvote 0
Sir,

The code is meant to dowload the Daily Open,High,Low,Close,Volume of Different symbols referred in Range " A1:A11".

what i want to know is

1. Can i change the Output format of barray ? ( I also think that is not possible )

2. Insted of " put #hFile , , arrURL(i) ...i want the symbol names mentioned in Range " A1:A11". I don't need the complete string in the output file..

Example ..The output should look like

SymbolName,Date,Open,High,Low,Close,Volume.

Hope i am able to convey my request.

Thank you
 
Upvote 0
First of all, please take Norie's comments on board.

As regards the code, make these changes in a copy of your workbook:-
Code: 
[FONT=Courier New]       If Len(c.Value) > 0 Then
            ReDim Preserve arrURL[COLOR=black]([COLOR=red][B]1 to 2[/B][/COLOR],0 To i)
            arrURL([COLOR=red][B]1,[/B][/COLOR]i) = "http://www.nseindia.com/content/indices/histdata/"
            arrURL([B][COLOR=red]1,[/COLOR][/B]i) = arrURL([COLOR=red][B]1,[/B][/COLOR]i) & [B]UCase(c.Value)[/B]
            [COLOR=red][B]arrURL(2,i) =  UCase(c.Value)
[/B][/COLOR]            arrURL([B][COLOR=red]1[/COLOR][/B],i) = arrURL([COLOR=red][B]1,[/B][/COLOR]i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy")[/COLOR] & ".csv"
            i = i + 1
        End If
[/FONT]
and:-
Code: 
[FONT=Courier New]    For i = 0 To UBound(arrURL[COLOR=red][B],1[/B][/COLOR])
        oXMLHTTP.Open "GET", arrURL([B][COLOR=red]1,[/COLOR][/B]i), False
        oXMLHTTP.send
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop
        
[COLOR=black]        bArray = oXMLHTTP.responseBody
        
        Put #hFile, , arrURL([COLOR=red][B]2,[/B][/COLOR]i)
        Put #hFile, , bArray
[/COLOR]              
       Next i[/FONT]
As each complete URL/date is stored in column 1 of arrURL, UCase(c.Value) is stored alongside it in column 2 so it can be used later in the Put statement.

As regards the bArray question, which bit of bArray do you want to Put and which bit do you want to discard?

Finally, do you declare arrURL, i and bArray anywhere in your code?
 
Upvote 0
What do you want?

In post #3 you say you only want 'S&P CNX Nifty', do you mean only want the prices for that stock/symbol/company?

Is this the correct URL and the data you want?

http://www.nseindia.com/content/indices/histdata/S&P%20CNX%20NIFTY30-05-2011-30-05-2011.csv


<TABLE style="WIDTH: 376pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=502><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" span=5 width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=71>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Open</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>High</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Low</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Close</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Shares Traded</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=111>Turnover (Rs. Cr)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>30-May-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5493.75</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5509.3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5458.6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5473.1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1.01E+08</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4451.38</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
Sir,

I made the Following changes as suggested by you..

Code: 
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, 1)
        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


But I got the output for only Three Symbols and that too wrongly as

S&P CNX NIFTY"Date"
S&P CNX 500"Date"
CNX 100"Date"

Regarding the Barray file....The Desired output format is

SYMBOLNAME,DATE,OPEN,HIGH,LOW,CLOSE,VOLUME

Where as The Barray format is

arrURL(i)
DATE,OPEN,HIGH,LOW,CLOSE,VOLUME,TURNOVER


I used the SymbolChange Function provided by you recently to extract symbol names...and i added macro to change date format (YYYYMMDD) delete alteranterown(Strings),and change number format of OPEN,HIGH,LOW,CLOSE to "0.00".

Hope i am clear now

symbols used in the Range " A1:A19"

<table width="126" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:95pt" width="126" height="17">S&P CNX NIFTY</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S&P CNX 500</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX 100</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX NIFTY JUNIOR</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">NIFTY MIDCAP 50</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX MIDCAP</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX SMALLCAP</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">BANK NIFTY</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX ENERGY</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX FMCG</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX INFRA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX IT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX MNC</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX PHARMA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX PSE</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX PSU BANK</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX REALTY</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">CNX SERVICE</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S&P CNX DEFTY</td> </tr> </tbody></table>

I would be grateful to you if you could suggest any easy/alternative way to accomplish this task.

It could also save me lot of time...

This code is generating output within seconds...The Only Problem is with the Output Format.

Thank you very much
 
Upvote 0
@ Norie sir

I want the Data for all the 19 Symbols..

PHP: 
      S&P CNX NIFTY       S&P CNX 500       CNX 100       CNX NIFTY JUNIOR       NIFTY MIDCAP 50       CNX MIDCAP       CNX SMALLCAP       BANK NIFTY       CNX ENERGY       CNX FMCG       CNX INFRA       CNX IT       CNX MNC       CNX PHARMA       CNX PSE       CNX PSU BANK       CNX REALTY       CNX SERVICE       S&P CNX DEFTY


Desired Output

SYMBOLNAME,DATE,OPEN,HIGH,LOW,CLOSE,VOLUME

I just wanted to trim the above code to get the desired output..

Do u know any other easy/alternative method of accomplishing this task.

Thank you verymuch
 
Upvote 0
Is the data I posted not what you want, even for just that one stock?

I could post some code, but that's the data it returns, in the format it's returned, saved as a CSV file.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
212
Peter_SSs
Peter_SSs
W
  • Question Question
VBA Help - Lookup Match Between 2 Worksheets
Replies
0
Views
693
Web_Jim
W
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
823
Agnarr
Agnarr
M
  • Solved
My code was working 2 days back but now my excel just crashes. Thank for any help in Advance!
Replies
9
Views
489
Alex Blakenburg
A
A
  • Question Question
VBA store data from file in web to matrix
Replies
5
Views
823
Axtros
A

Forum statistics

Threads
1,224,594
Messages
6,179,795
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top