Need vba to download and copy the data from zip file

[vjvijay88]

i need to download the zip file and copy the data from csv and paste in workbook without opening

https://www.nseindia.com/content/historical/DERIVATIVES/2016/DEC/fo26DEC2016bhav.csv.zip
the link contains the file in zip format but it has only one csv file inside and i need to copy in my download sheet help me

 

[ZVI]

Or just use this:
DestWorkbook = "Autobuy.xlsm"
Its folder does not matter for the code as it is already open

 

[ZVI]

Happy it is helped!

 

[captivator]

Hi Sir,

Thanks for this awesome code !!!!

I have an issue that name of csv file from where I am extracting data includes date and changes everyday as below

https://www.nseindia.com/content/historical/EQUITIES/2019/JUN/cm06JUN2019bhav.csv.zip

as on today,
tomorrow it will be

https://www.nseindia.com/content/historical/EQUITIES/2019/JUN/cm07JUN2019bhav.csv.zip


kindly suggest a change in code.

Thanks...

Or just use this:
DestWorkbook = "Autobuy.xlsm"
Its folder does not matter for the code as it is already open

 

[ZVI]

Hi & welcome to MrExcel Board!
Use this line in the code to get a date dependent url:

  UrlFile = "https://www.nseindia.com/content/EQUITIES/" & [TEXT(NOW(),"YYYY/MMM/c\mDDMMMYYYY")] & "bhav.csv.zip"

Regards

 

[captivator]

Hi ZVI,

Thanks a lot for such prompt answer !!!

This has resulted same as I was expecting. But there is a minor thing left which I could not mention at the time of sending my querry.

This code is resulting as below

https://www.nseindia.com/content/historical/EQUITIES/2019/Jun/cm08JUN2019bhav.csv.zip

But there is a minor change in Format of Month Name as below (REQUIRED IN CAPS)

https://www.nseindia.com/content/historical/EQUITIES/2019/JUN/cm08JUN2019bhav.csv.zip

Kindly help me for this change too.

Regards.

 

[ZVI]

Hi, try this then:

  Dim MonthName As String
  MonthName = [TEXT(NOW(),"MMM")]
  UrlFile = "https://www.nseindia.com/content/EQUITIES/" & [TEXT(NOW(),"YYYY/MMM/c\mDDMMMYYYY")] & "bhav.csv.zip"
  UrlFile = Replace(UrlFile, MonthName, UCase(MonthName))

 

[captivator]

Working 100% accurate.

Big THANKS !!!

Hi, try this then:

  Dim MonthName As String
  MonthName = [TEXT(NOW(),"MMM")]
  UrlFile = "https://www.nseindia.com/content/EQUITIES/" & [TEXT(NOW(),"YYYY/MMM/c\mDDMMMYYYY")] & "bhav.csv.zip"
  UrlFile = Replace(UrlFile, MonthName, UCase(MonthName))

 

[ZVI]

You are welcome, thanks for the feedback!

 

[Vaishali]

Hi Sir,

I am trying to download the zip from the below url.

https://www1.nseindia.com/content/historical/EQUITIES/2020/MAR/cm13MAR2020bhav.csv.zip

But, I am getting error that 'The download of specified resource has failed'. Kindly help me.

 

[Vaishali]

Hi ZVI,

Thanks a lot for such prompt answer !!!

This has resulted same as I was expecting. But there is a minor thing left which I could not mention at the time of sending my querry.

This code is resulting as below

https://www.nseindia.com/content/historical/EQUITIES/2019/Jun/cm08JUN2019bhav.csv.zip

But there is a minor change in Format of Month Name as below (REQUIRED IN CAPS)

https://www.nseindia.com/content/historical/EQUITIES/2019/JUN/cm08JUN2019bhav.csv.zip

Kindly help me for this change too.

Regards.