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
LVL 8
Naresh PatelFinancial AdviserAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
Naresh PatelFinancial AdviserAuthor Commented:
Direction is right, but instead of maintaining text file. is that way to print last downloaded file name in excel sheet?

Thank you
0
gowflowCommented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Naresh PatelFinancial AdviserAuthor Commented:
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
0
gowflowCommented:
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
0
Naresh PatelFinancial AdviserAuthor Commented:
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
0
gowflowCommented:
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
0
gowflowCommented:
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
0
Naresh PatelFinancial AdviserAuthor Commented:
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
0
gowflowCommented:
yes but suggest you go 1 by one unless if they are easy to incorporate.
This is the today() fix.
gowflow
01-Downloading-V03.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelFinancial AdviserAuthor Commented:
Perfect - Posting Follow Up Link In & 5 Min
0
Naresh PatelFinancial AdviserAuthor Commented:
Here is the link "Code Incorporate"


Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.