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.

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.

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)

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

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.

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.