none
how to update word tables based on a table in excel? RRS feed

  • Question

  • Hello,

    I have a Word file which includes some paragraphs and a few tables (3-columns, unknown rows). I need to revise those tables based on a table in excel. for example all tables have 3 columns of car's name, car's year, and car's price. but I have a reference table in excel that says some cars have different price with the same car's year. In other words, for example the reference table in excel says, Benz is $100000 and Hyundai is $15000, but their price in the Word file is different. I want to revise the Word tables to see $100000 and $15000 for Benz and Hyundai.

    Important: Note that in the Word tables we don't see a pure car name, for example we see Benz L9000C or Hyundai Accent 2014. but in the reference table in excel we see pure car names (with no suffix and prefix).

    Thank you.
    Wednesday, August 30, 2017 12:39 AM

All replies

  • Hello,

    You may create a macro in Word to automate Excel to retrieve the data. You could also use ADO connection. Please visit

    Using ADO to Query an Excel Spreadsheet

    ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

    After getting the Excel data, you could loop through the cells of Word table to check for matching and write new value.

    >>Important: Note that in the Word tables we don't see a pure car name, for example we see Benz L9000C or Hyundai Accent 2014. but in the reference table in excel we see pure car names (with no suffix and prefix).

    You could use Find function to search the Word table to get the row or row number and then get the price using row.Cells(3).Range.Text to get the current price in Word table. Here is the example:

    Sub Demo()
    Dim name As String
    Dim price As String
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=D:\Book1.xlsx;" & _
            "Extended Properties=""Excel 12.0;HDR=Yes;"";"
    
    objRecordset.Open "Select * FROM [Sheet1$]", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    Do Until objRecordset.EOF
    name = objRecordset.Fields.Item("name")
    price = objRecordset.Fields.Item("price")
    Dim tb As Table
    Set tb = ActiveDocument.Tables(1)
    Dim rng As Range
    Set rng = tb.Range
    rng.Find.Execute FindText:=name
    If rng.Find.found = True Then
    If rng.Information(wdWithInTable) Then
    rng.Select
    i = Selection.Cells(1).RowIndex
    tb.Rows(i).Cells(3).Range.Text = price
    End If
    End If
    objRecordset.MoveNext
    Loop
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, August 31, 2017 6:58 AM
    Moderator