?
Solved

RT - Data

Posted on 2016-08-19
3
Medium Priority
?
46 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:Naresh Patel
[X]
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
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
WalkaboutTigger earned 2000 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:Naresh Patel
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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