Update Word Document with Data entered in Excel

jskfan
jskfan used Ask the Experts™
on
Update Word Document with Data entered in Excel
sp
I have Excel sheet displayed on the screenshot above.
I also have word documents with the name that matches exactly the name of each Comp-Name.

BATH.docx
MONR.docx
STAR.docx
JUSGB.docx




Each word document has a table with the same column names as the Excel sheet

I would like to have the respective word document table updates automatically  its data to match the new entered data in Excel .
for instance If I have a new purchase, I can manually add it to a new Excel row, and the program should add that new row automatically to the corresponding word document.

Example:
I have new purchase for a company MONR
On Excel , I will add new entry:
MONR      TX      3/3/2018      3/8/2018

on a corresponding word document MONR.doc, I should have the new entry added automatically to the table in word.

Any help will be very much appreciated.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
William FulksSystems Analyst & Webmaster

Commented:
Here's a good how-to on linking Excel data into a Word document, but if you're looking to generate real-time reports you might out to consider making an Access database and then creating reports for outputting this info.

https://www.k2e.com/tech-update/tips/158-linking-excel-data-into-word-documents

Author

Commented:
I was Exactly on that link before I posted the question.
However that link shows how to sync between  one Excel sheet and one  word document based on the Data you have already pasted in word.
 the question is different...each row or set of rows based on the comp-name should update a specific word document
William FulksSystems Analyst & Webmaster

Commented:
Which is why I said you need to be doing this in Access. Databases are made for linking tables where you have common keys, etc.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
There should be  a way in VBA Scripting to do it.

Author

Commented:
Ok let's make it simple.

If I change a Value  in a column in Excel  , then  word document corresponding to Comp-Name, will just change the value,
if I add new Entry in Excel, and the Comp-Name entered  already has a corresponding word document, the new entry will just be added on the table in word document

if I add new Entry in Excel, and the Comp-Name entered does not exist, then a new word document will be created with same name as Comp-Name and the row data filled in Excel will be also filled in the word document.

Hope it is clear
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...

Place the following codes on Sheet Module. To do that, right click the Sheet Tab --> View Code --> Paste the codes given below into the opened code window.

As per the following code, once you enter the Delivery_Date in column D and if all other details are filled in that row, the code will open the Word Document with the name similar to entered in column A, add another row in the existing word table, save and close the word document.

Note: The codes assume that all the word documents and macro workbook are saved in the same folder.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim r As Long
Dim rng As Range
If Target.Column = 4 And Target.Row > 1 Then
    If IsDate(Target) Then
        r = Target.Row
        Set rng = Range("A" & r & ":C" & r)
        If Application.CountA(rng) = 3 Then
            UpdateWordDocument Range("A" & r).Value, Range("B" & r).Value, Range("C" & r).Value, Range("D" & r).Value
        End If
    End If
End If
End Sub


Sub UpdateWordDocument(DocName As String, Location As String, Purchase_Date As Date, Delivery_Date As Date)
Dim wdApp As Word.Application
Dim Doc As Word.Document
Dim tbl As Word.Table
Dim rng As Word.Range
Dim DocPath As String, strDocName As String

Application.ScreenUpdating = False

DocPath = ThisWorkbook.Path & "\"
strDocName = DocName & ".docx"

Set wdApp = New Word.Application
wdApp.Visible = False

On Error Resume Next
Set Doc = wdApp.Documents.Open(DocPath & strDocName)
On Error GoTo 0

If Doc Is Nothing Then
    MsgBox "Document " & DocPath & strDocName & " was not found.", vbExclamation
    GoTo Skip
End If
Set tbl = Doc.Tables(1)
tbl.Rows.Last.Range.Rows.Add

With tbl.Rows.Last
    .Cells(1).Range.Text = DocName
    .Cells(2).Range.Text = Location
    .Cells(3).Range.Text = Purchase_Date
    .Cells(4).Range.Text = Delivery_Date
End With

Doc.Close True
Skip:
wdApp.Quit
Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

Open in new window

UpdateTableInWordDocument.xlsm

Author

Commented:
when I enter a value in Delivery_date column, then press enter the window below will open up with line pointed with the red arrow highlighted

e
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Sorry! I forgot to tell you that you need to add reference to the Microsoft Word 16.0 Object Library.
I am using Office 365 so my version is 16.0 so it may be different for you if you are not using 2016 or Office 365.
The version numbers are as below....

Excel 2007 --> 12.0
Excel 2010 --> 14.0
Excel 2013 --> 15.0
Excel 2016 --> 16.0
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
If you find any Missing reference in References Window, uncheck it and add the same reference as per your excel version.

Author

Commented:
Sorry! I forgot to tell you that you need to add reference to the Microsoft Word 16.0 Object Library.
how do you do that ?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay, try the attached and you don't need to do anything.
UpdateTableInWordDocument-v2.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Resolved.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial