Difficulty in Downloading files

zaska · Jul 18, 2011

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

taurean · Jul 18, 2011

Sorry, it is my mistake:
Change this line in the code:

Code:

[COLOR=red]Private[/COLOR] Sub LoopThroughURLs()

To

Code:

[COLOR=red]Public[/COLOR] Sub LoopThroughURLs()

Then it will appear in the run macro window.

As to second part: does the rest remain the same and ONLY DATE changes?

zaska · Jul 18, 2011

Sir,

Now the code worked and for the second part..yes the date changes everyday in the URL but the date format is not the same for all the URL's . Moreover is it possible to specify To and From Dates so that i can choose to download files between two or three days? I want to download all the files into a single folder.

Thank u

Ruddles · Jul 18, 2011

Sektor said:
Code:

Declare Function URLDownloadToFile Lib "urlmon"...

A very useful bit of code. I've snipped it and I'm sure I'll be using it in the future.

Thanks!

taurean · Jul 18, 2011

zaska said:
Sir,

Now the code worked and for the second part..yes the date changes everyday in the URL but the date format is not the same for all the URL's . Moreover is it possible to specify To and From Dates so that i can choose to download files between two or three days? I want to download all the files into a single folder.

Thank u

If the date part is a consistent pattern then something formula based can be done.

2nd part, I am not sure about. I don't think I can help you with that.

zaska · Jul 18, 2011

Hello,

Can anyone help me with the second part of the code ?

Thank You

zaska · Jul 19, 2011

Can anyone help me to download the files from Dynamic URL's which only change the date part?

Thank u

Ruddles · Jul 19, 2011

The URL is made up of various parts:-

http://www.nseindia.com/content/historical/DERIVATIVES
/
2011
/
JUL
/fo
18JUL2011
bhav.csv.zip

You need to build the URL from these parts. Where the parts are static and unchanging (parts 1,2,4,6,8), you use those values; where the parts are dependent upon the current date (picked out in red), you use the appropriate formula to obtain the value you need from the date.

So to get part 3:-

Code:

format(now(),"yyyy") [COLOR=green]' 2011[/COLOR]

and part 5:-

Code:

format(now(),"mmm") [COLOR=green]' JUL[/COLOR]

and part 7:-

Code:

format(now(),"ddmmmyyyy") [COLOR=green]' 18JUL2011[/COLOR]

Then your final code for building the URL would be:-

Code:

[FONT=Fixedsys]sURL="[/FONT][URL="http://www.nseindia.com/content/historical/DERIVATIVES"][FONT=Fixedsys]http://www.nseindia.com/content/historical/DERIVATIVES[/FONT][/URL][FONT=Fixedsys]" _[/FONT]
[FONT=Fixedsys]    & "/" _[/FONT]
[FONT=Fixedsys]    & format(now(),"yyyy") _[/FONT]
[FONT=Fixedsys]    & "/" _[/FONT]
[FONT=Fixedsys]    & format(now(),"mmm") _[/FONT]
[FONT=Fixedsys]    & "/fo" _[/FONT]
[FONT=Fixedsys]    & format(now(),"ddmmmyyyy") _[/FONT]
[FONT=Fixedsys]    & "bhav.csv.zip"[/FONT]

Similarly, the filename would be:-

Code:

[FONT=Fixedsys]sFilename="[/FONT][FONT=Fixedsys]fo" &[/FONT][FONT=Fixedsys] format(now(),"ddmmmyyyy") [/FONT][FONT=Fixedsys]& "bhav.csv.zip"[/FONT]

Then you use sURL and sFilename when you call the DownloadFile subroutine.

You would analyse your other URLs and filenames in a similar way to determine which bits were static and unchanging and which bits were dependent upon the current date, then build a URL and filename as I've outlined above for passing to the DownloadFile subroutine.

To obtain the various date parts, you just change the string in the Format function. Typical examples are:-

<TABLE style="WIDTH: 123pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=164 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>

d

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=100>

7

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

dd