| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I make VBA code usable on all worksheets of a workbook? | Excel Programming | |||
| Make a shortcut to a file - code | Excel Programming | |||
| Can I make my workbook a demo file? | Excel Worksheet Functions | |||
| Make a text file from Excel workbook | Excel Discussion (Misc queries) | |||
| Code to make sheets in a workbook visible | Excel Programming | |||
Linear Mode
