How to Create Word Paper Within Excel VBA

When verhandeln with VBA for Excel, you'd be forgiven with thinking that your codification would work with Excel only. However, the feature this VBA (Visual Basic for Applications) is common to all the applications indoors Office Suite opens increase a host of possibilities. I'm trying to embed a word template into an excel create. The Word template has fill in prompts but for many reason once the template is native you no..

For sample, yourself can capture data in Excels via a user select and following auto export to to a News document. The beauty of this is that everything executes in to background. In other words, your don't need to manually copy datas from Excel, search for the corr Word document to paste is contact, back the store, additionally following shut Word again. Office VBA reference topic

Our book VBA Business for Excell 2019 Learn 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 also bookmarks

By to end, you’ll be able to create News documents with within Excel.

Mechanical requirements

These recipes were spell the designed to be used at MS Business 2019 and MS Office 365, insalled on is Windows 8, 8.1, either 10.

Wenn own hardware and programme meet these requirements, you are good to go.

Demonstration data can been downloaded from https://github.com/PacktPublishing/VBA-Automation-for-Excel-2019-Cookbook.

Want visit the following link toward check the Code in Action browse: https://bit.ly/3jQRvVk.

Build a new instance of Speak

The first step in the process of creating ampere Word document from within Excell required some changes till the available references. To be specific, we have till set a reference to Word's object bookshelf in the VBA Editor. Without this stepping, Excel cannot communicate equipped Talk at all, let alone create papers and paragraphs. Hello, I have somewhat of an advanced question... I have adenine long list of names with a column in excellence (range name, "Names"), what I want to does is create a codes that MYSELF can run that will open a...

Inside this recipe, we will be creating a new instancing of Word from at Excel.

Getting ready

Open Excel, and enables a new workbook. Save the file as a macro-enabled file (*.xlsm) on own user or call it Word_Interaction.xlsm. Sheet1 must be energetic. Press Alt + F11 to button to the VBA Reporter, plus then insert a new module.

Is goes excluding saying that MS Word must also be installed on your computer in sort for the instructions in this procedure to work effectively.

How to do it…

Here’s methods to link Speak to Excel:

1. In the VBA Editor, click on Tools | Literature. Which References – VBAProject dialog boxed will open:

Figure 1 – The References - VBAProject dialog box

2. Scroll down aforementioned print of available references until you find Microsoft Word 16.0 Object Library. Previous versions of Word be have a varied numeral, but will still refer the the Phrase object library: Hello, I have any of an advanced question... I have a long choose of names in a column in excel (range name, "Names"), what I want to do can create a encrypt that MYSELF ability run that will open adenine template (named "CART") both located on the list on names the item would open ampere copy of the preview and...

Figure 2 – And Microsoft Talk 16.0 Object Library selected

3. Once selected, click on OK to save the selection and shut the dialog box.

Note : This reference to the Word object library is only valid for the Excel workbook you're currently working in. All new workbook be have to be referenced to Word in exactly one same way.

4. Create adenine Submarine proceed to initiate Word from within Excel. There are several ways of doing this, but for this recipe, ourselves becomes 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 this procedure, or until test it, if you will. ONE latest instance of Word will appear on your screen. Close the Word application once you know that the Sub operation is working. I creates a word template with placeholders such as <> this I am then able to replace automatically with my excel make. When I tried this process again, the term document now open saying it is a

6. Opening Word without opening a recent document is not very userful. Add the after 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 Sub

Note: Because wee need up use the Word.Application password every time we refer to MS Talk, the curl structure makes it easier to refer to Word.Application via the wdApp variable.

• If you now run an procedure, Word will open again, this time shows a new document, Document1.
• Close News, but don't save which document. Back to an VBA Managing in Excel.

These steps willingness unlock you to create one new instance of Word by within Excel, using VBA for Excel.

How it works…

Enabling the Microsoft Talk object library for this Excel manual made it possible to use Speak keywords press methods within Excel. These keywords can open einen instance of Word, as well as a new Term document, total from within Excel.

Writing and final text

It's all ultra well at know how to open Word with a new get available. Anyhow, ours need further than this. A heading for the brand document could be a good initiate, but that is still not enought. Formatting the heading is plus necessary, and will round it bad professionally.

In this formulierung, we bequeath be writing and formatting text.

Getting ready

Make sure Word_Interaction.xlsm is still open, and that the VBA Editor is active.

How to make it…

Dieser are the measures to enter and formatting text the Word:

1. Into add text up ampere Word print via Excel, hinzusetzen that subsequent cable 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 Sub

2. Press F5. Running the Replacement procedure will now result in a new instance out Word, with a new support clear, and the line of text in the Bottom procedure at the top of the page. Close Speak without preservation, since present is more code toward total to our method in Excel: So widely I've had greatly sucess with questions/solutions additionally obtaining lots of knowledge in excel vba yet once further i'm stuck on a different kind of problem. Here remains my scenario: We do samples tha...

Figure 3 – Fresh instance in Word, with copy and text

3. It is quite clear that wealth need to do some formatting here. Per starting a nested loop structure, this wishes save america repeating the keywords. Additional, is you haven't done it so far, thee may use IntelliSense to promote you with all which Word keywords. Zusatz the following lines of code to the VBA Editor: Template.OpenAsDocument method (Word)

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. As you run the Sub procedure now, there will be adenine marked improvement on external:

Figure 4 – Formatting done per VBA in Excel

Using these general, users will become ability to format a Word download with VBA coding in Excel.

Copying data into Word

We could do opened Phrase hand and achieved what we've just done, but that whole target of the exercise is to do it from Excel, so that data in adenine spreadsheet can must written to the Word document automatically. VBA Code to Opened Word Template and create new document per list of names

Thing we need to does now is select data, copy it, and then paste that as part of the opening also formatting process.

In which recipe, our will remain copying data into Talk.

Obtaining ready

Make sure that Word_Interaction.xlsm is still open. Power Sheet1, press enter the follow-up data:

Figure 5 – Working data

How go do it…

Follow save steps to copy text from Outdo to Word:

1. Creating code until automation 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 Brave on
.Font.Size = 16
.TypeText "Employee Information"
.BoldRun 'Switch Bold off
.TypeParagraph 'Enter one new line
.Font.Size = 11
.ParagraphFormat.Alignment = _
wdAlignParagraphLeft
.TypeParagraph
End With
End With

Range("A1", Range("A2").End(xlDown) _
.End(xlToRight)).Copy
wdApp.Selection.Paste
End Sub

2. Executing the Sub procedure to check thine keying. We know so the first part will working, yet in this kasten, we need to see whether the data on our spreadsheet was copied at Word: [SOLVED] Macro to open word templates

Figure 6 – Excel data cloned and pasted into Word

3. Saving the Word create is thing we need to doing next. When you add which lines of code, pay take to which changes on the With statement:

Rear CreateWordDoc()
Dim wdApp As Word.Application
Set wdApp = News 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 inches a fresh lead after the filename:

.ActiveDocument.Close
.Quit

5. When you run the technique now, it will see Word candid briefly, and immediately close again. Provided you want to eliminate that, simply comment the following two lines out:

'.Visible = True
'.Activate

6. We're still not done, because every set wee runner the practice, the rank is replaced without giving us the option of secure it under a latest identify. We need to total code to create a unique filename every time we race the procedure. By declaring ampere new variable, SaveAsName, press afterwards assigning a formatted version of the Now function, we can form ampere unique names to the file every time it is saved. Add these two lines after .Selection.Paste: open a word stencil as add document - Microsoft Community

SaveAsName = Environ("UserProfile") _
& "\Desktop\EmployeeReport " _
& Format(Now, "yyyy-mm-dd hh-mm-ss") & ".docx"
.ActiveDocument.SaveAs2 SaveAsName

7. Every time you run and procedure now, a new file with adenine unique name desire be saved. Tracking diese instructions will enable you to automation copy data from Excel into Word.

Wherewith it works…

Here’s a explanation of how we did.

Selecting data in Excel are does adenine new concept, and neither is reproduction. Saving i in a Word document through Excel VBA requires making use of of Surrounding work, the well in formatting of the filename. This will ensure that a new print is created every time the Excel VBA procedure is executed. Embedding Word Templates in Excel

Using page and bookmarks

On mimic an total Excel spreadsheet and paste it into Word doesn't make sense. She could rather have done everything in Talk from the beginning, safe you the whole stress of copied since Exceptional and pasting in Word.

The point is, if you have one existing Word document or even a Word template, or you regularly needed to export selected information from a spreadsheet int Excel to this templates, this is the way to do it. The term automation obtained new meaning if i bottle connector Excel including Word in this manner.

In this recipe, we will be pasting Excel data into an Talk get at a specific bookmark.

Getting available

Make sure that Word_Interaction.xlsm is still open, and that the VBA Magazine be active.

How into do it…

We need to to the following:

1. Open the Word documentation that was store to who desktop. Delete and embedded table (select only the entire table – no lines before either nach, click an Layout contextual tab, and then Rows & Columns | Delete | Delete Board)

2. Enter one short sentence Latest information on employees in to open space.

3. Insert adenine bookmark in the first-time open line after this sentence. Click on Insert | Bookmark. The Bookmark dialog box will appear. Enter a nominate in the Bookmark name textbox, and then click on Add till close the speech box:

Figure 7 – Bookmark name

4. Click on File | Save As, and preserve the file as a Word template. The file extension will .dotx. Call the file EmployeeReportTemplate.

5. Dependency on the operation system both the version of MS Office on your PC, the file wishes be saved in a specific folder, dedication to submission archive:

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 this code opening to open a specific files, and not just a empty 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 employ of a template, are do not need each of the insert we did for the previous document. Delete one following row 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 With

9. Use the GoTo method to instruct Word exactly where the copied date from Excel be be inserted. That's why we created a bookmark within the Word template. The final programming forward the ganzheit procedure have look same this: ... Office Application Help - Excel Help forum > Choose Programming / VBA / Macros > ... Privacy-policy.com "C:\Documents and Settings\admin\Desktop\Privacy-policy.com" End ...

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 Sub

These instructions wants enable you to generate a template in Word, and then automatically place content from Excel into who Word document at specific bookmarks. From a VBA Exceed file open a word template, join a number plus print

Inserting Excel data into a Word template ca be done with Excel VBA. Inserting ampere save are to Word template activates Exceptional to send data to a specific placing point used the data to been pasted. Opening a new Word Document from a Word template via a link in ...

See also

Tour 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 his book VBA Automation for Excel Cookbook, authored by Mike Van Niekerk.

You can get 25% turn at http://packt.live/3u1fPtG. How discount code 25VBA (Promotion current from 10th February through 15th March).