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
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
Read More: Open Word Document and Save As PDF or Docx with VBA Excel
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.
Read More: Excel VBA: Open Word Document and Paste
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
