A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I set a file to auto-open and save at each month begin date?



 
 
Thread Tools Display Modes
  #1  
Old August 7th, 2009, 04:11 PM posted to microsoft.public.excel.misc
Dominic
external usenet poster
 
Posts: 31
Default Can I set a file to auto-open and save at each month begin date?

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?
  #2  
Old August 7th, 2009, 04:40 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default Can I set a file to auto-open and save at each month begin date?

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?

  #3  
Old August 7th, 2009, 05:01 PM posted to microsoft.public.excel.misc
Dominic
external usenet poster
 
Posts: 31
Default Can I set a file to auto-open and save at each month begin dat

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?

  #4  
Old August 7th, 2009, 05:36 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default Can I set a file to auto-open and save at each month begin dat

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?

  #5  
Old August 8th, 2009, 12:00 AM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Can I set a file to auto-open and save at each month begin dat

Windows Task Scheduler can start Excel with a designated workbook.

The workbook event code makes the change and saves the workbook and closes
Excel.


Gord Dibben MS Excel MVP

On Fri, 7 Aug 2009 09:36:01 -0700, JLatham
wrote:

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?


  #6  
Old August 8th, 2009, 02:02 AM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default Can I set a file to auto-open and save at each month begin dat

Gord, thanks for that - I wasn't sure if the task scheduler could do that or
not, should have at least suggested looking at it.

"Gord Dibben" wrote:

Windows Task Scheduler can start Excel with a designated workbook.

The workbook event code makes the change and saves the workbook and closes
Excel.


Gord Dibben MS Excel MVP

On Fri, 7 Aug 2009 09:36:01 -0700, JLatham
wrote:

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?



  #7  
Old August 8th, 2009, 02:06 AM posted to microsoft.public.excel.misc
Dominic
external usenet poster
 
Posts: 31
Default Can I set a file to auto-open and save at each month begin dat

Thanks for the solution! Got the task scheduler to open the file, but how to
I employ the workbook event code to close and save?

"Gord Dibben" wrote:

Windows Task Scheduler can start Excel with a designated workbook.

The workbook event code makes the change and saves the workbook and closes
Excel.


Gord Dibben MS Excel MVP

On Fri, 7 Aug 2009 09:36:01 -0700, JLatham
wrote:

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?



  #8  
Old August 8th, 2009, 03:15 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default Can I set a file to auto-open and save at each month begin dat

Perhaps I can redeem myself a little here. First, I did check the Scheduler
in Vista and I don't see a way to "end" an application, so we have to do it
some other way. One way would be to create a second workbook that the
scheduler would open a minute or so after the one to be saved which would
close the first workbook and then do an Application.Quit to shut down Excel.
Seems a bit complex, but would probably work.

So I came up with an almost equally complex solution using the one workbook
itself. It requires:
#1 - the big one: since a macro is going to do this for us, if you are using
pre-Excel 2007 version of Excel, your macro security is going to have to be
set to LOW (not something I highly recommend), or you'll have to set up a
digital signature and get Excel to trust it and enable macros/VBA Projects
signed with it. If you're using Excel 2007, you could place the file in a
Trusted Location, or declare its folder to be a Trusted Location.
#2 - the solution involves the Workbook_Open event, one user form, and a
regular code module. The user form must be opened in non-modal state so that
the .OnTimer event will actually fire rather than dumbly sitting there
waiting for you to click a button. The code takes care of that requirement.

Code to place into the ThisWorkbook module:

Private Sub Workbook_Open()
'in order for this to work unattended,
'macros must be enabled - this means that
'in pre-Excel 2007, your macro security will
'probably have to be set to LOW;
'in Excel 2007 you could declare its location
'as one in the Trusted Zone
'
'change the TimeValue() parameter to give yourself
'as much time as you want normally, the entry is
'standard hours, minutes, seconds format, so it is
'now set to a 10 second wait.
' a value of "00:01:00" would be a 1 minute wait
'
closingTime = Now() + TimeValue("00:00:10")
Application.OnTime closingTime, "SaveAndClose"
'show non-modal form
UserForm1.Show False ' open userform in non-modal state
End Sub

Next, create a user form. Accept the default names for it and the controls
you put on it. It will need a Label and a Command Button. I set the label's
text to inform me that I had X seconds/minutes to click the command button to
prevent the file from automatically saving and closing itself. I put some
text on the command button to tell me it was going to cancel the
self-destruct, and here's the code that goes with the command button:

Private Sub CommandButton1_Click()
On Error Resume Next
Application.OnTime EarliestTime:=closingTime, _
Procedu="SaveAndClose", Schedule:=False
If Err 0 Then
Err.Clear
End If
On Error GoTo 0
Unload Me
End Sub

Finally, you'll need to insert a new code module and place this code into it:

Option Explicit
Public closingTime As Date

Private Sub SaveAndClose()
On Error Resume Next
Application.OnTime EarliestTime:=closingTime, _
Procedu="SaveAndClose", Schedule:=False
If Err 0 Then
Err.Clear
End If
On Error GoTo 0
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
End Sub

Hope this helps - too bad the Scheduler doesn't have a "shut down
application" option - maybe Gord or someone else knows of a better way that
doesn't involve having to change your Macro Security settings.

"Dominic" wrote:

Thanks for the solution! Got the task scheduler to open the file, but how to
I employ the workbook event code to close and save?

"Gord Dibben" wrote:

Windows Task Scheduler can start Excel with a designated workbook.

The workbook event code makes the change and saves the workbook and closes
Excel.


Gord Dibben MS Excel MVP

On Fri, 7 Aug 2009 09:36:01 -0700, JLatham
wrote:

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?



  #9  
Old August 8th, 2009, 03:56 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Can I set a file to auto-open and save at each month begin dat

See Chip Pearson's site for OnTime method.

http://www.cpearson.com/excel/OnTime.aspx

Here is an example using Chip's procedures.

In a General Module..........................

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub"


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=True
End Sub

Sub Example_Macro()
ActiveSheet.Range("A1:F10").Interior.ColorIndex = 3
End Sub

Sub TheSub()
ThisWorkbook.Save
StopTimer
Application.Quit
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=False
End Sub

In Thisworkbook Module..............................

Private Sub Workbook_Open()
Example_Macro 'runs the macro
StartTimer 'starts the timer to save workbook and shut down Excel
End Sub

I''ll leave it to you to figure out a way to prevent macro warning when
workbook opens.


Gord

On Fri, 7 Aug 2009 18:06:01 -0700, Dominic
wrote:

Thanks for the solution! Got the task scheduler to open the file, but how to
I employ the workbook event code to close and save?

"Gord Dibben" wrote:

Windows Task Scheduler can start Excel with a designated workbook.

The workbook event code makes the change and saves the workbook and closes
Excel.


Gord Dibben MS Excel MVP

On Fri, 7 Aug 2009 09:36:01 -0700, JLatham
wrote:

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?




  #10  
Old August 8th, 2009, 05:57 PM posted to microsoft.public.excel.misc
Dominic
external usenet poster
 
Posts: 31
Default Can I set a file to auto-open and save at each month begin dat

Appreciate the help!

"Gord Dibben" wrote:

See Chip Pearson's site for OnTime method.

http://www.cpearson.com/excel/OnTime.aspx

Here is an example using Chip's procedures.

In a General Module..........................

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub"


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=True
End Sub

Sub Example_Macro()
ActiveSheet.Range("A1:F10").Interior.ColorIndex = 3
End Sub

Sub TheSub()
ThisWorkbook.Save
StopTimer
Application.Quit
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
Schedule:=False
End Sub

In Thisworkbook Module..............................

Private Sub Workbook_Open()
Example_Macro 'runs the macro
StartTimer 'starts the timer to save workbook and shut down Excel
End Sub

I''ll leave it to you to figure out a way to prevent macro warning when
workbook opens.


Gord

On Fri, 7 Aug 2009 18:06:01 -0700, Dominic
wrote:

Thanks for the solution! Got the task scheduler to open the file, but how to
I employ the workbook event code to close and save?

"Gord Dibben" wrote:

Windows Task Scheduler can start Excel with a designated workbook.

The workbook event code makes the change and saves the workbook and closes
Excel.


Gord Dibben MS Excel MVP

On Fri, 7 Aug 2009 09:36:01 -0700, JLatham
wrote:

If what I understand is correct, you cannot do it.

What I understand now is that you want an Excel file to open itself at
midnight and then save itself so that its date stamp will reflect the next
day.

A closed file cannot open itself. You would need another
application/scheduler running somewhere to perform that task. And right off
the top of my head I don't know of any - they probably exist, I've just never
had a call to use one.

"Dominic" wrote:

Well, let's just use TODAY() as an example. I save the file now, but want to
auto-open and save the file at midnight so the date on the file shows the
change in the date.

The need for this is that the file is used as an external data provider for
reporting. If the file is not saved with the change, the previous saved data
will be fed into reports instead of today's data. Thanks

"JLatham" wrote:

Probably, but a couple of examples of your date-dependent formulas would sure
help.

Also, look at the NOW() function in Excel. That may help you with this
problem. You can do math with it that will give elapsed time between dates,
as NOW()-30 for a date 30 days previous to the current date.


"Dominic" wrote:

Want the excel file to open so the date-dependent formulas refresh to the new
month. Then save the file. Can this be done?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:22 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.