While working in Excel, we often need to generate a word Document from Macro to copy our valuable data from the Excel worksheet to the word file. In this article, I’ll show you how you can generate a word document from an Excel Macro comfortably and conveniently.

VBA Code to Generate a Word Document From an Excel Macro (Quick View)

Sub Generate_Word_Document_from_Excel_Macro()

Set Word_Object = CreateObject("Word.Application")
Set Doc_Object = Word_Object.Documents.Add
Set Selection_Object = Word_Object.Selection

Word_Object.Visible = True

Selection_Object.Font.Size = 16
Selection_Object.Font.Name = "Times New Roman"
Selection_Object.Typetext "Employee Record of Jupyter Group"

ActiveSheet.UsedRange.Copy
Selection_Object.Paste

Doc_Object.SaveAs ("F:\ExcelDemy\MyWordFile")

End Sub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

An Overview of the VBA Code to Generate a Word Document from an Excel Macro (Step-by-Step Analysis)

So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to generate a word document from an Excel Macro in detail.

⧪ Step 1: Setting the Necessary Objects

At the very outset of the code, we have to set the necessary objects required in this code. They are:

• A Word Object
• A Doc Object
• A Selection Object

Set Word_Object = CreateObject("Word.Application")
Set Doc_Object = Word_Object.Documents.Add
Set Selection_Object = Word_Object.Selection

⧪ Step 2: Making the Word File Visible

The moment you declare the word object, the word file is created. Now we need to make it visible.

Word_Object.Visible = True

⧪ Step 3: Adding Text to the Word File

We have generated our word file. Now if you wish, you can add any text to the file.

Also, before adding the text, you can change the font name and size according to your wish.

Let’s change the font to Times New Roman, font size to 16, and then add the text “Employee Record of Jupyter Group”.

Selection_Object.Font.Size = 16
Selection_Object.Font.Name = "Times New Roman"
Selection_Object.Typetext "Employee Record of Jupyter Group"

⧪ Step 4: Copying Data from Excel Worksheet to the Word File

You can also copy data from your Excel worksheet to the word file. Here, our active worksheet contains a data set of the names, salaries, and joining dates of some employees of Jupyter Group.

To copy this data set to the generated word file, use these lines of code.

ActiveSheet.UsedRange.Copy
Selection_Object.Paste

Note: Here, I’ve used the VBA UsedRange object to copy all data from the active worksheet. If you want to copy a specific range, use that range instead.

For example, to copy range B4:D13, use ActiveSheet.Range(“B4:D13”).Copy.

Also, in place of ActiveSheet, you can use the name of a specific worksheet.

For example, Worksheets(“Sheet1”).Range(“B4:D13”).Copy.

⧪ Step 5: Saving the Word File

Finally, if you want you can save the word file in your desired folder in the desired name.

For example, to save it in the path F:\ExcelDemy with the name MyWordFile, use:

Doc_Object.SaveAs ("F:\ExcelDemy\MyWordFile")

So the complete VBA code will be:

⧭ VBA Code:

Sub Generate_Word_Document_from_Excel_Macro()

Set Word_Object = CreateObject("Word.Application")
Set Doc_Object = Word_Object.Documents.Add
Set Selection_Object = Word_Object.Selection

Word_Object.Visible = True

Selection_Object.Font.Size = 16
Selection_Object.Font.Name = "Times New Roman"
Selection_Object.Typetext "Employee Record of Jupyter Group"

ActiveSheet.UsedRange.Copy
Selection_Object.Paste

Doc_Object.SaveAs ("F:\ExcelDemy\MyWordFile")

End Sub

Developing the Macro to Generate a Word Document from an Excel Macro

We’ve seen the step-by-step analysis of the code to generate the word document from the Macro.

Now let’s see how to use the code to generate the document.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Enabling the Necessary References

Go to Tools > References in the toolbar above. Click on References.

The References dialogue box will open. Check Microsoft Office 16.0 Object Library in case it’s unchecked.

⧪ Step 3: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

⧪ Step 4: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

⧪ Step 5: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The code will run. First of all, it’ll open a new word document containing the inserted text and the Excel data set.

Also, it’ll save the document in the desired path on your computer in the desired name.

Things to Remember

• Make sure the path mentioned in the code to save the generated document is a valid one on your computer. Otherwise, it’ll show an error.
• It may take some time to generate the word file and save it depending on the configuration of your computer. So be patient and wait until the process finishes successfully.

Conclusion

Therefore, this is the process to generate a word document from an Excel Macro. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

The post How to Generate a Word Document from an Excel Macro appeared first on ExcelDemy.