Excel and Word Document Automatic Update/Refresh

Look-- one of my things with this web site is I wish, Wish WISH they would ban responses that begin with "Why are you doing it that way?"  Let's just assume I have my reasons.  I've hired the hourly programmers who asked those same question and wanted to redesign my project from top to bottom... and they all bailed on me at points in my project that I've been able to totally overcome on my own.  

My process is clearly not conventional—I will agree with you there, but it’s advanced me a lot farther that any professionals have gotten me.

Can you help with the specifics of my question?

I don't understand why you are using 2 XL files...  

OK mate - you can wish all you like, I was asking to better understand your requirements - you've got your reasons and thats fine.

Your files don't allow for macros.  Do I assume you don't wish to use macros?

I didn't mean to sound like a prick... I really do appreciate you guidance.  I've been dealing with an ugly Access problem for 3 days straight and I'm at my wit's end!

I would assume they allow for macros-- they are 2 virtually blank Excel 2013 spreadsheets and a word document.  

Essentially, I just want them all to be filled in/updated/refreshed at the same time (or in sequence) with only the word document remaining open.

Any clue how to make that happen?

No worries - guessing you are an Aussie too...  BTW Pointing out obvious stuff like that helps a lot of people

Looking at the spreadsheets it seems they are not dynamic... I know that is not part of the question but I don't really see how they hang together so was expecting to see some VBA.

DumpIt is essentially where you put your data, and I guess the Update is a subset of the data which you want to make available to MS Word.

When you open the word document it is linked to Update so at the very least you should see the what is in that file.  

I'm guessing the problem is that UpDate is not manually opened and perhaps not up to date...  Am I Close?

If you did the merge from DumpIt you would have the right data - but you have reasons for not doing that.

There was nothing that I noticed in the UpDate sheet that would respond to the addition of a new line in DumpIt...  I might have missed it.

Question - if you open the UpDate sheet, will it automatically update.  Then if you save & close it - does everything work?  If so you can use VBA to achieve this.  

However, VBA has its own problems - it has to be enabled.

Oh... to answer your question... it is possible to open documents and spreadsheets in the background without the user of the PC being aware... review this

Re the Word part,

You seem to be relying the Preview mode of Mail Merge. This is intended to give the designer of the Mail Merge Main document an idea of what the output will look when the merge is executed. For this reason there is no built-in facility for dynamically updating the display,

Also the workbook datasource is not editable while the Word document is open, so you would need some non-trivial VBA to close the document, update the spreadsheet, and reopen the document.

Actually, there may be an ‘update’ to my inquiry…

But first… am I an Aussie?  Negative!  I’m from the States… Denver, CO to be exact (why do you think our communications are exactly 12 hrs. apart)?  How could you think I’m not an American—did you not pick up my pretentious, dickly demeanor (which I’m totally proud of)?

J/K—ing! To be honest…  I’d love to get down to Australia for a vacation.  

Okay, stop F—ing around Pete; let’s get down to business:

Look, all I want to know is…

If I have 3 MS Excel Spreadsheets that are refreshed with data imported from Access:
Excel Dump1
Excel Dump2
Excel Dump3

And I have another 3 MS Excel Spreadsheets that are bound to these spreadsheet by Formulas (Dump Spreadsheets):

Excel Update1
Excel Update2
Excel Update3

And finally, I have another set of 3 MS Word documents that are bound to each of these Spreadsheets (Update Spreadsheets) through Word’s Mail Merge Application:

Document1
Document2
Document3

How do I get all three levels of these applications to work together--without having to manually open each one?  I mean, I’ve seen Macros perform feet’s greater than this before… I’m not asking to have sex on the moon!?

Here’s the update to my request.  How do I get both my Excel Spreadsheets and my Word applications to automatically ‘refresh’ themselves without ANY OF THEM having to remain open?  And also, how do I do this without getting ‘Do you want to update document’ pop ups?

If you can solve this…. I will root for Australia in the next Olympics.

The Excel workbook to workbook link should work OK, provided your Trust Centre settings are enabled.

For the Excel to Word bit, don't use a mailmerge document.  We would need to use VBA with bookmarks or form fields or content controls. I'll have a go at writing a macro for the last one.

Here is some macro code. It goes in the first sheet of the dump workbook. It runs when a change is made to data on the sheet. It opens the Word application, if necessary, and opens the word document. It opens the udate workbook and extracts the data from there and puts it in the content controls of the Word document.

This is a demo only, so doesn't close or save anything.

It uses early binding, so you would need to set a reference to the Microsoft Word Object Library.
 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim strDocName As String
    Dim strBookName As String
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim wdCC As Word.ContentControl
   
    strBookName = "I:\Allwork\ee\28326634\Excel-UDate1.xlsx"
    strDocName = "I:\Allwork\ee\28326634\Word-Test-Document2.docx"
    
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Set wdDoc = wdApp.Documents.Open(strDocName)
    wdApp.Visible = True
    Set xlWbk = Workbooks.Open(strBookName)
    Set xlWks = xlWbk.Sheets(1)
    Set wdCC = wdDoc.SelectContentControlsByTitle("Name")(1)
    wdCC.Range.Text = xlWks.Cells(2, 1).Value
    Set wdCC = wdDoc.SelectContentControlsByTitle("Hair_Color")(1)
    wdCC.Range.Text = xlWks.Cells(2, 3).Value
    Set wdCC = wdDoc.SelectContentControlsByTitle("pounds")(1)
    wdCC.Range.Text = xlWks.Cells(2, 2).Value

End Sub

Select allOpen in new window

Word-Test-Document2.docx

Nah... I don't pay attention to time stamps... we were up at the same time and I know many people who have their PCs in a different time zone.  It was actually your use of the word 'prick'.  It is quite common in Aus but I've never noticed it used in the US; perhaps I've been to the wrong places.

did you not pick up my pretentious, dickly demeanor (which I’m totally proud of)?

 Um... no. Guess you'll have to try harder ;-)