Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

RT - Data

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
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…

840 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