Make code aware of the workbook file name

O

ordnance1

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
 
J

Javed

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
 
P

p45cal

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.


My Auto_Open code runs a timer that will close a document if it has bee
open
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top