To write to a word file using Excel we need to access the word application using Excel VBA. In this tutorial we will learn how to open a word application, add a document and write content to it using Excel.
We have learned about the CreateObject method. In this article, we will use that method to create an object of word application instead of using the reference of it. So let's get started with the tutorial.
To write a word file using VBA, we first need to open the Word Application of course. Then add a document to it. Select the paragraph and write the text to it. Each of these steps can be done easily from Excel. You will not need to interact with the Word document.
Let's get started with an example without getting any further into theory. Because Iron Man said, "Sometimes you gotta run before you can walk".
Sub WriteToWord() ''Using Early Binding 'Dim wordApp As Word.Application 'Dim mydoc As Word.Document 'set wordApp=new word.Application 'Using late binding to create word application object 'Declaring variables as objects Dim wordApp As Object Dim mydoc As Object 'Initializing the object using CreateOBject Function Set wordApp = CreateObject("Word.Application") 'Making word App Visible wordApp.Visible = True 'Creating a new document Set mydoc = wordApp.Documents.Add() 'Getting input from user myString = Application.InputBox("Write Your Text") 'Writing to word wordApp.Selection.TypeText Text:=myString 'inserting a new paragraph wordApp.Selection.TypeParagraph End Sub
Explanation of the Code:
Well I have explained each step in the code itself using comments but let's have some word about the lines we have used in this sub.
So this VBA code takes input from the user and writes to a new word document.
The first few lines are commented out. These lines are using Late binding. If you have given reference to the word application you can remove the commenting tag. The code we are processing is using late binding. I have explained it in this article. You can refer to it to understand what is late binding and early binding in Excel VBA.
'Declaring variables as objects
Dim wordApp As Object
Dim mydoc As Object
Here we have declared two variables of type Object. The Object type variable can hold any type of object.
'Initializing the object using CreateOBject Function
Set wordApp = CreateObject("Word.Application")
'Making word App Visible
wordApp.Visible = True
'Creating a new document
Set mydoc = wordApp.Documents.Add()
In the first line above, we are intentiating the wordApp variable with an object of type Word.App using the CreateObject method. This will open the Word Application.
In the second line we are making the word application visible so that we can work with it.
In the next line, we add a new document to the word application using Word.Documents.Add() function. This is stored in the mydoc variable.
'Getting input from user
myString = Application.InputBox("Write Your Text")
Here we are simply getting input from the user using the InputBox function of Application Class. And we are storing the input in the mystring variable.
'Writing to word
'inserting a new paragraph
Finally, we are using the TypeText method of Word Application.Selection class to write to the document. The last line enters into a new paragraph.
So yeah guys, this is how you can create a new Word document and write to it using Excel VBA. I have not gone into details as it would make the article exhaustingly long. This was only to learn how you can write to a word document through Excel. I hope it helped you understand the process. If you have any questions regarding this article, you can contact me through the comments section below.
Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.