Solved

RT - Data

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

705 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