I'm not sure this is possible. Anyways, here is what I am trying to accomplish.



I have a word document that is populated using reference links from an excel file. Part of this document is a breakdown of the economic conditions at the time, which is dependent on what date is entered into my excel file. The economic conditions write ups are produced quarterly and stored in a separate word file. We currently manually copy and paste the text from the apropriate file into our document.

So for instance, if the date entered into the excel file is January 31, 2013, we would currently manually enter the text from the Q1 - 2013 word document we have saved in a separate file. If the date was November 30, 2012, we would copy and paste the text from the Q4 - 2012 word document and so on.

My question ultimately is, is there anyway to populate the text from the Q1-2013 word document into our linked word document based on the input in the excel file?

What I would like to happen is the user can put in a date, and based on the date, the text of the quarterly economic conditions word document be updated in our linked word document?

The text in the quarterly word docs is far too long to attempt to re-type into a concatenation formula.

I hope this makes sense, however I'm not sure it's possible.

Any help is greatly appreciated!

Thanks!

JM

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.

Cross-posted at: https://www.msofficeforums.com/word/...o-another.html
Please don't post the same question in multiple forums.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Macropod,

Regarding cross-posting...my question deals with both Word and Excel... so which should I post it to? If you look at the post by Bob Bridges, it will give you some insight on what I mean. Plus, what's the harm? Maybe someone with Word experience will have a better idea as to the solution than someone with Excel experience? I just don't get it, don't you want these threads to have activity? I'm not spamming or anything, it's a legitimate request.

I was going to ask the same thing, macropod. I've heard people objecting to cross-posting (and others insist that when something is cross-posted it's polite to announce the fact), but I've never understood the objection. What's the basis for this very common belief? Maybe I'm missing some practical reason for it. In fact, I think I must be.

Thank you, BobBridges, I'm in the same boat. I'm not on these forums too much but was awfully confused by macropod's reply when I read it. So you are limited to one forum for any post? Did I offend someone by posting my question that has to do with both excel and word in both forums? It seems as though we're both missing something here. I'm not interested in receiving only half of the solution so if that's the case I'm not sure this forum will be very helpful to me at all.

Please read this about cross posting.
Also remember macropod is the forum admin . You will find the same reaction on any other forum.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Never mind, macropod. Pecoflyer posted this link in another thread that explains it very well.

Jmaxcy, the point seems to be not that you must never cross-post, but that when you do you're asking more than one group of people to work on the same problem. Since all of us who answer questions are volunteering our time because we want to help, the result is that you take up the time of more people than may be necessary. So the writer of the above page says:

1) Post your question at one place first, and give them a few days to respond. If you don't get any reply then go ahead and ask elsewhere, but:
2) Always announce when you cross-post, so people know what they're getting into, and explain why.
3) When you get an answer, mark your question "solved" wherever you asked it (not just in the forum where you got the answer), so that others won't waste time on it.

This makes sense to me. In your case, you were asking for help from a different set of experts about a different aspect of the problem, so you might not think it was actually cross-posting but only looked like it. But given how experienced forumites feel about cross-posting, it's probably better, and certainly more polite, to write the question differently. In the Excel forum you were asking how to set up the problem; from the Word forum what you really want is more information on how to identify and copy the parts of the text that need to be transferred from the Q doc to the C doc.

May I also draw your attention to the fact that the regular XL forum members ( which are usually the most experienced) hate spending their free time for nothing. This community being rather small they communicate regularly with each other. Heavy cross-posters are thus well known and after the admin's patience has grown thin, posters can be banned (on different forums), making it difficult for them to find (free) help.
Finally it all boils down to that : you get free help, so please respect the people giving it

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

By the way, jmaxcy: No one said you may not ask for help from both Excel and Word experts, on this problem, but even if it were true you wouldn't be stuck. I don't know the Word object nearly as well as I do Excel, but I have used it from time to time in automating repeated tasks. You and I may not be sure how to do this; but we can figure it out, even if the Word guys turn out to be no help to you.

In this case, it would have been appropriate to post in any one of the Word, Excel or Office forums. I'd suggest the latter due to the cross-application issues.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I've glanced at the posts in the Office forum, macropod; they talk about different kinds of issues there (and far fewer ones, as well). I think in the end he picked the right one; he can ask specific questions in the Word forum, but here in Excel is where he's probably going to do the bulk of the work.

Bob,

You're entitled to your opinion, of course, but I'm interested in telling the OP where the most appropriate place is to post questions about cross-application issues.

Having "the bulk of the work" done in Excel doesn't for one moment suggest the Excel forum is the most appropriate one. If the issue of where the data are maintained was to be used as the determining factor, one could argue that almost all of the Word mailmerge posts should be moved here too. As for:
without placing too fine a point on it, the cross-posting issue arose due to the OP not picking "the right one", but picking more than one...

As for your take on the cross-posting issue in general, whilst the etiquette expressed in the links applies to posting across multiple boards, no board that I know of allows cross-posting to forums within the same board.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]