Solved

Excel and Word Document Automatic Update/Refresh

Posted on 2013-12-27
11
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 15

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 15

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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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 15

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
 
LVL 15

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 15

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 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