Solved

Auto Download

Posted on 2014-12-26
12
143 Views
Last Modified: 2014-12-27
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
0
Comment
Question by:itjockey
  • 6
  • 5
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40519367
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
 
LVL 8

Author Comment

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

Thank you
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40519438
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
 
LVL 8

Author Comment

by:itjockey
ID: 40519442
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40519451
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
 
LVL 8

Author Comment

by:itjockey
ID: 40519508
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
ID: 40519568
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40519578
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
 
LVL 8

Author Comment

by:itjockey
ID: 40519619
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40519658
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
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40519687
Perfect - Posting Follow Up Link In & 5 Min
0
 
LVL 8

Author Comment

by:itjockey
ID: 40519692
Here is the link "Code Incorporate"


Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
formattig excel from access 3 17
Excel 6 18
Add macros on Open 9 32
Determine 90 days from most recent date 3 18
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now