Make code aware of the workbook file name
 

My Auto_Open code runs a timer that will close a document if it has been open
for 15 minutes (it is shared by a number of users, some of them tend to
forget to close out when they are done). What I was wondering, since I want
to create a template document to roll out each year, and I do not want to
have to remember to change the document name in the code each year. Is there
anyway that the code can be made aware of the document name? I can not use
ActiveWorkbook since it might not be the active document at the time.

Sub Auto_Open()
'
' AutoRun Macro
' Macro recorded 2/3/2001 by Patrick C. Simonds
'

bSELCTIONCHANGE = False

Events.Enable_Events


Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 15 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

Workbooks("VacationCalendar 2010.xlsm").Save
Workbooks("VacationCalendar 2010.xlsm").Close

End Sub

Make code aware of the workbook file name
 

On Apr 24, 9:48*am, ordnance1
wrote:
My Auto_Open code runs a timer that will close a document if it has been open
for 15 minutes (it is shared by a number of users, some of them tend to
forget to close out when they are done). What I was wondering, since I want
to create a template document to roll out each year, and I do not want to
have to remember to change the document name in the code each year. Is there
anyway that the code can be made aware of the document name? I can not use
ActiveWorkbook since it might not be the active document at the time.

Sub Auto_Open()
'
' AutoRun Macro
' Macro recorded 2/3/2001 by Patrick C. Simonds
'

bSELCTIONCHANGE = False

Events.Enable_Events

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 15 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
* *Start = Timer
* *Do While Timer < Start + TotalTimeInMinutes
* * * *DoEvents
* *Loop
* *Finish = Timer
* *TotalTime = Finish - Start
* *Application.DisplayAlerts = False
End If

Start = Timer
* *Do While Timer < Start + (5 * 60)
* * * *DoEvents
* *Loop
* *Finish = Timer
* *TotalTime = Finish - Start
* *Application.DisplayAlerts = False

* *Workbooks("VacationCalendar 2010.xlsm").Save
* *Workbooks("VacationCalendar 2010.xlsm").Close

End Sub

If it is in excel use Thisworkbook
If it is in wordl use Thisdocument

Make code aware of the workbook file name
 

perhaps replace:


VBA Code:
--------------------


Workbooks("VacationCalendar 2010.xlsm").Save
Wor;kbooks("VacationCalendar 2010.xlsm").Close
--------------------



with


VBA Code:
--------------------


Thisworkbook.close True
--------------------



which will save and close the workbook that the code is sitting in,
under its existing name/folder.


ordnance1;707998 Wrote:

My Auto_Open code runs a timer that will close a document if it has been
open
for 15 minutes (it is shared by a number of users, some of them tend to
forget to close out when they are done). What I was wondering, since I
want
to create a template document to roll out each year, and I do not want
to
have to remember to change the document name in the code each year. Is
there
anyway that the code can be made aware of the document name? I can not
use
ActiveWorkbook since it might not be the active document at the time.

Sub Auto_Open()
'
' AutoRun Macro
' Macro recorded 2/3/2001 by Patrick C. Simonds
'

bSELCTIONCHANGE = False

Events.Enable_Events


Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 15 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

Workbooks("VacationCalendar 2010.xlsm").Save
Workbooks("VacationCalendar 2010.xlsm").Close

End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198152

http://www.thecodecage.com/forumz

Make code aware of the workbook file name
 

Thank you sir.

2 birds one stone (save and close)

"p45cal" wrote in message
...

perhaps replace:


VBA Code:
--------------------


Workbooks("VacationCalendar 2010.xlsm").Save
Wor;kbooks("VacationCalendar 2010.xlsm").Close
--------------------



with


VBA Code:
--------------------


Thisworkbook.close True
--------------------



which will save and close the workbook that the code is sitting in,
under its existing name/folder.


ordnance1;707998 Wrote:

My Auto_Open code runs a timer that will close a document if it has been
open
for 15 minutes (it is shared by a number of users, some of them tend to
forget to close out when they are done). What I was wondering, since I
want
to create a template document to roll out each year, and I do not want
to
have to remember to change the document name in the code each year. Is
there
anyway that the code can be made aware of the document name? I can not
use
ActiveWorkbook since it might not be the active document at the time.

Sub Auto_Open()
'
' AutoRun Macro
' Macro recorded 2/3/2001 by Patrick C. Simonds
'

bSELCTIONCHANGE = False

Events.Enable_Events


Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 15 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

Workbooks("VacationCalendar 2010.xlsm").Save
Workbooks("VacationCalendar 2010.xlsm").Close

End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=198152

http://www.thecodecage.com/forumz

Make code aware of the workbook file name
 

Thanks for taking the time to help

"Javed" wrote in message
...
On Apr 24, 9:48 am, ordnance1
wrote:
My Auto_Open code runs a timer that will close a document if it has been
open
for 15 minutes (it is shared by a number of users, some of them tend to
forget to close out when they are done). What I was wondering, since I
want
to create a template document to roll out each year, and I do not want to
have to remember to change the document name in the code each year. Is
there
anyway that the code can be made aware of the document name? I can not
use
ActiveWorkbook since it might not be the active document at the time.

Sub Auto_Open()
'
' AutoRun Macro
' Macro recorded 2/3/2001 by Patrick C. Simonds
'

bSELCTIONCHANGE = False

Events.Enable_Events

Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True

TimeInMinutes = 15 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

Workbooks("VacationCalendar 2010.xlsm").Save
Workbooks("VacationCalendar 2010.xlsm").Close

End Sub

If it is in excel use Thisworkbook
If it is in wordl use Thisdocument