When dealing with VBA for Excel, you'd be forgiven for thinking that your code would work with Excel only. However, the fact that VBA (Visual Basic for Applications) is common to all the applications within Office Suite opens up a host of possibilities.
For instance, you can capture data in Excel via a user form and then automatically export it to a Word document. The beauty of this is that everything executes in the background. In other words, you don't need to manually copy data from Excel, search for the correct Word document to paste this information, save the file, and then close Word again.
Our book VBA Automation for Excel 2019 Cookbook addresses these topics.
In this article, we’ll look at recipes for:
- Creating a new instance of Word
- Writing and formatting text
- Copying data into Word
- Using templates and bookmarks
By the end, you’ll be able to create Word documents from within Excel.
Technical requirements
These recipes were written and designed to be used with MS Office 2019 and MS Office 365, installed on either Windows 8, 8.1, or 10.
If your hardware and software meet these requirements, you are good to go.
Demonstration files can be downloaded from https://github.com/PacktPublishing/VBA-Automation-for-Excel-2019-Cookbook.
Please visit the following link to check the Code in Action videos: https://bit.ly/3jQRvVk.
Creating a new instance of Word
The first step in the process of creating a Word document from within Excel requires some changes to the available references. To be specific, we have to set a reference to Word's object library in the VBA Editor. Without this step, Excel cannot communicate with Word at all, let alone create documents and paragraphs.
In this recipe, we will be creating a new instance of Word from within Excel.
Getting ready
Open Excel, and activate a new workbook. Save the file as a macro-enabled file (*.xlsm) on your desktop and call it Word_Interaction.xlsm. Sheet1 should be active. Press Alt + F11 to switch to the VBA Editor, and then insert a new module.
It goes without saying that MS Word must also be installed on your computer in order for the instructions in this recipe to work effectively.
How to do it…
Here’s how to link Word to Excel:
1. In the VBA Editor, click on Tools | References. The References – VBAProject dialog box will open:
Figure 1 – The References - VBAProject dialog box
2. Scroll down the list of available references until you find Microsoft Word 16.0 Object Library. Earlier versions of Word will have a different number, but will still refer to the Word object library:
Figure 2 – The Microsoft Word 16.0 Object Library selected
3. Once selected, click on OK to save the selection and close the dialog box.
Note
: The reference to the Word object library is only valid for the Excel workbook you're currently working in. Every new workbook will have to be referenced to Word in exactly the same way.
4. Create a Sub procedure to initiate Word from within Excel. There are several ways of doing this, but for this recipe, we will be using this specific technique:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
wdApp.Visible = True
wdApp.Activate
End Sub
5. Press F5 to run the procedure, or to test it, if you will. A new instance of Word will appear on your screen. Close the Word application once you know that the Sub procedure is working.
6. Opening Word without opening a new document is not very useful. Add the next lines of code to the Sub procedure to open a new document:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
.Documents.Add
End With
End SubNote: Because we need to use the Word.Application keyword every time we refer to MS Word, the loop structure makes it easier to refer to Word.Application via the wdApp variable.
- If you now run the procedure, Word will open again, this time displaying a new document, Document1.
- Close Word, but don't save the document. Return to the VBA Editor in Excel.
These steps will enable you to create a new instance of Word from within Excel, using VBA for Excel.
How it works…
Enabling the Microsoft Word object library for this Excel workbook made it possible to use Word keywords and methods within Excel. These keywords can open an instance of Word, as well as a new Word document, all from within Excel.
Writing and formatting text
It's all very well to know how to open Word with a new document available. However, we need more than this. A heading for the new document would be a good start, but that is still not enough. Formatting the heading is also necessary, and will round it off professionally.
In this recipe, we will be writing and formatting text.
Getting ready
Make sure Word_Interaction.xlsm is still open, and that the VBA Editor is active.
How to do it…
These are the steps to enter and format text in Word:
1. To add text to a Word document via Excel, add the following line of code:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
.Documents.Add
.Selection.TypeText "Employee Information"
End With
End Sub2. Press F5. Running the Sub procedure will now result in a new instance of Word, with a new document open, and the line of text in the Sub procedure at the top of the page. Close Word without saving, since there is more code to add to our procedure in Excel:
Figure 3 – New instance of Word, with document and text
3. It is quite clear that we need to do some formatting here. By starting a nested loop structure, this will save us repeating the keywords. Furthermore, if you haven't done it so far, you can use IntelliSense to assist you with all the Word keywords. Add the following lines of code to the VBA Editor:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
.Documents.Add
With .Selection
.ParagraphFormat.Alignment = _
wdAlignParagraphCenter
.BoldRun 'Switch Bold on
.Font.Size = 16
.TypeText "Employee Information"
.BoldRun 'Switch Bold off
.TypeParagraph 'Enter a new line
.Font.Size = 11
.ParagraphFormat.Alignment = _
wdAlignParagraphLeft
.TypeParagraph
End With
End With
End Sub
4. When you run the Sub procedure now, there will be a marked improvement in appearance:
Figure 4 – Formatting done via VBA in Excel
Using these principles, users will be able to format a Word document with VBA coding in Excel.
Copying data into Word
We could have opened Word manually and achieved what we've just done, but the whole purpose of the exercise is to do it from Excel, so that data in a spreadsheet can be written to the Word document automatically.
What we need to do now is select data, copy it, and then paste that as part of the opening and formatting process.
In this recipe, we will be copying data into Word.
Getting ready
Make sure that Word_Interaction.xlsm is still open. Activate Sheet1, and enter the following data:
Figure 5 – Working data
How to do it…
Follow these steps to copy text from Excel to Word:
1. Create code to automatically select whatever range is on a sheet:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
.Documents.Add
With .Selection
.ParagraphFormat.Alignment = _
wdAlignParagraphCenter
.BoldRun 'Switch Bold on
.Font.Size = 16
.TypeText "Employee Information"
.BoldRun 'Switch Bold off
.TypeParagraph 'Enter a new line
.Font.Size = 11
.ParagraphFormat.Alignment = _
wdAlignParagraphLeft
.TypeParagraph
End With
End WithRange("A1", Range("A2").End(xlDown) _
.End(xlToRight)).Copy
wdApp.Selection.Paste
End Sub
2. Run the Sub procedure to check your coding. We know that the first part will work, but in this case, we need to see whether the data on our spreadsheet was copied into Word:
Figure 6 – Excel data copied and pasted into Word
3. Saving the Word document is what we need to do next. When you add these lines of code, pay attention to the changes in the With statement:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
.Documents.Add
With .Selection
.ParagraphFormat.Alignment = _
wdAlignParagraphCenter
.BoldRun 'Switch Bold on
.Font.Size = 16
.TypeText "Employee Information"
.BoldRun 'Switch Bold off
.TypeParagraph 'Enter a new line
.Font.Size = 11
.ParagraphFormat.Alignment = _
wdAlignParagraphLeft
.TypeParagraph
End With
Range("A1", Range("A2").End(xlDown) _
.End(xlToRight)).Copy
.Selection.Paste
.ActiveDocument.SaveAs2 Environ("UserProfile") _
& "\Desktop\EmployeeReport.docx"
End With
End Sub
4. The last thing we need to do is close down the document, and finally Word itself. Just add the following two lines in a new line after the filename:
.ActiveDocument.Close
.Quit
5. When you run the procedure now, you will see Word open briefly, and immediately close again. If you want to eliminate that, simply comment the following two lines out:
'.Visible = True
'.Activate6. We're still not done, because every time we run the procedure, the file is replaced without giving us the option of saving it under a new name. We need to add code to create a unique filename every time we run the procedure. By declaring a new variable, SaveAsName, and then assigning a formatted version of the Now function, we can create a unique name for the file every time it is saved. Add these two lines after .Selection.Paste:
SaveAsName = Environ("UserProfile") _
& "\Desktop\EmployeeReport " _
& Format(Now, "yyyy-mm-dd hh-mm-ss") & ".docx"
.ActiveDocument.SaveAs2 SaveAsName
7. Every time you run the procedure now, a new file with a unique name will be saved. Following these instructions will enable you to automatically copy data from Excel into Word.
How it works…
Here’s an explanation of what we did.
Selecting data in Excel is not a new concept, and neither is copying. Saving it in a Word document with Excel VBA requires making use of the Environ function, as well as formatting of the filename. This will ensure that a new file is created every time the Excel VBA procedure is executed.
Using templates and bookmarks
To copy an entire Excel spreadsheet and paste it into Word doesn't make sense. You could rather have done everything in Word from the beginning, saving you the whole effort of copying from Excel and pasting in Word.
The point is, if you have an existing Word document or even a Word template, and you regularly need to export selected information from a spreadsheet in Excel to this template, this is the way to do it. The word automation acquires new meaning if you can link Excel with Word in this manner.
In this recipe, we will be pasting Excel data into a Word document at a specific bookmark.
Getting ready
Make sure that Word_Interaction.xlsm is still open, and that the VBA Editor is active.
How to do it…
We need to do the following:
1. Open the Word document that was saved to the desktop. Delete the inserted table (select only the entire table – no lines before or after, click the Layout contextual tab, and then Rows & Columns | Delete | Delete Table)
2. Enter the short sentence Latest information on employees in the open space.
3. Insert a bookmark in the first open line after this sentence. Click on Insert | Bookmark. The Bookmark dialog box will appear. Enter a name in the Bookmark name textbox, and then click on Add to close the dialog box:
Figure 7 – Bookmark name
4. Click on File | Save As, and save the file as a Word template. The file extension is .dotx. Call the file EmployeeReportTemplate.
5. Depending on the operating system and the version of MS Office on your PC, the file will be saved in a specific folder, dedicated to template files:
Figure 8 – Folder for template files
6. Close the file and return to the VBA Editor in Excel.
7. Add the following line of text in the code window to open a specific file, and not just a blank document:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
.Documents.Add "C:\Users\User\Documents\Custom _
Office Templates\EmployeeReportTemplate.dotx"
8. Because we're making use of a template, we do not need any of the formatting we did for the previous document. Delete the following lines of code:
With .Selection
.ParagraphFormat.Alignment = _
wdAlignParagraphCenter
.BoldRun 'Switch Bold on
.Font.Size = 16
.TypeText "Employee Information"
.BoldRun 'Switch Bold off
.TypeParagraph 'Enter a new line
.Font.Size = 11
.ParagraphFormat.Alignment = _
wdAlignParagraphLeft
.TypeParagraph
End With9. Use the GoTo method to instruct Word exactly where the copied data from Excel must be inserted. That's why we created a bookmark in the Word template. The final coding for the entire procedure should look like this:
Sub CreateWordDoc()
Dim wdApp As Word.Application
Dim SaveAsName As String
Set wdApp = New Word.Application
With wdApp
'.Visible = True
'.Activate
.Documents.Add "C:\Users\User\Documents\Custom _
Office Templates\EmployeeReportTemplate.dotx"
Range("A1", Range("A2").End(xlDown) _
.End(xlToRight)).Copy
.Selection.Goto wdGoToBookmark, , , "ExcelTable"
.Selection.Paste
SaveAsName = Environ("UserProfile") _
& "\Desktop\EmployeeReport " _
& Format(Now, "yyyy-mm-dd hh-mm-ss") & ".docx"
.ActiveDocument.SaveAs2 SaveAsName
.ActiveDocument.Close
.Quit
End With
End SubThese instructions will enable you to create a template in Word, and then automatically place content from Excel into the Word document at specific bookmarks.
Inserting Excel data into a Word template can be done with Excel VBA. Inserting a bookmark in the Word template enables Excel to send data to a specific insertion point for the data to be pasted.
See also
Visit https://docs.microsoft.com/en-us/office/vba/api/word.selection.goto for more information on the GoTo method.
To read a more in-depth guide check out our book VBA Automation for Excel Cookbook, authored by Mike Van Niekerk.
You can get 25% off at http://packt.live/3u1fPtG. Use discount code 25VBA (Promotion running from 10th February through 15th March).