Here's what I think you're saying, jmaxcy:



You're referring to three sources: an Excel workbook (which I'll name E.xlsx), a "current" Word document (C.docx) and a series of quarterly Word documents (which I'll named 2013Q1.docx, 2013Q2.docx and so on). From time to time someone goes into E.xlsx, goes to a worksheet there (let's say SheetD) and types a date into one of its cells (D5). At that point you want C.docx to reflect the information from whichever of the quarterly Word documents corresponds to that date, so if the user entered 2012-09-15, C.docx is updated so that certain text from 2012Q3.docx is copied manually into C.docx.

But you'd like to know whether it's possible for this to happen automatically. Now, certainly part of it can be automated. For example, you can write a VBA or VBS program that looks at the date in [E.xlsx]SheetD!D5, translates that date to the name of a new quarterly Word report (say "2013Q2.docx"), opens that report and C.docx, copies certain text from 2013Q2.docx into C.docx and saves the changed result. After that, anyone who opens C.docx will see the updated information.

First, is that what you're asking?

And second, that would be an improvement on what you're doing now, especially if you have to do it fairly often. But it does mean that someone would have to remember to run the program. Is there a way for this to happen even more automatically?

Maybe. I can think of two possibilities:

1) Write an Excel VBA program in E.xlsx (only now it's going to be E.xlsm, because it'll contain a program) that runs automatically when anyone changes the value of [E.xlsm]SheetD!D5. That would be a Worksheet_Change subroutine; it would have to look at the date, then open and change and close the two Word documents. That can be done.

2) I think it's possible to write the program in C.docx, so that whenever it's opened it checks the contents of [E.xlsx]SheetD!D5 and updates itself if necessary. I wouldn't do it this way for several reasons:

a) It has to check the contents of E.xlsx every time it's opened, whether E.xlsx has been updated or not. If you have the program reside in E.xlsm, then it can be made to run only when the date is changed.

b) Back in the Bad Old Days when Microsoft first invented the ability of Office documents to run programs automatically upon certain events—such as opening the document—lots of clever rascals wrote viruses that were transferred by such methods, doing lots of damage to trusting workplaces. MS Office is smarter about that now: When it sees a VBA program in a document it's opening, it usually stops and asks the user whether it should allow the program to run; and because of the old virus problem, informed users invariably answer "no" (often with various adverbs for emphasis). It's possible to get around that by using digital certificates to sign those programs, and then informing Word that it may run properly signed programs without worrying the user, but that's a lot of work.

I said "several" reasons and I think I had a third one in mind, too, but just now I can't remember what it might have been. But anyway, if you want it to run automatically I'd do it in Excel, not Word.

Bob,

Yes, that is exactly what I am looking to do. And I agree with you that it would be easier to include the macro in the excel file vs. the word as the function will happen immediately.

However, there are a few concerns for functionality - Do you have to have both the E.xlsm and C.docx file open for it to work? If I change the date into the E.xlsm without the C.docx document being open will it update this information when I do open the C.docx?

Finally, the bread and butter - what code is required to make it work?

Thanks for all the help, you are certainly a beast at Microsoft Office! Keep up the good work!

Best Regards,
Jerald

Depends what you mean. C.docx doesn't have to be open when you make the change in E.xlsm; the program can easily open C.docx, make the change and close it again. But that means C.docx has to be available to be opened, ie not in use by anyone else. Someone else can have it open with the read-only option, but if they're editing it then E.xlsm will be unable to make the update.

As for what code would be required, the part that happens in Excel is easy enough for any of us—maybe for you, too. It's the part that happens in Word that we'll have to figure out.

By "happens in Word" I don't mean that you mistook what I said before; the program will run in Excel, alright. But in order to manipulate a Word document, your Excel program will have to open a Word application object, and then manipulate Word properties and methods to get the desired result. And since no one on the Excel forum (I presume) is a Word expert—and believe me, the Word object is a very different beast from the Excel object!—we'll either need help from the Word jocks over on the other forum, or we'll have to figure it out ourselves. Me, I vote for the latter; I'd much rather learn how to do VBA/Word.

Learn more about it, I mean, for I've already done some very rudimentary things in it. But here very roughly is how I think the program would look:This has a lot of holes to fill in. But something like this should work; it'll just take looking things up, experimenting and debugging until it works reliably.