Excel VBA: Open Word Document and Paste (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel VBA Open Word Document and Paste

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 Sub

Output: The above code successfully created a new document file and copied the table from the Excel worksheet to the document file.

Excel VBA Open Word Document and Paste

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.

Excel VBA Open Word Document and Paste

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 Sub

Output: The above code successfully opened the existing file and copied the table from the Excel worksheet to the document file.

Excel VBA Open Word Document and Paste

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 Sub

Running 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.

Excel VBA Open Word Document and Paste


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo