Difficulty in Downloading files

[zaska]

Hello,

I download few files daily from the following links and i would like to seek the help of members of this forum in automating the same using VBA to download files daily ( specifying from date & Todate ) to E drive


http://www.nseindia.com/content/historical/EQUITIES/2011/JUL/cm18JUL2011bhav.csv.zip

http://www.nseindia.com/content/historical/DERIVATIVES/2011/JUL/fo18JUL2011bhav.csv.zip

http://www.nseindia.com/archives/equities/mto/MTO_18072011.DAT

http://www.bseindia.com/BSEDATA/gross/2011/SCBSEALL1807.zip

http://www.bseindia.com/BSEDATA/gross/2011/SCBSEALL1807.zip

Any help would save me considerable amount of time daily.


Thank you

 

[Sektor]

Declare 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


Private Sub DownloadFile(URL As String, TargetFile As String)
    If URLDownloadToFile(0, URL, TargetFile, 0, 0) <> 0 Then
        MsgBox "Error occured.", vbCritical
    Else
        MsgBox "File has been downloaded!", vbInformation
    End If
End Sub

 

[zaska]

Sir,

Thank you.. How could i use the above code with the download links ?

 

[Sektor]

URL = "http://www.nseindia.com/content/historical/EQUITIES/2011/JUL/cm18JUL2011bhav.csv.zip"
TargetFile = "C:\cm18JUL2011bhav.csv.zip"

 

[zaska]

Sir,

I added the same but the code is not running and asking me the macro name.

Moreover the date in the URL links changes daily. How to handle this?

[B]Declare 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
Private Sub DownloadFile(URL As String, TargetFile As String)
    URL = "http://www.nseindia.com/content/historical/EQUITIES/2011/JUL/cm18JUL2011bhav.csv.zip"
    TargetFile = "C:\cm18JUL2011bhav.csv.zip"
    If URLDownloadToFile(0, URL, TargetFile, 0, 0) <> 0 Then
        MsgBox "Error occured.", vbCritical
    Else
        MsgBox "File has been downloaded!", vbInformation
    End If
    
End Sub
[/B]

Thank u

 

[zaska]

Hello,

Plz tell me about how this code can be used


thank u

 

[taurean]

Zaska,

You can have a look at the following code which has been posted at vbaexpress.com

http://www.vbaexpress.com/forum/showthread.php?t=29333
Post#2

The code from Sektor is similar (uses the same API hook)

 

[zaska]

Sir,

I tried the following code which worked. But how to make this work for all the five URL's posted above at once and also how to loop the code for downloading files of more days.

Option Explicit

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

Private Sub Example()
'From All API
DownloadFile _
"http://www.nseindia.com/content/historical/EQUITIES/2011/JUL/cm18JUL2011bhav.csv.zip", _
"E:\cm18JUL2011bhav.csv.zip"
End Sub

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

Thank u

 

[taurean]

You will need to put the URL addresses in an excel sheet.
E.g. Sheet1, Column A (URL), Column B (SavePath)
Then edit the code to loop through the list like:

Option Explicit
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
Private Sub LoopThroughURLs()
'From All API
'example by Matthew Gates ([EMAIL="Puff0rz@hotmail.com"]Puff0rz@hotmail.com[/EMAIL])
'http://www.vbaexpress.com/forum/showthread.php?t=29333
Dim lLR As Long
Dim r As Range
lLR = Range("A" & Rows.Count).End(xlUp).Row
If lLR = 1 And Range("A1").Value = "" Then Exit Sub
For Each r In Range("A1:A" & lLR)
DownloadFile r.Value, r.Offset(, 1).Value
Next r
End Sub
Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

And do not remove the VBA comments. They will be useful when your mind goes blank and you don't remember where did you pick them up!

 

[zaska]

Sir,

The above code is not running and asking for a Macro name and moreover the URL is dynamic ..the date contained in the URL's changes everyday.

How do i handle this? I don't have Good knowledge about VBA.


Thank u