• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

time&date triggered macro to open , run , save close workbook

hy,
have this workbook where i run macro on determined date and time , need to automate this process ,
every week i change data in 5 data grids , in data grids there are between 1 and 60 date&time cells ,
example:

 after initial run on 1.4 10:00 data looks like this:

        A        ...                       C            ...                 E         ...
1  7.4. 19:00                  10.4. 16:00              14.4. 11:00
2                                    10.4. 19:00              14.4. 20:45
3                                    11.4. 13:00
4                                    11.4. 14:30
5                                    11.4. 16:00
6                                    11.4. 17:00
7                                    13.4. 21:00
8                                    14.4. 20:45
9                                    14.4. 20:45

workbook waits for 1 date&time value that will occur , in this example is in cell A1 ,
four hours before time in cell A1  on 7.4 13:00 workbook opens , runs macro "Start" , saves and close workbook ,
 now workbook waits for 2 date&time cell C1 and  repeats procedure open 4 hours before  (10.4 12:00) , runs macro "Start" , saves and close workbook ,
 on 3 date&time cell C3 , open 4 hours before (11.4 9:00) , runs macro "Start" , saves and close workbook ,  
 4 date&time cell C7 , open 4 hours before (13.4 17:00) , runs macro "Start" , saves and close workbook ,

after 4 run macro stops  automation of opening  workbook , then i change date&time and next round  of automation begins .

how to automate this process


 

0
thmh
Asked:
thmh
  • 2
1 Solution
 
bromy2004Commented:
You need to be a bit clearer.
You have said that you choose a DateTime and the macro runs 4 hours BEFORE that time.
then the Workbook WAITS for Time2 (10.4. 19:00) and runs 4 hours before that?

Could you provide a sample workbook?
0
 
thmhAuthor Commented:
here is workbook ,sorry for bad english
Book71.xls
0
 
Rob HensonFinance AnalystCommented:
For the file to run automatically at a specific time you would have to run through Windows scheduler. However you want the criteria of the file to change afterwards.

How about creating a separate .BAT file that opens the Excel file. The routine in Excel would then be in the Workbook Open routine and would include a stage that creates the .BAT file with the new date and time criteria for opening the file again.

Regards
Rob H
0
 
thmhAuthor Commented:
Rob H,
if i understand :
-bat is created when i manually open excel and run "create bat"
-bat opens excel on 1 date&time
-excel creates new bat
-new bat is scheduled to 2 date&time
...
this could work ,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now