Update Word Document with Data entered in Excel

Update Word Document with Data entered in Excel
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.


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.

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

William FulksSystems Analyst & WebmasterCommented:
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.

jskfanAuthor 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 & WebmasterCommented:
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.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

jskfanAuthor Commented:
There should be  a way in VBA Scripting to do it.
jskfanAuthor 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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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)

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
Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jskfanAuthor 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

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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 ExpertCommented:
If you find any Missing reference in References Window, uncheck it and add the same reference as per your excel version.
jskfanAuthor 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 ExpertCommented:
Okay, try the attached and you don't need to do anything.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.