asked on

Auto Download

Hi Experts,

I have one file which downloads zip files from website. for these I have to put time range in cells so code will downloads files between these time range. After downloading I do unzipping and formatting to these files. Say I had given time range in cell is 23 Dec 2014 (in cell A1 - Start Date) & in cell B1 26 Dec 2014 (End Date) then on clicking download button I will get files for these dates in zip format. after uzipping & formatting I will have "cm23DEC2014bhav" ,"cm24DEC2014bhav" & ""cm26DEC2014bhav".(25 DEC holiday).

Question Part
now I wondered is there any way in code which it search for last date which I downloaded (in above case "cm26DEC2014bhav") & Start downloading from very next date from last downloaded date to todays date.

and yes before downloading zip files delete all files from downloading location (old unzipped files). my downloading location path is "D:\AmiBroker Data\NSE\Eq"

See attached file

Thanks
01-Downloading.xlsm

Phillip Burton

Why don't you have a text file in which you LINE PRINT the names of the files you download.

Then, when you run the routine again, you can check all file names against the files previously downloaded, and skip any already on the list.

Naresh Patel

ASKER

Direction is right, but instead of maintaining text file. is that way to print last downloaded file name in excel sheet?

Thank you

Jacques Geday

Let me summarise your request

You need to locate the last date that a file was downloaded and then you want to run the same procedure from the next date till present or today and before doing that you want to clear the directory and then start downloading.

My questions:

You want to do that based on the files you have in your directory ? Like you want the macro to look for the last downloaded file and start the process from that date till today and prior that delete then the files in the directory ?

or

You want this process to be done AFTER you run the present routine and have the last date captured and then the directory cleaned again and procedure run from next date till today ?

gowflow

Naresh Patel

ASKER

Let me summarize your request

You need to locate the last date that a file was downloaded and then you want to run the same procedure from the next date till present or today and before doing that you want to clear the directory and then start downloading.

Yes

I think the 2 way of achieve this is better then 1st as you suggested in your comment. As in 1st way macro look for the last date in directory but in future I may not getting same format of file name i.e. cm24DEC2014" (I think you will get last dated file from this string).

So in 2 way of achieving this - capture last downloaded file date in excel cell it self. so in next time it will download from this capture date +1 till TODAY(). before doing this clean directory. I had bold sentence last downloaded file date as some time holidays are there so if we go by TODAY() date as capture date it wont work. need to capture the date of file.

may I have one button request which give me choice to switch auto download to manual or manual to auto.

Thanks

Jacques Geday

Look how I solved it very simple.

Now we have
Start Date A1 = 22 Dec 2014
End Date B1 = 27 Dec 2014

You press on Download
it will do the following:

1) clean all the files that are in the folder: D:\AmiBroker Data\NSE\Eq
2) run the download file to create the files between start date and end date
3) Display at the end of the routine the last effective date which is 26 Dec 2014 in this case and will give you a feedback
4) It will update now at the end of the routine
Start Date A1 = 27 Dec 2014
End Date B1 = blank

A1 = 26 Dec 2014 + 1 day
5) It will leave B1 as blank so the next time you run the routine all you have to do is simply put today's date in B1 and not touch A1 as it will be updated automatically by the routine.

Pls check the attach file by older dates and see if it works correctly.
gowflow
01-Downloading-V01.xlsm

Naresh Patel

ASKER

Just need to confirm on Monday 29Dec2014 if I run this routine on morning it will download file till 26dec2014 and capture 26dec2014 as last date? As on morning of 29Dec 2014 file is not available on server for the date 29dec2014 ....it will be available after 5:30PM.

Thanks

Jacques Geday

it will capture whatever is there until the date put in B1. so if you put on Monday 29 the date 12/29/2014 in B1 it will capture whatever from A1 to B1 available.

To test it put say in A1 = 1 Oct 2014 and in B1 = 1 Dec 2014
and run it and see what happens

then run it again with whatever date there is in A1 and put in B1 today's date and see the results.
So same will be on Monday.

gowflow

Jacques Geday

This version has the Interval taken care of. If file not found it will reset correctly. Try it not putting a start or end date or run it say from 12/27/2014 till 12/27/2014 this is what will happen on 12/29/2014 in the morning. Run it again after 5:30Pm on Monday will give different result.

gowflow
01-Downloading-V02.xlsm

Naresh Patel

ASKER

ok done got it - it is perfect only thing to need is - default excel formula in cell B1 "TODAY()" as after running this macro cell B1 formula "TODAY()" get disappear.

may I ask new question & post link over here? it is follow up question. all procedures is done need to combine all process in one WB. I have Downloading Zip - Unzipping - Formatting . These are separate WB need to combine all in one WB.

Thanks

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naresh Patel

ASKER

Perfect - Posting Follow Up Link In & 5 Min

Naresh Patel

ASKER

Here is the link "Code Incorporate"

Thanks