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
0
Utf
4/24/2010 4:48:01 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
829 Views

Similar Articles

[PageSpeed] 14

On Apr 24, 9:48=A0am, ordnance1 <ordnan...@discussions.microsoft.com>
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 wa=
nt
> 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 th=
ere
> anyway that the code can be made aware of the document name? I can not us=
e
> 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 =3D False
>
> Events.Enable_Events
>
> Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
> Application.DisplayAlerts =3D True
>
> TimeInMinutes =3D 15 'Timer is set for 180 minutes; change as needed.
> If TimeInMinutes > 5 Then
> TotalTimeInMinutes =3D (TimeInMinutes * 60) - (5 * 60)
> =A0 =A0Start =3D Timer
> =A0 =A0Do While Timer < Start + TotalTimeInMinutes
> =A0 =A0 =A0 =A0DoEvents
> =A0 =A0Loop
> =A0 =A0Finish =3D Timer
> =A0 =A0TotalTime =3D Finish - Start
> =A0 =A0Application.DisplayAlerts =3D False
> End If
>
> Start =3D Timer
> =A0 =A0Do While Timer < Start + (5 * 60)
> =A0 =A0 =A0 =A0DoEvents
> =A0 =A0Loop
> =A0 =A0Finish =3D Timer
> =A0 =A0TotalTime =3D Finish - Start
> =A0 =A0Application.DisplayAlerts =3D False
>
> =A0 =A0Workbooks("VacationCalendar 2010.xlsm").Save
> =A0 =A0Workbooks("VacationCalendar 2010.xlsm").Close
>
> End Sub

If it is in excel use Thisworkbook
If it is in wordl use Thisdocument
0
Javed
4/24/2010 7:36:35 AM
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/showthread.php?t=198152

http://www.thecodecage.com/forumz

0
p45cal
4/24/2010 7:40:58 AM
Thank you sir.

2 birds one stone (save and close)

"p45cal" <p45cal.49wx3n@thecodecage.com> wrote in message 
news:p45cal.49wx3n@thecodecage.com...
>
> 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/showthread.php?t=198152
>
> http://www.thecodecage.com/forumz
> 
0
ordnance1
4/25/2010 3:01:37 AM
Thanks for taking the time to help

"Javed" <asadullajaved@gmail.com> wrote in message 
news:823aa4ae-8204-4343-9979-6d120477ab7e@v27g2000pro.googlegroups.com...
> On Apr 24, 9:48 am, ordnance1 <ordnan...@discussions.microsoft.com>
> 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 

0
ordnance1
4/25/2010 3:02:47 AM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Fax Icon on my File menu and toolbar
I had a Fax Icon on my toolbar and also on the file menu. All of a sudden it is gone and I want it back. Where did it go? Was it a dedicated toolbar? Or was it just icons on a builtin toolbar? If it was a dedicated toolbar, you may get lucky and find it under: Tools|customize|toolbars tab (just not selected) But if it was on a builtin toolbar (along with the file menu), then maybe you reset your toolbar--also under: Tools|customize|toolbars tab selecting a toolbar and hitting the reset button. If that's what happened, I think I might try to find the Fax addin/workbook that added t...

exporting outlook 2000 pst files to a database
I need to export outlook 2000 to a database- attachments and all. Is there a simple way to do this so you can search and retreive messages and attachments in a database form? Thanks Jeff You can export individual folders to access or excel to have them available for searching. I have never tried to export my entire .pst file to access or excel however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jeff <laacid@yahoo.com> asked: | I need to export outlook 2000 to a database- ...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

Outlook receiving zip files
Why is it when I try to send zipped files to my address; it gets returned as service unavailable? Hello Omar, your Exchange dont allow you to send this! Please speak with your Domain Admin "Omar" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0754E7DA-31DC-4966-9FC1-C290A8D8222C@microsoft.com... > Why is it when I try to send zipped files to my address; it gets returned as service unavailable? Hi Omar, your Provider don�t supportet this part of file! -- Gestern lief noch alles, ich habe nichts gemacht! Bitte in der Newsgroup antworten, damit jeder d...

unable to read file #7
Does anyone know how to solve this error? This is the second time I have had the same error on the same file. Last time I was able to recover from a saved copy and updated fine. This time whenI opened the copy and updated it, then saved it, I got the same error when I tried to open it again, Now I don't have a good copy. I have downloaded all avaliable updates and tried running "Excel.exe /regserver>ok" This did not help. I also ran defran on the drive. "Randell" wrote > Does anyone know how to solve this error? This is the > second time I have had th...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

Importing AOL Email File Cabinet
Is it possible to tranfer a complete AOL Email File Cabinet into outlook? AFAIK, no, but see if anything here will do it: http://www.slipstick.com/config/convmsg.htm - check out Address Magic Plus first, it can convert almost anything. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http:/...

making filtering rules?
hi, i am trying to create a rule that goes something like if (site1.com OR site2.org OR site3.gov) AND (agri OR agriculture OR agribusiness) then move to folder X that is, if it is from any of those sites AND has any of those keywords then move it. The problem is i can't seem to figure out the specifying the AND part. Any help/ideas would be greatly appreciated! Cheers -Gaiko ...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Exporting contacts in a csv file
I am a mysterious problem exporting my contacts as csv file. The resulting file has only 58 contacts out of almost 1200 contacts in my outlook. These contacts appear to be the last 58 contacts added. (I say appear because the attribute "createdon" is not exported so I can't be exact. Why is this? cinnamngrl <cinnamngrl@gmail.com> wrote: > I am a mysterious problem exporting my contacts as csv file. The > resulting file has only 58 contacts out of almost 1200 contacts in my > outlook. These contacts appear to be the last 58 contacts added. (I > say appea...

is there a way to program my Excel file to do a loop?
Hi all, If I want B10 to B17 all follow the change of the same number(copy cell), let's say I put it in A1, and C10 follows the change of A2(copy cell), and C11 follows the change of A3(copy cell), and C12 follows the change of A4(copy cell), then I have 4 variables in my calculations: A1, A2, A3, A4. I want to loop each of the variables in a different set, then I hope the whole worksheet will be able to refresh following the change of A1, A2, A3, A4, and then I want to find the very set of A1, A2, A3, A4 that gives the smallest value of D10, how do I program the whole procedure...

Publisher can not save file
I recently started having troubles with my Publisher 2003. Whenever I go to save my files now, using save as or just the save button, it gives me a dialog box that says "Can not save file." It does that twice, then it disappears. I also noticed that when it does this, it leaves the .tmp files in the directory where I tried to save. I can change the name of the files, and sometimes it will save it. Most of the time not though. I have NAV, and I noticed that it was said there was an issue with Publisher and NAV. Is this the same with the 2005 version, and is this anything anyone...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is called Book1 and the other document is the actual document that I want to open. How do I stop this from happening? Brian To prevent Book1 from opening you can append /e to your shortcut for opening Excel. "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e Alternative to the above............ Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel Worksheet. Then if running Win98 OS Edit> select "Open" and Edit. If using WinXP OS you would scroll down to...

Continuing problmes with chkdsk and PST file
My PST file is apparently corrupted, with "Data error, cyclic redundancy check" upon starting Outlook. As per earlier advice, i ran chkdsk /r. But, chkdsk hangs. The % done on phase 4, file data check, was moving along at 1% per every 1 to 5 minutes. Then, at exactly 50%, it just stopped moving. I left it for a few hours, but no further progress (so i interrupted it). The disk light was on continually, but no progress in % complete. Also, if scanpst.exe is run, windows crashes (blue screen). So, are there any further suggestions? Is there any disk utility more robust t...

Numbering in Book Files
I am creating a tech manual where each chapter is its own file. I have created an outline numbered list and saved the list in a template that I then use to create the new chapters. However, I am finding that when I go into a new chapter that the outline numbering does not pick up on the chapter (Heading 1) number. For example: Chapter 1: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. But when I start my next chapter, when I update the chapter number to start at 2, I get this: Chapter 2: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Restoring Outlook Files
hi, I need to move the actual inbox, etc. from one hard drive to another. Which files do I look for? I had Outlook configured as an IMAP client. I keep looking for a master "inbox" or "mailbox" file, but am unable to find it. thanks for the help! rt brown Look here for all the information you need on backup and restoration of Outlook data: http://www.slipstick.com/config/backup.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:02b50...

Outlook 2000 slow to attach files
I am running Outlook 2000 with XP, and when I go to attach a file on the network, it is extremely slow. However, when I go to the same file through Windows Explorer, it is very quick. Does anyone know what this problem might be or how to fix it? ...

Change Domain Name on outgoing Emails
Our company just purchased another company with their own Exchange Server and AD infrastrure. We want all users in this new facility to have Email addresses with our Domain such as username@abc.com instead of their current Domain username@123.com. Until I migrate resources from their Forest into our Forest I have created contacts to forward all Emails from the abc Domain to the 123 Domain. When users reply or send Emails from the 123 Domain it still has their username@123.com Email address which will cause confusion with our customers and suppliers. How do I force their Emails to us...