+ Reply to Thread
Results 1 to 13 of 13

automating a macro

  1. #1
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    automating a macro

    For the following piece of code, I click a "button" in the tool bar to initiate this process.
    Please Login or Register  to view this content.
    What I would like to accomplish is running this automatically everyday at a specific time. Any suggestions?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: automating a macro

    Welcome to the forum.

    See VBE Help for the OnTime method, the examples there, and the many examples you can find in the forum.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    Thank you for the welcome. I went to OnTime Method thread and reading through it I cannot figure out how to incorporate it into what I'm trying to accomplish.

    What I have currently is a button that runs this code. I want to do away with the button and have this code run at say midnight every day.

  4. #4
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    Do I create a new macro containing a link to the existing macro or do I add this string to the existing? And should it look like
    Please Login or Register  to view this content.
    as a new macro?

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: automating a macro

    The workbook would need to be open for the code to run, will this be the case when you want the code to run?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    Yes, the Excel Workbook is open 24/7. It is on a machine that monitors critical equipment.

    The only thing I'm trying to accomplish is having the code from the macro I posted above, to run before 8am everyday. I have been researching and researching the OnTime Method and I simply cannot figure it out.

    I am a 100% complete newbie when it comes to this.

  7. #7
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    I tried the line of code I posted before as a macro by its self and as an additional line to the first macro I posted and it didn't work either way.

    Any help would be greatly appreciated.

  8. #8
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    Okay, I think I'm getting closer to a solution. It's not quite working but here's what I have. Can someone look it over and let me know what/if I can fix? I have two macros. One is:
    Please Login or Register  to view this content.
    And the second is:
    Please Login or Register  to view this content.
    I appreciate the help.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: automating a macro

    You've got one sub nested inside another; that code won't compile.

    What is MyMacro supposed to do, if you've already scheduled UpdateSheet_Click to run in the Open event?

  10. #10
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    I got that problem fixed. Moved the
    Please Login or Register  to view this content.
    moved to the "ThisWookbook" and
    Please Login or Register  to view this content.
    It runs once and the update occurs but the next day it doesn't. Am I missing something to get this to repeat daily?

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: automating a macro

    I would reschedule it to run tomorrow like this:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-04-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: automating a macro

    Would that
    Please Login or Register  to view this content.
    Have to be changed to match in "ThisWorkbook" as well for example
    Please Login or Register  to view this content.
    Or would I only need to change it in the module?

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: automating a macro

    When you open the workbook, it schedules the sub once.

    When you run the sub, it schredules itself to run again the following day.

    Think through the the logic, and decide what you're trying to do.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1