vba for downloading data from dynamic url

[parag2222]

hi,

I got following vba code wherein i just need to link url "https://www.nseindia.com/api/corpor...m_date=05-04-2020&to_date=05-05-2020&csv=true" to a cell reference because this link will change on daily basis. please provide a vba for same

>>> use code - tags <<<

Sub TestDown()
DownloadFile "https://www.nseindia.com/api/corporate-board-meetings?index=equities&from_date=05-04-2020&to_date=05-05-2020&csv=true", "C:\test.csv"
End Sub

Function DownloadFile(myURL As String, saveToPath As String)
Dim WinHttpReq As Object
Dim iTimer As Long
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
Debug.Print WinHttpReq.readystate
WinHttpReq.send
'make sure readystate is finished
iTimer = Timer
Do While WinHttpReq.readystate = 1
'if 10 seconds elapse and nothing happens, abort:
If Timer - iTimer > 10 Then Exit Do
Loop
'readystate 4 = all data received
If WinHttpReq.readystate = 4 Then
    If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile saveToPath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
    MsgBox ("Download Complete")
    Else
    MsgBox ("HTTP error: " & WinHttpReq.Status)
   End If
Else
MsgBox ("Couldn't get file")
End If
Set oStream = Nothing
Set WinHttpReq = Nothing
End Function

 

[Logit]

If you paste the URL only into a web browser, the Excel file downloads automatically.

Leave out this part : " , "C:\test.csv"

 

[parag2222]

i mean i need a code instead of
DownloadFile "https://www.nseindia.com/api/corpor...m_date=05-04-2020&to_date=05-05-2020&csv=true",

to
DownloadFile "url to be taken from cell A1 in sheet1",

 

[Logit]

Option Explicit

Sub CommandButton1_Click()
    ActiveWorkbook.FollowHyperlink Address:=Sheet1.Range("A1").Value
End Sub

 

[Marc L]

Hi,​

​

for this static URL do not use ADODB.Stream - do you have at least an idea why you need it ?‼ -​

but from the request proprerty responseText just use the VBA basics for managing as usual any text file …​

Another question : why Excel / VBA to download on hard disk a text file ?​

​

 

[parag2222]

Hi thanks for the code but i am a novice in vba so can you please help me write the whole code like where to put

>>> 2nd time - - use code - tags <<<

Option Explicit

Sub CommandButton1_Click()
    ActiveWorkbook.FollowHyperlink Address:=Sheet1.Range("A1").Value
End Sub

before or after downloadfile function

 

[Marc L]

Thanks to not forget to use the code tags (easy via the code icon in the 3 dots menu) …​

​

If at least you well elaborate your need in order there is nothing to guess !​

What's you need, just to create a file on hard disk or what else ?​

​

As you must have found a ready code on several forums - like here ! - just performing a smart web search​

so before to create any thread …​

​

 

[parag2222]

my requirement is i need to download a file from website wherein the url will be picked up from a cell

 

[Marc L]

As several existing solutions on Web like at least 3 within this forum (one yet explained in post #5 …),​

your actual code, did you really write it or it's just a bad copy/paste from a too old thread ?​

As copying/pasting is very not coding …​

​

 

[parag2222]

Marc did you read my post #1 wherein i wrote that i got this code, second in post #6 i already mentioned i am novice, instead of giving solutions you are criticizing the things.

It would be helpful if you provide the code instead of criticizing it.

 

[Marc L]

So without any answer just use the top page Search button for URLDownloadToFile (this one is worldwide !)​

or for RequestDownload, among samples in this forum …​

​

 

[parag2222]

Thanks for helping, i got code from another website:

>>> 3rd time --- use code - tags <<<

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub download_multple_photos()

dlpath = "D:\"

For i = 2 To 7
    imgsrc = Cells(i, 2)
    imgname = Cells(i, 1)
    URLDownloadToFile 0, imgsrc, dlpath & imgname & ".csv", 0, 0
Next i

End Sub

 

[Marc L]

The same than within this forum !​

​

Next time follow forums rules …​

​