This article illustrates how to open a new or existing word document from Excel and paste data into it using VBA with 3 different examples. We’ll use Excel’s built-in functions and properties to configure our code. Let’s dive into the examples to explore the techniques that can get your job done fast and easily.
3 Suitable Examples to Open Word Document from Excel and Paste Data in It Using VBA
Write Code in Visual Basic Editor
To open a Word document and paste data in Excel, we need to open and write VBA code in the visual basic editor. Follow the steps to open the Visual Basic Editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.
Now put your code inside the visual code editor and press F5 to run it.
1. Create and Open a New Word Document from Excel to Paste Data into It
Task: Open a new Word document, then copy and paste data from the Excel worksheet to the word file.
Solution: We need to use different methods and properties to accomplish the above task. Let’s discuss the solution.
- We need to use the CreateObject function to create a new word file. The only argument is the application name and class of the object to create. The syntax of the function-
CreateObject(class, [servername])
In our code, to open a word document, the line of code is-
CreateObject(Class:="Word.Application")- To add a new and empty document in the word file, we’ll use the Add method in our code.
- To get the row and column number of the used range from the Excel worksheet, we need to use the UsedRange property. In this example, we’ll use the active sheet property everywhere. For example, the number of rows in the active sheet’s used range is-
 ActiveSheet.UsedRange.Rows.Count- We need to use the Add method to create and define the table structure in the new document. And we’ll use the extracted row and column numbers from Excel’s used range to define it.
Code: Insert the following code in the Visual Basic Editor and press F5 to run it.
Sub OpenDocumentAndPaste()
   On Error Resume Next
   Dim rowNum, colNum As Integer
   rowNum = ActiveSheet.UsedRange.Rows.Count
   colNum = ActiveSheet.UsedRange.Columns.Count
   Dim mWord As Object
   Set mWord = CreateObject(Class:="Word.Application")
   mWord.Visible = True
   mWord.Activate
   Dim ActiveDocument
   Set ActiveDocument = mWord.Documents.Add
   Dim docRng
   Set docRng = ActiveDocument.Range
   ActiveDocument.Tables.Add docRng, rowNum, colNum
   Dim docTbl
   Set docTbl = ActiveDocument.Tables(1)
   docTbl.Borders.Enable = True
   Dim countRow, countCol As Integer
   For countRow = 1 To (rowNum + 1)
       For countCol = 1 To (colNum + 1)
           Dim data As Variant
           data = ActiveSheet.Cells(countRow, countCol)
           docTbl.cell(countRow - 1, countCol - 1).Range.Text = data
       Next countCol
   Next countRow
   Set mWord = Nothing
End SubOutput: The above code successfully created a new document file and copied the table from the Excel worksheet to the document file.
Read More: Excel VBA: Open Word Document and Replace Text
2. Open an Existing Word Document File with a Specific Location from Excel and Paste Data into It
Task: Open an existing Word document with a specific file location, then copy and paste data from the Excel worksheet to the word file.
Solution: In this case, to open an existing document file we need to use the Documents.Open method instead of Documents.Add method.
To get the exact document file location, we can do this-
- Go to the document file location.
- Press the Shift key and right–click on the document file.
Then we’ll store the path location into a variable named fileLoc and then use it in the method.
Code: Insert the following code in the Visual Basic Editor and press F5 to run it.
Sub OpenDocumentAndPaste()
   On Error Resume Next
   Dim rowNum, colNum As Integer
   rowNum = ActiveSheet.UsedRange.Rows.Count
   colNum = ActiveSheet.UsedRange.Columns.Count
   Dim mWord As Object
   Set mWord = CreateObject(Class:="Word.Application")
   mWord.Visible = True
   mWord.Activate
   Dim ActiveDocument
   fileLoc = "D:\Exceldemy\Doc1.docx"
   Set ActiveDocument = mWord.Documents.Open(fileLoc)
   Dim docRng
   Set docRng = ActiveDocument.Range
   ActiveDocument.Tables.Add docRng, rowNum, colNum
   Dim docTbl
   Set docTbl = ActiveDocument.Tables(1)
   docTbl.Borders.Enable = True
   Dim countRow, countCol As Integer
   For countRow = 1 To (rowNum + 1)
       For countCol = 1 To (colNum + 1)
           Dim data As Variant
           data = ActiveSheet.Cells(countRow, countCol)
           docTbl.cell(countRow - 1, countCol - 1).Range.Text = data
       Next countCol
   Next countRow
   Set mWord = Nothing
End SubOutput: The above code successfully opened the existing file and copied the table from the Excel worksheet to the document file.
Read More: How to Generate a Word Document from an Excel Macro
3. Open an Existing Word Document File with File Selection Option from Excel and Paste Data into It
Task: Open an existing Word document with the file selection option, then copy and paste data from the Excel worksheet to the word file.
Solution: We need to use the Application.GetOpenFilename method to open a dialogue box to select our desired document file.
Code: Insert the following code in the visual basic editor and press F5 to run it.
Sub OpenDocumentAndPaste()
   On Error Resume Next
   Dim rowNum, colNum As Integer
   rowNum = ActiveSheet.UsedRange.Rows.Count
   colNum = ActiveSheet.UsedRange.Columns.Count
   Dim mWord As Object
   Set mWord = CreateObject(Class:="Word.Application")
   Dim ActiveDocument
   Set ActiveDocument = mWord.Documents.Open(Application.GetOpenFilename())
   mWord.Visible = True
   mWord.Activate
   Dim docRng
   Set docRng = ActiveDocument.Range
   ActiveDocument.Tables.Add docRng, rowNum, colNum
   Dim docTbl
   Set docTbl = ActiveDocument.Tables(1)
   docTbl.Borders.Enable = True
   Dim countRow, countCol As Integer
   For countRow = 1 To (rowNum + 1)
       For countCol = 1 To (colNum + 1)
           Dim data As Variant
           data = ActiveSheet.Cells(countRow, countCol)
           docTbl.cell(countRow - 1, countCol - 1).Range.Text = data
       Next countCol
   Next countRow
   Set mWord = Nothing
End SubRunning the above code would open the file explorer to select the document file.
Output: The above code successfully opened the existing file and copied the table from the Excel worksheet to the document file.
Things to Remember
In the above examples, we took the used range to copy and paste data from an Excel worksheet to the Word file. We could apply different conditions to choose a specific range of data cells. In addition, we used the active worksheet to get data, for our convenience. The code can be easily configured to choose a specific or user-defined worksheet easily.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to open a document to copy and paste data from an Excel worksheet using VBA code with the help of suitable examples. Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
