Importing text values from Excel to Word in a desired

aftbrah

Board Regular
Joined
Sep 25, 2011
Messages
51
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

aftbrah

Board Regular
Joined
Sep 25, 2011
Messages
51

ADVERTISEMENT

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.

VBA Code:
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

Active Member
Joined
May 19, 2011
Messages
398
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

Board Regular
Joined
Sep 25, 2011
Messages
51

ADVERTISEMENT

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

Active Member
Joined
May 19, 2011
Messages
398
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
VBA Code:
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

Board Regular
Joined
Sep 25, 2011
Messages
51
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
VBA Code:
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.
 

Attachments

  • Original.png
    Original.png
    218.5 KB · Views: 6
  • desired.png
    desired.png
    107.1 KB · Views: 6

EdNerd

Active Member
Joined
May 19, 2011
Messages
398
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,130
Members
414,429
Latest member
Bilaal xaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top