Macro to link word document to excel spreadsheet

[pheens]

I'm posting in VBA because I assume I'll need some macro to accomplish this. I've got a word document with a small table I'd like to be able to enter a code into, and upon entry, have spec's pop up. Of course this info is already in an excel chart, line by line with the code, a few cells describing the material, then finally a specs cell. What I'd like is to be able to enter a 4 digit code into the word document table, and have accompanying specs from the excel document populate the next cell in the chart in word.

Is this possible? If needed I can supply an example excel sheet and word document, but I'd have to create these. Any help is appreciated.

 

[shrivallabha]

It will be good if you post an example as that will explain above requirements more clearly. The example should be accurate enough of your real life requirement in case if the real information / data is sensitive and you can't post it.

 

[pheens]

See the attached .zip file. I've created a excel file with generic materials/codes/specs. I've also created a word doc that with a blank table and a "populated" table, with the info from the spreadsheet, of course I just typed the info in there but I'm looking for it to automatically populate specs upone entry of a code.

THANKS!

 

[shrivallabha]

From the description, you will need VBA in the word document. And that is difficult for me as I have no (or very little) experience with word VBA. Maybe you can try some word forums to check possibilities or we will have to wait for someone who uses word VBA regularly.

Word doesn't seem to have an event suitable for checking the changes made to the range. However, there's a VBA control which can be added (manually) to handle this. I am uploading a sample word macro document.

Try changing content in column 2 in the attached document. It should update text in the next column. If you are OK with manual setting up of the complete table then I can work out the rest of the code for handling Excel data import. Excel part would be easier

 

[pheens]

This seems to work fine actually. If this can link to the excel spreadsheet I think it'd be suitable.

 

[shrivallabha]

OK. Here's the code. Please keep in mind following items.

• Read all comments in the code and update information where necessary at your end like variable "strWbkName" value.
• Do not forget to keep source Excel workbook in the same folder as this word document is.
• You will have to handle setup for adding those fields manually.
• In case it doesn't find suitable data it will do nothing to next cell's content.

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim tbl As Word.Table
Dim xlApp As Object 'Excel.Application
Dim xlWbk As Object 'Excel.Workbook
Dim xlSht As Object 'Excel.Worksheet
Dim xlRng As Object 'Excel.Range
Dim strWbkName As String

'\\ Check if relevant info has been filled in Content Control
If Len(ContentControl.Range.Text) <> 4 Then Exit Sub
'\\ Set table reference
Set tbl = ThisDocument.Tables(1)
'\\ Define the source workbook name here
strWbkName = "Example Excel specs sheet.xlsx"

On Error Resume Next
'\\ Check if instance of Excel is running
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = False
End If

Set xlWbk = xlApp.Workbooks.Open(ThisDocument.Path & _
Application.PathSeparator & strWbkName)
If xlWbk Is Nothing Then
  If xlApp.Visible = False Then xlApp.Quit
  MsgBox "Source workbook not found!" & vbCrLf & _
  "Please place it in the same directory as this word document" & vbCrLf & _
  "Name should be : " & strWbkName, vbInformation
  Exit Sub
End If
On Error GoTo 0

'\\ Now check through 2nd column and then change text accordingly
For i = 1 To tbl.Rows.Count
  If InStr(tbl.Rows(i).Cells(2).Range.Text, ContentControl.Range.Text) > 0 Then
  '\\ Loop through all sheets to locate info
  For Each xlSht In xlWbk.Worksheets
  Set xlRng = xlSht.Cells.Find(What:=ContentControl.Range.Text)
  If Not xlRng Is Nothing Then
  '\\ Currently your data is 2 columns offset to the number
  tbl.Rows(i).Cells(3).Range.Text = xlRng.Offset(0, 2).Value
  Exit For '\\ Found and updated info so exit
  End If
  Next xlSht
  End If
Next i

'\\ After processing reset
If xlApp.Visible = False Then
  xlApp.Quit
Else
  xlWbk.Close
End If
'\\ Release variables
Set xlApp = Nothing: Set xlWbk = Nothing: Set xlSht = Nothing: Set xlRng = Nothing

End Sub

I am also attaching the zip file containing the Word document containing code. Feel free to adopt and change to suit as it may not have everything you want.