Close Workbook With Application.OnTime

Jul 28, 2009

I didn't receive a response to a previous post where I was trying to use a batch file to close and open a workbook at a scheduled time everyday. I was thinking about this problem over the weekend, and thought that it would probably be easier to just use a macro to close the file at a certain time everyday (if it is open) and then use a batch file to open the file a couple of minutes later.

I'm having problems with what I thought should be pretty simple though. I tried this to close the file at 5:15pm

View 14 Replies


Ontime Causes Workbook To Reopen After Close

Jun 18, 2007

i want vba to show a msgbox every 5 sec & it should start as soon as the file is opened. Below is the code & it works. Problem is, that when i close the file it immediately reopens and starts the "msgbox proess". Only if i close excel completely, this problem goes away (& thats not a very proffessional way to do it!!). Can someone pl look at this one & help ?

Private Sub workbook_open()
Call Macro4
End Sub

Sub Macro4()
Dim i As Integer
MsgBox "test!!"
NextTick = Now + TimeValue("00:00:05")
Application .OnTime NextTick, "Macro4"
End Sub

View 6 Replies View Related

Application.OnTime Not Stopping

Nov 3, 2006

I am running a timer in XL using the Application .OnTime Method.

Everywhere I've looked tells me that the procedure to stop the timer is

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
End Sub

I have this code in with the timer, but when I hit F5 or go to Tools--->Macros--> and Run the StopTimer Macro the timer just keeps running.

how I can properly call this procedure and stop the timer?

View 9 Replies View Related

Repeat Application.ontime

Apr 26, 2007

how to repeat the following line?

Private Sub Workbook_Open()
Application .OnTime TimeValue("22:02:00"), "Email"

so that everyday it runs at the same time Everyday? At the moment i have to close the workbook at somepoint during the day then relaunch so that it picks this line up, when this eventually "goes live" it will not be monitored as closely as it does now during testing.

View 2 Replies View Related

Application Ontime - Stopping It?

May 25, 2007

I'm using Application OnTime to fire a procedure every 30 mins. It works great, but the workbook tries to reopen itself when i close it (but not if i close excel entirely). I assume this is because I haven't stopped it anywhere. So I put a call to a procedure that has: Application.OnTime RunWhen, cRunWhat, , False

In the "Workbook_BeforeClose" event. This doesn't solve the problem. What am I missing?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_Open()
End Sub


Public RunWhen As Double
Public Const cRunIntervalSeconds = 1800 ' thirty mins
Public Const cRunWhat = "dothis"...............................

View 8 Replies View Related

Application.ontime With A Macro That Needs Parameters

Jun 25, 2009

Is it possible to use a macro that needs parmeters as part of an application.ontime instruction? - I am getting a macro cannot be found error message.
If I change the called macro so that parameters are not required, it is found OK (but obviously isn't doing what I want anymore!).

I reckon it is something to do with the quotations around the macro name

View 9 Replies View Related

Bloomberg BDH Refresh Via Macro Loop Application.Ontime

Dec 10, 2009

Firstly you don't need to know anything about Bloomberg to answer this query, only that Bloomberg has various functions that take time to update.

The code Application.Run "RefreshEntireWorkbook" will update these functions but Application.OnTime (Now + TimeValue("00:00:25")), "RunList" is required to allow them time to get the data from the feed. However this only works if the refresh and wait commands are in a seperate function. So in simplified terms it must go like this in vba:

View 2 Replies View Related

Close And Save Application

Dec 5, 2008

When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application

View 2 Replies View Related

Close Application Via VBA From Access

Dec 24, 2008

Looking for code that will verify if Excel is running and if so, then close Excel, including all hidden versions.

View 9 Replies View Related

How To Auto Close Excel Application

Jun 15, 2014

Any way to auto-close the excel application after finishing my macro run?

I tried this code,but it doesn't work... i added a sub and ran it like this:

Sub CloseandSave()
ActiveWorkbook.Close SaveChanges:=True
End Sub

to be more specific,there is a macro running,closes all open workbooks,but excel itself stays open...

View 4 Replies View Related

Auto_Open And Application.Close Combination

Feb 21, 2008

I have a macro that auto opens does a couple of open and saves but then the Application.Close doesn't want to close the blank Excel screen. Can't you use Application Close in a Auto_Open macro?

Sub Auto_Open()
' Auto_Open Macro
' Macro recorded 1/27/2006 by Rich
' Keyboard Shortcut: Ctrl+c
Application.DisplayAlerts = False

View 9 Replies View Related

Close Application Without Save Prompt

Nov 14, 2006

Is there a code I can use, through a private command button, that will exit the Excel program (not just the current workbook) without asking to save?

View 3 Replies View Related

Close Application Without Save Message

Dec 13, 2007

Application starts with a button on an otherwise empty spreadsheet. Then opens a user form. After I close using a button on the form. My close routine closes the worksheets and workbooks. I then quit the application and I get a message asking if I want save the original spreadsheet.

View 6 Replies View Related

Compile Error On Close Of Excel 2003 But Not On Close Of Workbook

Mar 22, 2012

I am using Excel 2003.

It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.

These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.

I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.

These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.

Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.

View 9 Replies View Related

Cancelling OnTime When Workbook Is Closed?

Feb 9, 2012

How to correct the code below so that OnTime is cancelled when the workbook is closed?

Error on bold line is Run-time error '1004': Method 'On Time' of object'_Application' failed

Public RollTime As Date, CalculateTime As Date, RefreshTradesTime As Date

Sub CancelOnTimeProcess()
With Application
.OnTime RollTime, "RollMacro", , False
.OnTime CalculateTime, "AutoCalculation", , False
.OnTime RefreshTradesTime, "UpdateLinks", , False

[Code] .......

CancelOnTimeProcess is being called when the user closes the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

View 2 Replies View Related

OnTime Method Opening Workbook

Aug 13, 2007

my wonderous spreadsheets have had a number of errors on them. One of them seems that the "autosave" VBA that we have been using seems to be hidden in memory. By this I mean that if I am working on this book and close it, on occassion the workbook tries to open itself again (it prompts for the password). Therefore I think something gets stuck "in memory" but I am not sure why it would act in this way?

The code is as follows:-

Public g_blnDate As Boolean

Public vartimer As Variant
Const TimeOut = 60 'in minutes

Sub Salva()
Call Tempo
End Sub

View 9 Replies View Related

Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook

Jan 23, 2014

I have data that daily needs to be refreshed and printed to pdf.

I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.

I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).

View 4 Replies View Related

Close And Reopen Workbook From Macro Stored Inside Workbook

Mar 30, 2007

I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?

View 4 Replies View Related

Excel 2010 :: Close Second Workbook From Workbook BeforeClose?

May 27, 2014

I cannot make this work in XL2010.

I am opening a second workbook in ReadOnly in the Workbook Open and Closing it in the Workbook BeforeClose.

I don't EVER want to save changes on either workbook.

I need to leave excel open as the macro created a 3rd workbook which the user will need.


View 6 Replies View Related

Copy Range To New Workbook & Close Existing Workbook

Jun 30, 2008

I have a very brief question

dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Windows("Derivative YK pricing Mod G.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook

Currently I have


however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.

View 9 Replies View Related

Application.Run Macro In Different Workbook

Jun 19, 2009

Ive got a script in which I call another script using the Application.Run method. The exact line is below:

Application.Run "'" & wb & ".xls'!save_case", sheetname, fieldvalue
The macro runs fine, but after that a different workbook becomes active. There are a couple more lines of code after the line above that don't get executed (ive tested this by putting a MsgBox command after it).

How do i pass control back to the starting subroutine? Or should it and there is something wrong with my code?

View 9 Replies View Related

Save Workbook As Application

Apr 21, 2008

I've been informed that an excel workbook can be saved as a vba application -

View 3 Replies View Related

VBA To Exit Excel Application Not Only Workbook?

Feb 17, 2012

I have some code that I am using to save and close the active workbook, this code works perfectly, but I need the code to also close the excel application, not just the workbook. I have tried active window.close but it didn't work even though when I recorded the actions that was the code that I was give.

background -I set a task in my OS to open the workbook, then I wrote some code on the trigger to run and then save and close, but only the activebook closes.

View 2 Replies View Related

Save Workbook On Workbook Close

Aug 26, 2006

I snagged this code out from another project I wrote, and it works fine in that one, but doesn't in the new project.

What I want, is when the person presses the exit button, it will save whatever was done, its running as a form printer and database. The code below is the code for my exit button.

Private Sub CommandButton4_Click()
Dim sAnswer As String
Dim i As Integer

' ****************
' ** Exit Button**
' ****************

When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..

View 9 Replies View Related

Save And Close Workbook Using VBA

Dec 11, 2013

How can i save and close one workbook in excel with vba code.

View 2 Replies View Related

Make One Workbook Close Another

Apr 9, 2007

How can I make one workbook close another without saving?
These all work in the immediate window, but not in the
Private Sub Workbook_BeforeClose

View 9 Replies View Related

Close A New Workbook After Email

May 7, 2009

I have the below code in a macro attached to a button. Basically, if i choose yes to the message box at the beginning then the macro copies the all the cells on the sheet 'selections', opens up a new workbook, pastes into here, deletes some unused cells, rows and columns, then sends the sheet as an attachment on an e-mail.

What i also need to do is when i have sent the e-mail, the macro closes the new workbook without saving changes.

View 13 Replies View Related

Code For Close The Workbook

Jul 11, 2009

I know this is the code to close a workbook

View 5 Replies View Related

Close Workbook With Macro

Sep 22, 2009

I have a workbook that contains 4 macros. This workbook is copied by application with another name. When I run the macro in the copied worksheet, the Original file/workbook is opened automatically, and it is open until the copied workbook is closed.

My question:

1) Is this normal, when a copied workbook invokes a macro from the original workbook, the original workbook opens?

2) Is it possible to close the original workbook while executing the macro from the copied workbook?

3) Will that affect the macro in the copied workbook?

4) If possible to close the original workbook, kindly help me with the code.

View 14 Replies View Related

VBA - Close Workbook After 15 Minutes

Apr 2, 2013

I somehow need to add to the section of code below a event to have the "(Public)Archive.xls" to automatically close 15 minutes without saving everytime it is opened.

Kill "H:AllMaterial Prep Archive(Public)Archive.xls"


View 1 Replies View Related

Copyrights 2005-15, All rights reserved