Autorun a macro at preset times

I have a macro that draws data from a website and places in to several tables 
which are then organised is different criteria. Instead of me manually 
hitting the button with assigned macro, I want to have the macro run itself 
every 10 minutes. How do I do this please?
-1
Utf
2/11/2010 4:23:01 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1152 Views

Similar Articles

[PageSpeed] 56

Darren,

have a look at ONTIME in Excel VBA help

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Darren" wrote:

> I have a macro that draws data from a website and places in to several tables 
> which are then organised is different criteria. Instead of me manually 
> hitting the button with assigned macro, I want to have the macro run itself 
> every 10 minutes. How do I do this please?
-1
Utf
2/11/2010 4:28:01 PM
You can use this line of code:

Sub YourSub()

    ' your code here
    
    Application.OnTime Now + TimeValue("00:10:00"), "YourSub"

End Sub

I would also suggest you reading Chip Pearson's site about the OnTime Event. 
 Hope this helps!  If so, let me know, click "YES" below.

http://www.cpearson.com/Excel/OnTime.aspx

-- 
Cheers,
Ryan


"Darren" wrote:

> I have a macro that draws data from a website and places in to several tables 
> which are then organised is different criteria. Instead of me manually 
> hitting the button with assigned macro, I want to have the macro run itself 
> every 10 minutes. How do I do this please?
1
Utf
2/11/2010 5:04:12 PM
Hi Mike, I did as you suggested and this is what I come up with:
Sub Autorun()
'
' Autorun Macro
'

'
    Application.OnTime Now + TimeValue("00:10:00"), "Refresh"
End Sub
Unfortunately it only repeats once. How to I make it repeat itself at the 
stated time intervals i.e. every 10 minutes?

"Mike H" wrote:

> Darren,
> 
> have a look at ONTIME in Excel VBA help
> 
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Darren" wrote:
> 
> > I have a macro that draws data from a website and places in to several tables 
> > which are then organised is different criteria. Instead of me manually 
> > hitting the button with assigned macro, I want to have the macro run itself 
> > every 10 minutes. How do I do this please?
0
Utf
2/11/2010 5:08:01 PM
You are telling the OnTime Event to run a macro named "Refresh"  you want to 
re-run "Autorun".  Try this line below or see my reply to your original post. 
 Hope this helps!  If so, let me know click "YES" below.

Application.OnTime Now + TimeValue("00:10:00"), "Autorun"
-- 
Cheers,
Ryan


"Darren" wrote:

> Hi Mike, I did as you suggested and this is what I come up with:
> Sub Autorun()
> '
> ' Autorun Macro
> '
> 
> '
>     Application.OnTime Now + TimeValue("00:10:00"), "Refresh"
> End Sub
> Unfortunately it only repeats once. How to I make it repeat itself at the 
> stated time intervals i.e. every 10 minutes?
> 
> "Mike H" wrote:
> 
> > Darren,
> > 
> > have a look at ONTIME in Excel VBA help
> > 
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "Darren" wrote:
> > 
> > > I have a macro that draws data from a website and places in to several tables 
> > > which are then organised is different criteria. Instead of me manually 
> > > hitting the button with assigned macro, I want to have the macro run itself 
> > > every 10 minutes. How do I do this please?
0
Utf
2/11/2010 5:21:01 PM
Thankyou.

0
Utf
2/11/2010 6:15:03 PM
Reply:

Similar Artilces:

autorun a macro
Hi Experts, Can someone help me out. I have a spreadsheet with the code Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Range("B1").Value = "" Then MsgBox ("s.v.p. je naam invullen!") End If End Sub in ThisWorkbook. The spreadsheet must be filled in by various colleagues. On opening the file a dialog about turning the macro's off or on, I know for sure my colleagus will push the first button and will turn off the macro. Is there a way to run the macro automatically, so without the dialog. Altering the secutiry level is n...

formula for adding time to time eg 90min to 6.30am
I am designing a spreadsheet for work which will record the Start, Complete and Pop time for plastic products produced by our factory. I am having trouble getting the formula correct for it. I need it to be able to calculate the Complete and Pop times when I enter the product code and it's cycle time. EG Start 6.30am, Complete +90minutes, and Pop +120 minutes. How do I write a formula to do this. Also how do I write a formula/name range so that when I enter the product code it will recognise this and add the relevant cook and cool times to the sheet. We will have atleast 35 diff...

How to execute a macro automatically on startup
Currently I execute a macro either with a short-cut key (^w) or a custom menu entryon the TOOLS menu. Is there any way to automatically execute the macro when excel starts up (i.e. open excel to the desired workbook, and automatically run one of that workbook's macros on startup. ) Thanks, John G. Open the workbook, do Alt + F11, double click ThisWorkbook in the left project pane Private Sub Workbook_Open() End Sub put either a reference to your macro like Private Sub Workbook_Open() Macro1 End Sub or put the whole code in there -- Regards, Peo Sjoblom "John Gilchr...

Trouble with "time" in formulas
I have a time sheet that looks something like this: A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>time(4,0,0)),A3,time(4,0,0)) A1 and A2 are set up as dropdown lists and the times in the list are i increments of 15 minutes. WHat I'm trying to accomplish is (in cell A4 if A3 is between 15 minutes and 4 hours, then return 4 hours. if A3 i 0 or greater than 4 hours, return A3. If I enter enter times in A1 around 00:00 and A2 a little after 00:0 it works fine... example: A1=22:00...A2=01:00...A3=03:00...A4=04:00 A1=20:30...A2=00:15...A3=03:45...A4=04:00 A1=00...

Macros n Sheet/Workbook protection
Hi everyone, We use excel 2000. I have setup an excel file to serve as a stationary requisition at work.. all works well.. but now i need to ensure that no one changes the format / formulas by error. The sheet has 3 macros.. - to send form by mail - to be used by all - print - to be used by all - Update a database - for the admin dept only If i protect the sheet... the macros will not work.. is there any way of working around this problem...users should not have to key in passwords.. Lynne Lynne, You can still run a macro on this sheet when it is protected, use something like this Ac...

Macro Combine Error?
Hello, If someone can tell me where the error is in this macro, I would greatly appreciate it. What I am in need for this macro to accomplish, is to combine all sheets to one master sheet. For some reason I am testing it on three sheets and the output is not correct? Thanks JR Sub Combine() Dim J As Integer On Error Resume Next Sheets(1).Select Worksheets.Add ' add a sheet in first place Sheets(1).Name = "Combined" ' copy headings Sheets(2).Activate Range("A1").EntireRow.Select Selection.Copy Destination:=Sheets(1).Range(&q...

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

Macro to highlight every 2nd row
Hi, I am trying to work out how to write a macro so that it takes my data range and highlights every second row in a certain colour. if my data is in the range A2:K31, then i want the macro to run so it selects A3:K3 and highlights that in yellow, then skip down two rows and highlights A5:K5 in yellow and so on until there is no more data in the cells (ie. Row 31). the code I have so far is this, but it only highlights every second cell in column A and does NOT extend out to column K... Sub NEWY() Range("A2:K2").Select Do Until ActiveCell.Value = Empty ...

$500 A Day
$500 A Day - Your Work From Your Home Part Time! -Your job will consist mostly of sending and receiving emails. You will also be filling in short forms on the internet. For every email that is successfully processed you will earn $15 to $25 depending on the email. http://tinyurl.com/dhwqvt ...

Start Up Macro
How do I create a macro that runs automatically when the spreadsheet i opened -- colinfraser@prosealuk.co ----------------------------------------------------------------------- colinfraser@prosealuk.com's Profile: http://www.officehelp.in/member.php?userid=523 View this thread: http://www.officehelp.in/showthread.php?t=126905 Posted from - http://www.officehelp.i right click the excel icon in the upper left next to file>select workbook open> put it in there -- Don Guillett SalesAid Software dguillett1@austin.rr.com "colinfraser@prosealuk.com" <colinfraserprosea...

macro screen
Is htere a way to bypass the macro security screen? (the one that asks to enable or disable? Set the macro security level lower -- Don Guillett SalesAid Software donaldb@281.com "Edwin Merced" <edwinmerced@coqui.net> wrote in message news:u9tem8upDHA.1708@TK2MSFTNGP12.phx.gbl... > Is htere a way to bypass the macro security screen? (the one that asks to > enable or disable? > > Tools->Macro->Security Set the value to "low" If you do this, make sure that you know the source of any workbooks that you open! -- Regards; Rob Please reply to th...

How do you view an active macro code?
I have a macro code on one of my worksheets, but can't seem to find out how to open it in VBE to edit it. Seems like a simple resolution, but I can't for the life of me get the existing code to display, but I know it's there. Thanks. Alt+F11 then in turn double click the worksheets and 'ThisWorkbook' until you find it -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Scott" wrote: > I have a macro code on one of m...

Macros #55
Can a macro be made to go to specific web sites and collect data? Do macros work with Quickbooks ? Is this technology just not out yet? Or atleast that simple to do yet? -- famdamly ------------------------------------------------------------------------ famdamly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29382 View this thread: http://www.excelforum.com/showthread.php?threadid=500324 1. Definitely. Have a look at data>import external data> 2. Dunno. Excel can do it all -- Don Guillett SalesAid Software dguillett1@austin.rr.com "famdamly&...

Help with Macro (copying data from multiple workbooks)
All, How can I set up a macro which copies data in the same column and cell but from different workbooks or worksheets..... Thanks Tim Tim, This example will copy the data from range A1:A6 from Sheet1 to Sheet2 within the same workbook: Sub CopyRange1() Range("A1:A6").Copy Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub This example will copy the data from range A1:A6 from Sheet1 in Book1 to Sheet1 in Book2: Sub CopyRange2() Range("A1:A6").Copy Windows("Book2"...

Embedded PDF object in web page: OneNote is launching and opening PDFs at the same time as Adobe Reader
I have a Windows 2008 Server machine with Office 2007, Adobe Reader 9, and IE8 installed. When opening a web page with an embedded PDF control (using <object> tag and Adobe Reader's CLASSID) in IE8, the PDF appears properly in the Adobe PDF control embedded in the web page, but at the same time, OneNote launches separately and opens the same PDF. How do we stop OneNote from doing this? We have XP Pro machines with Office 2007, Reader 9, IE8 that do not exhibit this behavior. -- Paul -------- Original-Nachricht -------- > I have a Windows 2008 Serv...

Open & update another file (through macro) while running macro
I am using MS Excel 2003 and I need help to solve my problem. One excel file (suppose A.xls) in which I’ll update my graph and the data is available in another file (suppose B.xls). What I am doing (through macro) is that I have created one button (in A.xls) and when I pressed it; its open the file B.xls (the data file) and at their I select month from drop down from which its update the pivot table based on the selection month and then same I was doing with another data excel file (suppose C.xls). Query : Is there any solution that when I pressed button from file A.xls its ...

Macro Setting
I am setting up a macro and in it the is a "make a table" query that would require the user to click on "yes". Is there a way that popups don't appear and yes is the default? Preceed it with SetWarnings to No, then the query, and follow with SetWarnings to Yes. -- Build a little, test a little. "H" wrote: > I am setting up a macro and in it the is a "make a table" query that > would require the user to click on "yes". Is there a way that popups > don't appear and yes is the default? > . > ...

activate macro
how do i automatically run a macro when i move from one worksheet to another. ie select another worksheet, then automatically run a specific macro pat Patrick, Put this sub in the ThisWorkbook module. Sh will be the worksheet that was just activated. Private Sub Workbook_SheetActivate(ByVal Sh As Object) MsgBox Sh.Name ' take out the line above and put in your code End Sub -- Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets ...

How do I get a macro to operate only on cells in specific columns?
I want to do a Trim on non-formula cells in specific columns. Currently the Trim macro (which works, but is slow because of hitting all cells), looks like this:- Sub Trim_Text() Dim cell As Range On Error Resume Next For Each cell In ActiveSheet.UsedRange With cell If .HasFormula = False Then .Value = Application.WorksheetFunction.Trim(cell.Value) End If End With Next cell End Sub and I want it to work only on cells in these columns Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,Y:Y,AB:AB,AE:AE,AH:AH,AK:AK,AN:AN,AQ:AQ&q...

Delete Macros Macro
I have some xl files that have many macros in them. For example 20 xl files with 10 macros in each file. I have the following macro in it's own xl file that I use to delete the macros in the other files before I save the xl files in another directory. Sub zzzDeleteMacros() Dim vStop As Double vStop = 0 Do Until vStop = 50 Application.SendKeys Keys:="%tmm", Wait:=False Application.SendKeys Keys:="%D", Wait:=False Application.SendKeys Keys:="Y", Wait:=False vStop = vStop + 1 Loop End Sub This works fine exce...

List of Macro Commands for GreatPlains
Subject speaks for itself. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=3a6e5104-41dd-4543-8a6f-ecd818c61777&dg=microsoft.public.greatplains The list of Macro comman...

Macro to update a column in a work based on another work sheet
My excel knowledge is enough to be dangerous. I have two work sheets. Both have multiple columns and two of the columns are then same on both work sheets. Currently, I do all the updating by hand for these columns: case # and status. I really need a macro or a way to update the two columns on work sheet 2 based on what is in work sheet 1. It woudl compare a case #s and then update the status column on work sheet 2 to match the status in work sheet 1. I see alot with vlookup, never used it. Regards, Jeb Hi, I assume your case # in sheet1 is in column A, your status in sh...

2003 OWA
Hi there,We have been noticing that the images which make up the mailbox user interface are not being displayed to users over the weekend period like an access restriction is in place.This renders the interface useless and I cannot find a setting anywhere that could be causing this.Can anyone offer any advice? Thanks in advance. Colin I don't know of any way to make this kind of setting in Exchange or IIS, so I have to believe that you have a firewall between the client and server that's causing this problem. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and...

How to delete sheets using macro?
There is a list of sheet name under column B of sheet "Date", I would like to delete any sheets, which name is not included within the lists and the Date sheet cannot be deleted too. Do you have any suggestions on how to code a macro to do it? Thanks in advance for any suggestions Eric Eric, Try this Sub del_sheets() Dim ws As Worksheet Set sht = Sheets("Date") Dim LastRow As Long LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & LastRow) Application.DisplayAlerts = False For Each c In MyRa...

Macro and Protection
Hi, I am responsible for a spreadsheet that calculates the cost of sales of a chain of restaurants in the UK. The chain is linked via intranet and a remote desktop. The spreadsheet is fairly easy to use, however due to the fact many people using it aren'computer literate, very often the ss fails with the REF! error message. It always happens when people drag and drop values. It is very easy to 'repair', but all formulas are locked, so I have to open the ss, unlock it and fix it. Can a Macro do it, as it can do it when the sheet is protected, but pword free, but is there a way t...