Solved

RT - Data

Posted on 2016-08-19
3
42 Views
Last Modified: 2016-08-22
Hi Experts,

Please Any One Help Me With Code Which Can Download Zip Files From Server?
In Attached WB - Sheet - Main - Cell - I1 Is Date ....Want Code Which download Data Between That To Today Date In Folder "D:\RTTrading\Data\" & Link Is Like This "https://www.nseindia.com/content/historical/EQUITIES/2016/AUG/cm18AUG2016bhav.csv.zip
Above Link Will Download Zip File For Date 18AUG2016.

Thanks
RT-From-Terminal.xlsm
0
Comment
Question by:itjockey
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
WalkaboutTigger earned 500 total points
ID: 41763261
The fundamental difficulty is building the download filename.  This presumes the workbook will be downloading the file dated for the day.  If that is not the case, let me know.

Option 1:

Option Explicit
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 DownloadFileAPI()
Dim strURL As String
Dim LocalFilePath As String
Dim DownloadStatus As Long

	strURL = "https://www.nseindia.com/content/historical/EQUITIES/"
	strURL = strURL & Date('yyyy',Date) & "/"
	strURL = strURL & UPPER(Left(MonthName(Month(Date)),3)) & "/"
	strURL = strURL & UPPER(Left(MonthName(Month(Date)),3)) & "/"
	strURL = strURL & "cm" & Day(Date) & UPPER(Left(MonthName(Month(Date)),3)) & Date('yyyy',Date) & "bhav.csv.zip"
    LocalFilePath = "D:\RTTrading\Date\" & "cm" & Day(Date) & UPPER(Left(MonthName(Month(Date)),3)) & Date('yyyy',Date) & "bhav.csv.zip"
    DownloadStatus = URLDownloadToFile(0, strURL, LocalFilePath, 0, 0)
    If DownloadStatus = 0 Then
        MsgBox "File Downloaded. Check in this path: " & LocalFilePath
    Else
        MsgBox "Download File Process Failed"
    End If
End Sub

Open in new window


Option 2:
Option Explicit
Sub DownloadFile()
Dim WinHttpReq As Object
Dim oStream As Object
Dim myURL As String
Dim LocalFilePath As String

	myURL = "https://www.nseindia.com/content/historical/EQUITIES/"
	myURL = myURL & Date('yyyy',Date) & "/"
	myURL = myURL & UPPER(Left(MonthName(Month(Date)),3)) & "/"
	myURL = myURL & UPPER(Left(MonthName(Month(Date)),3)) & "/"
	myURL = myURL & "cm" & Day(Date) & UPPER(Left(MonthName(Month(Date)),3)) & Date('yyyy',Date) & "bhav.csv.zip"
    LocalFilePath = "D:\RTTrading\Date\" & "cm" & Day(Date) & UPPER(Left(MonthName(Month(Date)),3)) & Date('yyyy',Date) & "bhav.csv.zip"

Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "", ""  '("username", "password")
WinHttpReq.send

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile LocalFilePath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub

Open in new window

0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 41765296
Thanks
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41766237
One thing to remember is that many URLs are CaSe-SeNSiTiVe, hence the use of the UPPER function in the code.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question