Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Auto Download

Posted on 2014-12-26
12
Medium Priority
?
160 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: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
  • 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:Naresh Patel
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 31

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 8

Author Comment

by:Naresh Patel
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 31

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:Naresh Patel
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
 
LVL 31

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 31

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:Naresh Patel
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 31

Accepted Solution

by:
gowflow earned 2000 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:Naresh Patel
ID: 40519687
Perfect - Posting Follow Up Link In & 5 Min
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40519692
Here is the link "Code Incorporate"


Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

604 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