Solved

RT - Data

Posted on 2016-08-19
3
39 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The code described here does no longer work. Please see replacement Article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html (http…
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 this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now