Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Run Macro In Closed File At Same Time Each Day

i know there is information on this ontime method, i haev read through it, but since i am working with reuters running live in the background they just dont work.
Anyway, on a normal excel sheet, I have made the automated macros run on a timer, which is great. BUT If someone switches off my pc, its not going to be so great because i dont think the macro would run without excel being open.

How can i make my macro run every morning at 8am? Assuming the worksheet is closed, even excel is closed. i am sure the answer is starring me in the face, i am just getting boerd of looking at it! in the Thisworkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "thismodule", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("20:00:00"), "thismodule"
End Sub

and in the Module:

Sub thismodule()
dTime = Now + TimeValue("08:00:00")
Application.OnTime dTime, "thismodule"

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro Needs To Run Each Time I Open The File
I need a Macro to run when a file is opened through a macro on another workbook. The code I have for the workbook that open's the file is:

Sub opensaveclose()
    Application.Workbooks.Open ("C:...testfile_revised.xls")
    Sheets("Call Report").Select
End Sub

Private Sub runmacro()
    Application.Run ("RefreshStats")
End Sub 

View Replies!   View Related
Play Sound File At Set Time Each Day
I'm trying to automate/keep records of my day by setting up a spreadsheet which : 1) Plays a wav music file at a schedule time as a reminder and 2) at the same time calls up a form where I can enter the results of the activity then 3) saves the results to a sheet where consecutive daily records can be recorded and graphed.


At 5am each day I want a "Motivation.wav" music file to open and play and a form appear where I enter in the number of sit ups, push-ups I get done, which I enter in the form and it gets saved to a sheet where I can graph my progress based on the results recorded.

View Replies!   View Related
Run A Macro Automaticly On A Closed Work Book Every Friday
Is there a way to run a macro automaticly on a closed work book every Friday ? I've found ways of doing this using on-open event but It made me wonder if this was possible.

View Replies!   View Related
How To Get A Macro To Run Exactly On The Hour And Day
I do not understand how to get a macro to run exactly on the hour, each hour of a day, day after day?

View Replies!   View Related
Macro To Run On First Day Of Every Month
I need to run a macro on first day (1st) of every month at 07:00 am.

View Replies!   View Related
How To Make A Macro Run Just Once A Day
I am trying to figure out how to make a macro run just once a day -- the first time i open a workbook and then no matter how many times I open that workbook again it does not run......... I know how to get to run automatically when i open the book - just not how to keep it from running again THAT DAY.

Then the next day when i open that book again I want it to run again -- once......and so on..................

View Replies!   View Related
Copy Range From Closed File & Paste To Closed Workbook
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder

View Replies!   View Related
Run Macro Time: Start A Sub Running At A Selected Time
how to include a line of code, to start a sub running at a selected time? Given that the workbook is already opened before that time?

View Replies!   View Related
Run-time Error Every Other Time The Macro Runs
I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.

Every time I get the error, it highlights this line of
Sheets("Regenerate Request").Paste

This is all of the code up to where I get the error:

Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email

Application.Run "LogUnprotect"
Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy
Sheets("Regenerate Request").Activate
Application.Run "RegenFormUnprotect"
Sheets("Regenerate Request").Paste

View Replies!   View Related
Worksheet Change Macro Takes Too Much Time When Run With Update List Macro
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.

My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?

View Replies!   View Related
Run Macro In X Time
2 worksheets.

Sheet 1 has a list of events and their "event start times" in Easter Standard Time. The is the actual data Imported.
15:00:00 in Sydney NSW would be 14:30 West of Sydney, SA,NT ect.

Sheet 2's reference formula to Sheet1's Event Start times is:

'Sheet2 A1
=INDIRECT("Sheet1!A1") ' will show the event start time from Sheet1 A1
'Sheet2 A2
=INDIRECT("Sheet1!A2") '
'Sheet2 A3
=INDIRECT("Sheet1!A3") '
'Sheet2 A4
=INDIRECT("Sheet1!A4") '..............

View Replies!   View Related
Getting Macro To Run All The Time
I have a macro that clears a cell if the value of a cell matches a certain variable:

Sub ClearCell()
With Worksheets("Sheet1")
If .Range("C20").Value = "10" Then .Range("C22").ClearContents
End With
End Sub

View Replies!   View Related
How To Run A Command/macro At A Certain Time
I have an Excel worksheet with a DDE link that continually displays the latest data from a trading platform.

I want to create a second worksheet with a list of times in column A from midnight to 23:30 in 30 minute increments.

At each 30 minutes during the day I want to capture the latest data from worksheet A and store it beside the appropriate time in worksheet B.

But I don't know how to execute a command or macro at a time that is in a cell. My problem is getting the current time to match the cell value.

View Replies!   View Related
Getting A Macro To Run On Time Intervals
Below is the code i want to be run evey 2 minutes Thanks
Sub WebData()

Dim wSU As Worksheet
Dim wSR As Worksheet
Dim wSS As Worksheet

Dim iForRow As Integer
Dim iLastRow As Integer
Dim sURL As String

View Replies!   View Related
Macro Run Time Error 91
Sub asdf()
Dim c As Range
Dim x As Long
Dim y As Integer

Set c = Columns("A:B"). Find(" Total", lookat:=xlWhole)
x = c.Row
For y = 15 To 4 Step -1
If Cells(x, y) = 0 Then Columns(y).Delete
Next y
End Sub
However, it errors out at
x = c.Row
Saying "object variable or With block variable not set"
I have xl2000. Could this be the problem?

With the code I'm trying to locate my "Total" row. Then delete all columns from D:O with a value of zero. An example sheet is supplied at the link.

View Replies!   View Related
Automatically Run Macro At A Particular Time.
I would like to run Macro_1 at every day 3.40pm. I have inserted following code to workbook.

Private Sub Workbook_Open()
Dim when As Variant
Dim name As String
Dim torlance As Variant
Application.OnTime when = "15:40:00", name = ("WeeklyStockReconciliation1")
End Sub

View Replies!   View Related
Run Macro At Set Time
I've got an auto close macro that closes after 8 minutes, however if the user is half way through another macro the application will not close, such as a message box/input box is open. Is there any way to stop all macros running before running the auto close macro to force close.

View Replies!   View Related
Macro Fix Any Run Time Errors
I want to create a Macro that will basically fix any run time errors, that should occur without users having to close the document and reopen. I know I can hit "ctrl G" in the VBA and in the immediate window type in:

Application.EnableEvents = True

and then close the VBA and the macro will work again. I just want this code to be able to be applied by my users witout having to go into the VBA. I will assign this macro to a Button. How can I do this?

View Replies!   View Related
Run A Macro In Multiple Sheets At The Same Time
I have to run the macro in over 75 sheets every month and they are divided in few workbooks. The workbooks have 2 summary sheets and then the sheets I need to run the macro. Can you help me to run the macro in all of these workbooks running it just once?

Sub TelcoTicketsCleaning()
' TelcoTicketsCleaning Macro
' Macro recorded 6/13/2007 by EQUANT
Dim lastrow As Long
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For r = lastrow To 46 Step -1 'Remove rows with DSL, ISDN and PSTN services..................

View Replies!   View Related
Run Macro In HTML File
I have wriiten a macro in excel that pops up some photos whenever the respective cell is selected. It is working good except when I save the file in HTML format. Also what is the difference between publishing and saving html file? I am using office 2003 and microsoft web components installed.

View Replies!   View Related
Open A Closed File To Archive Some Data From A Current File.
I have the need to be able to open a closed file to archive some data from a
current file.

what coding i need to be able to kick the "open file" bit off?

View Replies!   View Related
Check Length Of Time It Takes Macro To Run
I've done quite a bit of searching in the forum and online and haven't found anything that's generic and can be used at anytime.

What I'm looking for is a way or for code that tells you how long it takes a macro to run from start to finish, something that can be used to time any macro. I've seen some threads in the forum where people indicate that it took x amount of seconds for their macro to run but not sure how to do it.

View Replies!   View Related
A Macro Setting That Would Cause A Run-time Error 1004
Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?

View Replies!   View Related
Unexpected Run Time Error 1004 In Macro
I have a macro, operating in my excel document. It works and does it's function. The only problem is every time this macro is running I get the run time error 1004, Method 'SaveAs' of object '_Workbook' failed. After clicking End in the error window it opens up a newly created sheet that I need. I don't need to save the file in the desktop, I need it just to open up like it does right now.

View Replies!   View Related
Sort Macro: Run-time Error 1004
I have created a macro which sorts 4 columns of data in asceding order numerically. I have assigned this macro to a button so that when the button is clicked, the columns of data are sorted.

Unfortunately when the button is pressed i get a vba run-time error 1004. I havent a clue how to resolve this. I recorded the macro useing the excel recorder function as opposed to typing it out manually.

The code created is given below. If i click debug when the error pops up, the code referring to column f:f is highlighted yellow.

View Replies!   View Related
Getting Run Time Error 1004 When I Am Running This Macro
I am getting a run time error 1004 when i am running this macro. I got this macro to my earlier thread. As i am a beginner in a macro, I am unable to identify the problem. I have attached the workbook for the same

View Replies!   View Related
Run Macro Based On Time In Cell Every X Minutes
1st post. Very basic understanding of Excel / Macros / VBA but I have searched and still not quite able to get what I'm looking for.

I would like to be able to manually put in a TIME in a cell, and have a macro run at set times before that TIME e.g. something like
If TIME in cell A1 =(hh:mm minus 30mins) run macro 1,
If TIME in cell A1 =(hh:mm minus 5mins) run macro 2

It was suggested to me to use vba code that would constantly check the time against the system time and as soon as it is 30 mins before the time in cell A1 and the 30 mins flag in cell B1 was ‘N’ then it would run macro 1 code and set the 30 mins flag to ‘Y’ to show that macro 1 had been run.

and that this could also do the same for the 5 mins event

View Replies!   View Related
Auto Run Macro Every X Minutes Or Time Specified In Cell
The below seems to work but I'm wondering if there might be a better way. I'm trying to keep an ongoing up-to- date and accurate time. my code is as follows:

Private Sub Workbook_Open() ' placed inside thisworkbook
Call TimeUp
End Sub

Sub UpdateTime() ' placed in module
If Range("A4") = TimeValue("00:00:00") Then
Application.OnTime Now + TimeValue("00:01:00"), "TimeUp"
End If
End Sub

Sub TimeUp()
[a1] = Time
End Sub

Sub UpdateTime2()
[a1] = Time
Application.OnTime Now + Range("A4").Value, "TimeUp"
End Sub

Does anyone know if there is any way to improve the Code or formulas within the Cells?

View Replies!   View Related
Suppress Macro Code Run-Time Errors
how to supress the continue,end,debug, message when there is a vba error. The idea being that if there is a bug in my system that I have no realised, I don't want my end user seeing that message! I would preferrable design my own error message to appear instead.

View Replies!   View Related
Continually Run Macro Code At Time Intervals
I am trying to continually refresh a spreadsheet (it is a countdown timer), and i am looking for a way to do this other than hold "F9". It is excel 97.

View Replies!   View Related
Run Macro If File/Workbook Name Matches
I have a macro attached to a button. The file with this button is distributed among several people via outlook. The macro can only then work properly if the user saves the file on his/her hard drive with its original name. Outlook sometimes automatically saves the files as: Copy of... etc. I was wondering if there is a way to add a condition to the code so that the macro could only then be executed if the name of the saved file was exactly the same as stated in the code.

View Replies!   View Related
Auto Run Macro When File Is Opened
I am using the following code and putting it into a module.

Private Sub Workbook_Open()
MsgBox "Hi, thanks for opening me", vbInformation, ""
End Sub

when i open the file and click on allow macros it does not run.

View Replies!   View Related
Table Which Shows Time Started, Time Finished And Then A Total For Hours That Day, Then That Week
i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.

Start 08:00
End 16:00
Total 8 hrs.

How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?

View Replies!   View Related
Worksheet_Change Macro (run Any Time There Is A Change In Any Of The Cells On The Worksheet)
determine the coding for a worksheet_change macro for the attached spreadsheet? I've got a macro set up in the worksheet and would like it to run any time there is a change in any of the cells on the worksheet.

View Replies!   View Related
How Do I Get A Certain Macro To Run Anytime I Open Any Excel File
I want to put in an if-then statement in a macro that will check to what kind of report i open so it will automatically know what to do with it. Where do I put that macro? I tried it in auto_open in my personal.xlsb file but it stops before the file actually opens.

View Replies!   View Related
Run A .bat File From A Macro (using A Relative Path)
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.

View Replies!   View Related
Random Errors When Macro Is Run In A Bat-file
I'm running loads of SQL-queries in a bat-file (using the isql-command). After that has been done I want to run Excel to format the result of the queries, and save it as a excel file on a networkplace. The SQL-queries are working fine, and the macro is working fine, when run seperatly, but since I cant predict how long the SQL-queries will take I have to either give it an extra long time before I start with the Macro, or run then in a chain from the bat-file.

When I do that I usually end up with random errors in Excel. Most usually it is "Not enough memory to display completly". This error is shown before the actual workbook is loaded, and therefore no macro has been loaded either so there's nothing I can do from there.

When I click OK the macro runs as should be though, but since it should be run at 5 in the morning it will just stand there until I come into work each day, and have loads of complaint that they havent got their figures yet.

I have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.

View Replies!   View Related
Run-time Error '1004':File Not Found :: Folder On Network
The script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.

It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.

I cant for the life of me work out a cause.

More baffling, the wording of the error says that the file 'R:DirCancellations.xls' could not be found.

At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?

Sub Search()

Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim DirLoc As String
Dim PolNum As String

View Replies!   View Related
Adding A Time Offset Based On Time Of Day
I am replicating a Matlab program which calculates tide levels at different times of day. I need to replicate it in excel to speed up data analysis and I am nearly there.

what happens is I need to apply a time offset to the time of high tide at port a based on the time of day, so if it is:

between 00:00 and 06:00 the high tide at port b is 81.6 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 74.56 minutes after the peak at port a
between 12:00 and 18:00 the high tide at port b is 81.75 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 79minutes after the peak at port a

I tried this formula, where CO2 has the time/date of the high tide at port a:

The problem is the high tide on 07/01/2005 07:45 is read as 38359.32 rather than 0.32 - is there any easy way to tell excel I'm only interested in the time not the date? I have this spreadsheet setup now to do all the other bits required and i is just the timing that is a problem.

View Replies!   View Related
Time W/in Each Hour Of Day Btwn A Time Range
I need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers.

For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).

View Replies!   View Related
Run Time Error 1004 (macro Called From Access Module)
i am running a macro thru vba (beiing called from a ms access module) and am getting a RuntTimeError 1004.

the code opens a workbook...then open a second workbook (which houses the macro) then activates the desired worksheet and call the Maco via the run command but errors out.

if i open the workbook and set focus on the desired sheet ....tool>macro>desired macro name it runs fine.

the line of code in the macro is: "ActiveCell.Offset(1, 0).Activate"

View Replies!   View Related
Run Excel Macro From Batch File. Workbook Open
I wanted to run a batch file which does something like,

Dir /b >Test.csv
Start Test.csv

And then runs an Excel macro once the workbook is open.

I looked into this and discovered that it’s not really that possible to run an excel macro through batch file but other options were to use ‘Workbook_Open’ etc.

I thought this might cause problems because Test.csv doesn’t contain a macro to auto open and because csv’s usually don’t like macros.

I do however have personal.xlsb which opens every time if that helps.

View Replies!   View Related
Schedule Macros To Run While Workbook Is Closed
I'm trying to look at options for scheduling macros to run at a particular time. Ideally I would like to run one at 3:00 am during every week night. From what I've looked into, I can run the OnTime procedure and it could schedule this to run each night. Does anyone know if this is correct, and if there are any other options avaliable to me?

Also, does anyone know if this can run automatically if Excel is closed, or does Excel have to be open? Does the workbook have to be open?

View Replies!   View Related
Run A Vba Code When Sheet Is Closed
I want to run vba code when a excel worksheet closes. But I want this code to be used for every worksheet that opens. So the macro or vba code has to be loaded with Excel automaticly. I don't want to reenter the code in every template I use.
In access you can attach a module that runs when access or the application is closed: 'Private sub form_close()' Is it possible to create something like this in Excel.

An explanation what I want to do:
I Open/create a workbook from within access. When the worbook closes I want to look with vba if there is a cell called " Total" and if so I want to open a access databases and submit the total to the database. I don't want access to wait untill the worksheet is closed. If the worksheet stays open for a longer time I want to work on in access with other tasks.

View Replies!   View Related
Worksheet Open Event: Macro Run Every Time A Sheet In A Workbook Is Accessed
Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.

View Replies!   View Related
Creating A Time Card HR:MM Not As Time Of Day
I have a time card that is split into sections so there are a lot of SUM formulas. However, I need to include a space for breaks - 10 min.

How do I make everything convert comfortably?

Ex. 5 hours allotted to person A. 5 hours to person B. 1 hour 40 allotted to tasks. And 20 min per day for breaks.

Is there a way to format the cell so that it displays hours and minutes
1:35 = 1hr35min ??
All the formats available show it as time. I need it as a quantity of time rather than time of day so that the formulas will add correctly.

View Replies!   View Related
Saving A File With A Different Name Each Time Using A Macro
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.

Is this possible and if so what VBA code

View Replies!   View Related
Macro, Export To Text File, One Row At A Time
I have a spreadsheet that currently looks like ........

My problem is that I want the macro to create either (a) a seperate text file for each line (1 for line 2, one for line 3 etc etc) or even better (b) create a file for the specific line i am on. For example if i happended to click on D5 and then pressed the macro key it would onyl create a txt file for line 5, same if i clicked on E6, it would only create a file for line 6.

I would be grateful if you could highlight the code you insert into my macro as it may be useful for other reports i run.

View Replies!   View Related
Run-Time Error '1004' Cannot Open PivotTable Source File 'Consolidated Report'
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous

View Replies!   View Related
Sumif For Values From Closed File
I am trying to summarise data from 50 different files (updated by 50 different users) into one file in one place. To do this I created a simple sumif function that links to those files. This sumif works correctly when those files are open. But when I open my output file after changes have been made to one or more of 50 input files, sumif function doesn't work when I hit update at the file opening pop up question and get #value error. If I start opening those files this error goes away and it works great again.

View Replies!   View Related
Copyright © 2005-08, All rights reserved