+ Reply to Thread
Results 1 to 9 of 9

Automatically update year interval cycles from year to year

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    D.C.
    MS-Off Ver
    2010
    Posts
    4

    Exclamation Automatically update year interval cycles from year to year

    Hey everyone,

    My name is David. I have been working with Word and Excel all my life but now I have the need to learn about Macros. I'm trying to streamline my process by using macros. Hopefully someone can help me here.

    Backdrop:

    I have worked as a software tester for about 7 years now. At my company we update word and excel documents on a cycle to cycle basis. On our word documents there are references to what cycle year we are testing in. For example, this years cycle year is 2015-2016 (56). The next cycle would be 2016-2017 (67) and so on. We have these cycle year references throughout the entire document. These documents go to our client and get reviewed thoroughly. In reviewing any scientific documents, if they are errors with something as simple as a cycle rollover from year to year, the clients thinks what else could be wrong with the document. I tried using the macro function and succeeded but the issue is that it doesn't update from year to year.

    Question:

    Is there a function within the macro process that allows for yearly updates (as stated above) to the document to be processed? I have attached an example of an word document for visual aid purposes.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 08-20-2014 at 04:05 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,853

    Re: Newbie to Macros

    This is an Excel forum, but you can try this macro. Reply with the earlier of the years you want to replace.

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,575

    Re: Newbie to Macros

    trumptight, please update your thread title to something that gives insight to the question you are asking.

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    08-20-2014
    Location
    D.C.
    MS-Off Ver
    2010
    Posts
    4

    Re: Need help with creating a Macro for Word Document.

    Thanks Bernie. I'll try it.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,575

    Re: Need help with creating a Macro for Word Document.

    trumptight, I appreciate the effort but this thread is in a forum for Word macros--every thread here is asking for help for creating a macro for Word. Because it is your first thread I have updated it for you. Please keep this in mind for the future.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,853

    Re: Need help with creating a Macro for Word Document.

    I did not realize that there was a Word macro forum here as well - learn something new every day

  7. #7
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,531

    Re: Automatically update year interval cycles from year to year

    Instead of messing around with macros, I'd suggest using a custom document property to hold the start year number (e.g. named 'StartYear'). Then, in the document itself, all you need to replicate that is a DOCPROPERTY field, coded as:
    {DOCPROPERTY StartYear}
    You can have as many of these as you like throughout the document.

    For the end year, you have two options:
    1. use a formula field coded as {={DOCPROPERTY StartYear}+1}; or
    2. use another custom document property to hold the end year number and corresponding DOCPROPERTY fields.
    The first option is slightly easier to set up & maintain, whilst the second option gives greater flexibility for the # years between the start and end.

    No VBA required. All that is needed to update the document content after changing the custom document property values is to use Ctrl-A, F9. It can also be made to update when printing by ensuring Word's 'update fields before printing' option is checked.

    Of course, the updating of the custom document property/properties and document content could also be automated. This would still be more flexible than trying to maintain everything directly in the document via a macro.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    Registered User
    Join Date
    08-20-2014
    Location
    D.C.
    MS-Off Ver
    2010
    Posts
    4

    Re: Automatically update year interval cycles from year to year

    Man I wish I knew what you talking about Macropod lol. Is there anyway you can show me an example that I can use and update?

    Quote Originally Posted by macropod View Post
    Instead of messing around with macros, I'd suggest using a custom document property to hold the start year number (e.g. named 'StartYear'). Then, in the document itself, all you need to replicate that is a DOCPROPERTY field, coded as:
    {DOCPROPERTY StartYear}
    You can have as many of these as you like throughout the document.

    For the end year, you have two options:
    1. use a formula field coded as {={DOCPROPERTY StartYear}+1}; or
    2. use another custom document property to hold the end year number and corresponding DOCPROPERTY fields.
    The first option is slightly easier to set up & maintain, whilst the second option gives greater flexibility for the # years between the start and end.

    No VBA required. All that is needed to update the document content after changing the custom document property values is to use Ctrl-A, F9. It can also be made to update when printing by ensuring Word's 'update fields before printing' option is checked.

    Of course, the updating of the custom document property/properties and document content could also be automated. This would still be more flexible than trying to maintain everything directly in the document via a macro.

  9. #9
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,531

    Re: Automatically update year interval cycles from year to year

    See attached.

    The document contains two custom properties, named StartYr and EndYr, respectively. Assuming you're using Word 2010, you can access them via File|Info>Properties>Advanced Properties>Custom. Word 2007 & 2013 are similar. In Word 2003, you can access them via File|Properties>Custom.

    Back in the document, all the years are now created via Fields. You can see where they all are if you go to File|Options|Advanced>Show Document Content>Field Shading and choose the 'Always' option. All except one of these is a DOCPROPERTY field. You can toggle the field code display on/off (not the shading) via Alt-F9. Those DOCPROPERTY fields each reference the corresponding StartYr and EndYr properties. If you were to change one of those properties (via File|Info>Properties>Advanced Properties>Custom), then use Ctrl-A, F9, in the document, the corresponding DOCPROPERTY fields would all update. The first field in the document (the one in a bold font), however, is a MACROBUTTON field. If you double-click on that, you'll be asked for a start year. Input that and both the custom properties and their DOCPROPERTY fields will all update. There is some fairly simple macro code behind that process, which you can see via Alt-F11.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Newbie to Macros
    By trumptight in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-20-2014, 10:09 AM
  2. Newbie to Macros- Excel spreadsheet with insert images autofit help
    By sidfire in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2011, 03:20 AM
  3. Newbie needs help with String Manipulation & Macros
    By rhyswynne in forum Excel General
    Replies: 1
    Last Post: 10-18-2006, 02:35 PM
  4. [SOLVED] Newbie on Macros
    By Lei.Arcilla@gmail.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2006, 06:45 AM
  5. Newbie Help - Macros
    By NEO_72 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-12-2005, 12:21 PM

Tags for this Thread

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