Quote:
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?
|
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:
Code:
Private Sub Worksheet_Change(ByVal Target as Range)
' Remember we're interested only in a change to one particular cell:
If Target.Address <> "$D$5" Then Exit Sub
' Translate the new date to a Word document name.
vy = DatePart("yyyy", Target.Value)
vq = DatePart("q", Target.Value)
fnQ = "C:\path\" & vy & "Q" & vq & ".docx" '...where "fn" means "file name"
' Initialize a few things:
Set oWrd = CreateObject("Word.Application") 'the Word application
oWrd.Visible = True
Set fso = CreateObject("Scripting.FileSystemObject") 'to look up files in Windows
fnC = "C:\path\C.docx" 'the filename of C.docx
' Make sure that both C.docx and the quarterly doc are there.
If Not fso.FileExists(fnC) Then Abend fnC & " not found!"
If Not fso.FileExists(fnQ) then Abend fnQ & " (quarterly info) not found!"
' Now, we don't yet know what the statements will look like that will open our two Word documents.
' It may be something like this:
Set wdoC = oWrd.OpenDocument FileName:=fnC, OpenForEdit:=True
Set wdoQ = oWrd.OpenDocument FileName:=fnQ, OpenReadOnly:=True
' We'll also have to figure out how to identify the text you want to pull from the
' quarterly document, and locate what you want to replace in C.docx. Again,
' let's pretend we already know how:
txt = wdoQ.ParagraphRange(14, 16).TextOnly
wdoC.ParagraphRange(13, 15).Delete
wdoC.ParagraphRange(13).Insert(txt)
' More pretending that we know how to save and close the document — but keep
' in mind that we're going to find out, and test it until it works.
wdoQ.Close
wdoC.Close Save:=True
' Make sure the user knows it's done.
MsgBox "Successfully updated " & fnC & " from " & fnQ & "."
End Sub
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.