Importing text values from Excel to Word in a desired

[aftbrah]

Hi everyone,

So I am looking to create a standardized word document with the correct values properly entered. How would I be able to pull values from different cells in an Excel sheet and auto populate it into Microsoft Word? I know there is a way. Any help would be kindly appreciated!

Thank you

 

[Joe4]

Sounds like you are looking to do a Mail Merge, where the document is in Word, and the data is in Excel.
In you do a Google Search, you will find tons of articles, tutorials, and examples on how to do it.
Here is one to get you started: Mail merge using an Excel spreadsheet

 

[aftbrah]

Thanks a lot!

 

[Joe4]

You are welcome.

 

[aftbrah]

Hi again,

So I did some searching and found someone from a different forum trying to achieve the exact thing. (I won't mention the name of the forum because I am not trying to promote it!)
Basically, they managed to get this far with the code, however, it doesn't work on my computer at all.

Sub CreateSummary()

Dim oWord As Object
Dim WordWasNotRunning As Boolean
Dim oDoc As Object
Dim WS As Worksheet
Dim FN As Variant
Dim Phrase$
Dim Choice As Integer

Set WS = ActiveSheet

'See if Word is already running
On Error Resume Next
Set oWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    On Error GoTo Err_Handler
    Set oWord = CreateObject("Word.Application")
    WordWasNotRunning = True
    oWord.Visible = True 'Make the application visible to the user (if wanted)
End If

On Error GoTo Err_Handler

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Add(ThisWorkbook.Path & "\assessment template.dotx")

With oDoc.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[Property name]"
    Phrase$ = WS.Range("D" & ActiveCell.Row)
    .Replacement.Text = Phrase$
    .Wrap = 0 'wdfindstop
    .Execute Replace:=1 'Word.WdReplace.wdReplaceone
End With
With oDoc.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[Comment1]"
    Phrase$ = WS.Range("E" & ActiveCell.Row)
    .Replacement.Text = Phrase$
    .Wrap = 0 'wdfindstop
    .Execute Replace:=1 'Word.WdReplace.wdReplaceone
End With
With oDoc.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "[Comment2]"
    Phrase$ = WS.Range("F" & ActiveCell.Row)
    .Replacement.Text = Phrase$
    .Wrap = 0 'wdfindstop
    .Execute Replace:=1 'Word.WdReplace.wdReplaceone
End With

'Parse suggested filename
Temp = Split(WS.Range("D" & ActiveCell.Row), ",")
If Temp(0) <> "" Then
    FN = Temp(0)
    oWord.FileDialog(2).InitialFileName = ThisWorkbook.Path & "\" & FN
    Choice = oWord.FileDialog(2).Show
    If Choice <> 0 Then
        oWord.FileDialog(2).Execute
        'Store the path and filename
        WS.Range("G" & ActiveCell.Row) = oDoc.FullName
    End If
End If

oDoc.Close False

If WordWasNotRunning Then
    oWord.Quit
End If

'Make sure you release object references.

Set oWord = Nothing
Set oDoc = Nothing
Set myDialog = Nothing

'quit
Exit Sub

Err_Handler:
    MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " _
            & Err.Number
    If WordWasNotRunning Then
        oWord.Quit
    End If
End Sub

Highly appreciate any help! Thanks!

 

[EdNerd]

I did such a thing some years back. In my case, I was able to create named ranges in the Word doc and matching names in the Excel sheet. With the code in XL, I iterated all the names in the sheet, found the matching name in the doc, and transferred the value from one range to the other.

 

[aftbrah]

I did such a thing some years back. In my case, I was able to create named ranges in the Word doc and matching names in the Excel sheet. With the code in XL, I iterated all the names in the sheet, found the matching name in the doc, and transferred the value from one range to the other.

Wonder if you'd have anything you could share? Thanks.

 

[EdNerd]

I'll have to see if I have that specific project in my "file pile" when I get back to work tomorrow. In the meantime, here's how I have gotten into Word from Excel in another project. Please note that this uses late binding. I usually use early binding, but this was written on a computer with different versions of Word and Excel than the machine that would run this code.

Cheers!
Ed

Dim wkbM As Workbook  'Master Table workbook
Dim wkbR As Workbook  'Report workbook


Dim wksM As Worksheet 'Master Table worksheet
Dim wksR As Worksheet 'Report data worksheet


Dim objWord           'Word app object (late binding)
Dim objDoc            'Word doc object (late binding)
Dim docRng            'Word doc range object (set late binding)


    On Error Resume Next
    Err.Clear
    Set objWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
      Set objWord = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    objWord.Visible = True
    Set objDoc = objWord.Documents.Open _
      (Filename:="C:\Users\YourName\Full\File\Path\Goes\Here.doc") 'or docx
    
    With objDoc
      'And on we go
    End With


'At the end
    objDoc.SavevAs '... fill in variables   
    objDoc.Close savechanges:=wdDoNotSaveChanges
    objWord.Quit
    Set objDoc = Nothing
    Set objWord = Nothing

 

[aftbrah]

I'll have to see if I have that specific project in my "file pile" when I get back to work tomorrow. In the meantime, here's how I have gotten into Word from Excel in another project. Please note that this uses late binding. I usually use early binding, but this was written on a computer with different versions of Word and Excel than the machine that would run this code.

Cheers!
Ed

Dim wkbM As Workbook  'Master Table workbook
Dim wkbR As Workbook  'Report workbook


Dim wksM As Worksheet 'Master Table worksheet
Dim wksR As Worksheet 'Report data worksheet


Dim objWord           'Word app object (late binding)
Dim objDoc            'Word doc object (late binding)
Dim docRng            'Word doc range object (set late binding)


    On Error Resume Next
    Err.Clear
    Set objWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
      Set objWord = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
  
    objWord.Visible = True
    Set objDoc = objWord.Documents.Open _
      (Filename:="C:\Users\YourName\Full\File\Path\Goes\Here.doc") 'or docx
  
    With objDoc
      'And on we go
    End With


'At the end
    objDoc.SavevAs '... fill in variables 
    objDoc.Close savechanges:=wdDoNotSaveChanges
    objWord.Quit
    Set objDoc = Nothing
    Set objWord = Nothing

I think what I am asking for is a bit simpler than that. The company I work for has a word doc template for an inspection report. The user has to edit each report and fill in the data about a property. It would be useful to edit the data in a centralised worksheet, and then have this placed in the respective word documents for each property using a substitution approach. I don't think mail merge is feasible as the company templates vary and formatted with corporate branding etc. I'm more looking for something that will do a substitution of values in existing word docs based on the items in the square brackets only, and not something that creates new fresh word docs. Is it possible to create a VBA that, when you tell it the filepath of a word document, can substitute a string for another desired string contained in the excel sheet? I have attached two images of what the original word doc could look like and the second image of the desired result. This isn't the template, but I just want to see if this is possible first.

I hope that was a little more clear.

 

[EdNerd]

Yes - much simpler. That would use Word's native VBA Find/Replace code. I'm sure I've got something with that at work.

In the meantime, we need to know teo things:
-- "Template" has a special meaning in Word. Are you opening a file with a dot/dotx/dotm file extension? Or doc/docx/docm extension?
-- How are you going to identify the data in Excel to push into Word? Do you have a separate workbook for each property? Or a separate sheet? Or column??