Solved

Excel and Word Document Automatic Update/Refresh

Posted on 2013-12-27
11
440 Views
Last Modified: 2014-08-09
I have 2 Excel Spreadsheets and 1 Word document:

MS Excel—‘Excel Dump1’
MS Excel—‘Excel Update1’
MS Word—‘Word Test Document’

‘Excel Update1’ is connected to ‘Excel Dump1’ through formulas.
‘Excel Update1’ is connected to ‘Word Test Document’ through Mail Merge.

Suppose that ‘Excel Dump1’ has a new record added to it and is closed…
Is there a way to automatically refresh both ‘Excel Update1’ and then ‘Word Test Document’ to include the new records added… but then to only open (or keep open) ‘Word Test Document’?

If anybody can speak in simple terms and walk me though this one, it would be a huge help.
My applications are attached.
Excel-Dump1.xlsx
Excel-Udate1.xlsx
Word-Test-Document.docx
0
Comment
Question by:mdstalla
  • 5
  • 3
  • 3
11 Comments
 
LVL 14

Accepted Solution

by:
DrTribos earned 500 total points
ID: 39743158
I don't understand why you are using 2 XL files...  Seems to me that you don't need the Update file - it seems to be complicating the issue.  Why not just use the Dump file with the word document?

If you want to prevent accidental editing of the XL file you can allways protect it with a password to make read only...

HTH
0
 

Author Comment

by:mdstalla
ID: 39743200
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?
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39743219
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?
0
 

Author Comment

by:mdstalla
ID: 39743228
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?
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39743231
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 14

Expert Comment

by:DrTribos
ID: 39743267
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39743442
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.
0
 

Author Comment

by:mdstalla
ID: 39744448
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39744520
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39744565
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

Open in new window

Word-Test-Document2.docx
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39747460
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 ;-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now