Import Data from Excel into Word Automatically Using VBA (2 Ways)

Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 2 effective ways how to import data from Excel into word automatically using the VBA macro.

2 Ways to Import Data from Excel into Word Automatically Using VBA

Following this section, you will learn how to import data from an Excel file into a Word file automatically after running VBA code and by simply clicking a button.

Above is the example dataset that our Excel workbook has. We will import this data into word automatically using VBA.

1. Embed VBA to Import Data from Excel into Word Automatically

This section will let you know how you can import data from an Excel file into a Word file automatically by executing VBA code. To successfully execute this code, your system must have a word file, where the data from the Excel file will be imported.

To understand better, consider the following image.

Our system already has a word file named “Excel to Word” in the “ExcelDemy” folder inside Drive C. We will import the data from our Excel file into this “Excel to Word” file with VBA code.

The steps to perform that are given below.

Steps:

In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

Next, in the pop-up code window, click Insert -> Module from the menu bar.

Then, copy the following code and paste it into the code window.

Sub ActivateWordTransferData()
Worksheets("Sheet1").Range("B2:D14").Copy
Dim DocApp As Object
Dim DocFile As Object
Dim DocName As String
On Error Resume Next
Set DocApp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Err.Clear
Set DocApp = CreateObject("Word.Application")
End If
DocApp.Visible = True
DocName = "C:\ExcelDemy\Excel to Word.docx"
If Dir(DocName) = "" Then
MsgBox "File " & DocName & vbCrLf & "not found " & vbCrLf & "C:\ExcelDemy\.", vbExclamation, "Document doesn't exist."
Exit Sub
End If
DocApp.Activate
Set DocFile = DocApp.Documents(DocName)
If DocFile Is Nothing Then Set DocFile = DocApp.Documents.Open(DocName)
DocFile.Activate
DocFile.Range.Paste
DocFile.Save
DocApp.Quit
Set DocFile = Nothing
Set DocApp = Nothing
Application.CutCopyMode = False
End Sub

Your code is now ready to run.

Now, consider the part from the code shown in the image below.

In line 13 of the code, we wrote DocName = “C:\ExcelDemy\Excel to Word.docx”.

Here, C:\ExcelDemy\Excel to Word.docx is the word file path that our computer system has – in Drive C, “ExcelDemy” folder, word file name “Excel to Word.docx”. In this line of code, you must provide the path address that your word file holds.

Similarly, in line 15 of the code, make the change accordingly.

You must have an existing word file where you will import the data from the Excel workbook, and you must write the file directory address correctly in the code. Otherwise, you will get a “File not found” and/or “Document doesn’t exist” message.

Now that you have written the code without any error, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After successful code execution, go back to the word file to check whether the data from the Excel file was copied there or not.

As you can see from the above image, the exact data from our Excel workbook was copied into the Word file automatically.

VBA Code Explanation

Worksheets("Sheet1").Range("B2:D14").Copy

Define the worksheet name (“Sheet1” is the sheet name in our dataset) and the range (“B2:D14” is the range that stores the data) that will be imported.

Dim DocApp As Object
Dim DocFile As Object

Declare object variables for word application and word file.

Dim DocName As String

Declare a string variable for the file name and path directory.

On Error Resume Next

If an error occurs, then go to the next statement.

Set DocApp = GetObject(, "Word.Application")

Pass the word application in the GeObject function as a parameter and store that in the defined application variable. GetObject requires two parameters, the first one is optional and the second one is mandatory.

If Err.Number = 429 Then
Err.Clear

If the component cannot create the object (error number 429), then the object will be reset.

Set DocApp = CreateObject("Word.Application")
End If

Create a new instance of word application. Then exit the condition.

DocApp.Visible = True

To make the application visible.

DocName = "C:\ExcelDemy\Excel to Word.docx"

Storing the path address for our word file.

If Dir(DocName) = "" Then
MsgBox "File " & DocName & vbCrLf & "not found " & vbCrLf & "C:\ExcelDemy\.", vbExclamation, "Document doesn't exist."
Exit Sub
End If

If the file address doesn’t match or is found, then it returns an error message. Then exit the procedure and the condition.

DocApp.Activate

Activate the previously created word application instance.

Set DocFile = DocApp.Documents(DocName)

Declare the word file in the defined variable.

If DocFile Is Nothing Then Set DocFile = DocApp.Documents.Open(DocName)

If the file is not open, then we open it.

DocFile.Activate

To activate the word file.

DocFile.Range.Paste

To paste the copied range from the Excel file into the word file.

DocFile.Save

Automatically save the word file after importing data from the Excel workbook.

DocApp.Quit

After importing data, close the word file.

Set DocFile = Nothing
Set DocApp = Nothing

To free the memory allocated to the two object variables.

Application.CutCopyMode = False

Removes the selection from the range.

2. Import Data into Word File from Excel with VBA Button

In this section, we will learn how to import data from an Excel workbook into a word file simply by clicking a button in VBA.

To perform this, we need to create a button first. So, let’s see how to do that.

Steps to Create a Button to Import Data into Word from Excel:

To assign a button in our dataset, go to the Developer tab.
From there, click Insert and select Button under the ActiveX Controls group.

There will be a plus symbol (+). Drag and release the symbol to create a button in any size anywhere in your spreadsheet.

After release, this will be the first look of our VBA Button.

To modify the Button, right-click on it. From the appeared list, select Properties. Make sure you have the Design Mode on from the Controls group in the menu bar. To turn on or off the Desing Mode, you just need to click on it.

A Properties pop-up window will open. Write the name that you want your Button to display in the Caption We named our Button “Click to Convert”, and you can provide any name that you want.

You can modify various properties of the Button from the Properties window, but as that is not the goal of this article, so we are skipping modifying the button any further.

On the other hand, we created the button to import data from one type of file to another, right? So, button styling processes are optional, you can do it if you want, or you can omit it. The button will still work even if it looks unappealing. Or it won’t? Let’s find it out.

Steps to Execute the VBA Macro:

Later, to assign VBA code in the Button, right-click on the Button and choose View Code from the appeared option. You can also double click on the Button to go to the code window.

After the double-clicking (or the right-clicking, whatever process you chose), you will be redirected to an automatically generated code window with a couple of auto-generated lines of code.

Then, copy the following code and paste it into the code window.

Private Sub CommandButton1_Click()
Dim iRange As Excel.Range
Dim DocApp As Word.Application
Dim DocFile As Word.Document
Dim WordData As Word.Table
Set iRange = ThisWorkbook.Worksheets("Sheet2").Range("B2:D14")
On Error Resume Next
Set DocApp = GetObject(class:="Word.Application")
If DocApp Is Nothing Then Set DocApp = CreateObject(class:="Word.Application")
DocApp.Visible = True
DocApp.Activate
Set DocFile = DocApp.Documents.Add
iRange.Copy
DocFile.Paragraphs(1).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
Set WordData = DocFile.Tables(1)
WordData.AutoFitBehavior (wdAutoFitWindow)
End Sub

Make sure you didn’t do any mistakes while modifying the code according to your dataset. For instance, change “Sheet2” according to your sheet name and “B2:D14” according to the range in your worksheet.

If you have written the code correctly, then save the macro.

Then go back to the worksheet of interest (where your data are) and click on the button to check whether it really works or not. But before clicking the Button, don’t forget to turn off the Design Mode from the Controls group in the menu bar.

If you turn off the Design Mode (to turn on or off the Design Mode, you just need to click on it), now click the Button.

As you can from the above image, there is a new word file created with the exact data from the Excel workbook just by clicking the Button in Excel VBA.

VBA Code Explanation

Dim iRange As Excel.Range
Dim DocApp As Word.Application
Dim DocFile As Word.Document
Dim WordData As Word.Table

First, declare the variables and objects to hold the range and control the word application.

Set iRange = ThisWorkbook.Worksheets("Sheet2").Range("B2:D14")

Then, store the worksheet name (“Sheet2” is the sheet name in our dataset) and the range (“B2:D14” is the range that stores the data) that will be imported to the defined range variable.

On Error Resume Next

If an error occurs, then go to the next statement.

Set DocApp = GetObject(class:="Word.Application")

After that, pass the word application in the GetObject function as a parameter and store that in the defined application variable. This can be accomplished by the special “class” reference with the VBA GetObject command.

If DocApp Is Nothing Then Set DocApp = CreateObject(class:="Word.Application")

If the file is not open, then we open it by creating an instance of the word application.

DocApp.Visible = True

To make the application visible.

DocApp.Activate

To activate the word file.

Set DocFile = DocApp.Documents.Add

Later, create a new document or file inside the word application.

iRange.Copy
DocFile.Paragraphs(1).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

To copy and paste the range into a new table in the word file. And the actions performed here will be as follows:

The table will be non-linked.
The source format will be Excel format, not Word format.
The text will not use the rich text format.

Set WordData = DocFile.Tables(1)
WordData.AutoFitBehavior (wdAutoFitWindow)

If the Excel range is wider than the document, then these lines of code will autofit the new data to fit the range within the margins of the newly created word file.

Important Things to Consider

If you get the word.application not defined error, then you must follow the steps shown below.

Select Tools -> References… from the menu bar in the code editor.

A References – VBAProject pop-up window will appear. From there, check the box of Microsoft Word 16.0 Object Library.
Later, click OK.

This will add the word object library reference to your project, and you will get rid of the error if you execute the code again.

Download Workbook

You can download the free practice Excel workbook from here.

Import Data from Excel into Word.xlsm

Conclusion

To conclude, this article showed you 2 effective ways how to import data from Excel into word automatically using the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Get FREE Advanced Excel Exercises with Solutions!