Xem Nhiều 12/2022 #️ Office Space: From Vba Macro To Word Add / 2023 # Top 20 Trend | Trucbachconcert.com

Xem Nhiều 12/2022 # Office Space: From Vba Macro To Word Add / 2023 # Top 20 Trend

Cập nhật thông tin chi tiết về Office Space: From Vba Macro To Word Add / 2023 mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.

Office Space

From VBA Macro to Word Add-in

Robert Bogue

Code download available at: OfficeSpace2008_05.exe(166 KB)

Contents

Book Learning Macro Basics Moving Macros to VSTO Writing New Add-In Code More Buttons

Document automation isn’t a new functionality by any means. It has been going on since the invention of macros, and there has been a full-fledged programming model within Microsoft® Office applications since the early 1990s. For many years, the Office tools have featured macro recorders that weren’t limited to simply replaying keystrokes but have been capable of writing code on the fly as well. Macros have even demonstrated that they are functional enough to write viruses. And while writing a virus using a Word macro may be a dubious feat, it is a feat nonetheless.

However, even with all of the capabilities of Visual Basic® for Applications (VBA), there are some things it just doesn’t do very well. In the early days of VBA, XML hadn’t yet been invented, the Internet was in its infancy, and the first HTML pages were just beginning to appear. As such, it’s no wonder that making a Web service call isn’t well handled in the context of Visual Basic for Applications.

The Microsoft .NET Framework CLR, on the other hand, understands these technologies quite well. The ability to call Web services is just one among a number of reasons for wanting to write .NET-targeted code rather than VBA-and doing so means using Visual Studio® Tools for Office (VSTO). But switching from VBA to VSTO doesn’t have to be a case of throwing the baby out with the bath water; rather, it can be just a natural way to extend how you are already developing solutions in Office.

In this column you’ll see how to use Word to capture some VBA code that solves a basic problem. Then I will use the latest version of VSTO included with Visual Studio 2008 to wrap this code into a deployable Word add-in. I’ll also write some simple code for some of the tasks that the macro recorder either just can’t record or doesn’t record in an optimal way.

Book Learning

Recently, I found myself in a situation where this kind of VBA-to-VSTO conversion was perfect for the problem at hand. I was finishing my latest book, The SharePoint Shepherd’s Guide for End Users. The book is self-published, and I needed to output the manuscript from Word into a PDF that the printer could use. For that to work, there were several steps I first needed to complete to get the manuscript ready.

First, I had to assemble multiple files into one large document. With more than 140 individual files representing 13 sections and 116 tasks, munging them together wasn’t something I wanted to do by hand. Having so many individual files was great when working with Microsoft SharePoint® because it allowed for individual tracking of each task through the editorial workflow, but the sheer number made assembly a task best handled by automation.

Second, I wanted to make sure that all of the tracked changes in all of the documents had been accepted. During the editing process, I used revision marks (via the Track Changes feature in Word) to keep track of editing and other changes to the manuscript. The revision marks are supposed to all be accepted as part of the final checkout of the content, but if I missed some, the formatting of the revision marks would stand out in the final book, which wouldn’t be very professional looking.

I am going to illuminate the process of creating each of these pieces and the relative ability of the macro recorder to capture the code needed for each function. I’ll start by using the recorder to generate the basic automation code. From there I’ll take a closer look at workable but sub-optimal generated code. Finally, I’ll look at the recorder not generating code at all. I’ll convert the VBA code into VSTO code and put it into a Word add-in that I can use to assemble the final book. Just to make the process more challenging, I’ll convert the code from VBA to C# in the course of bringing it into VSTO.

Macro Basics

Figure 1** Enabling the Developer Tab in the Ribbon **

Figure 2** Record a Macro from the Developer Tab **

Figure 3** Naming the Macro **

Figure 4 AddFiles VBA Macro

Sub AddFiles() ' ' AddFiles Macro ' ' ChangeFileOpenDirectory _ "https://sharepoint.contoso.com/sites/sharepoint/" & _ "Shared%20Documents/SharePoint%20Tasks/" chúng tôi fileName:= _ "https://sharepoint.contoso.com/sites/SharePoint/" & _ "Shared%20Documents/SharePoint%20Tasks/" & _ "Task001%20-%20Create%20a%20Team%20Web%20Site.docx", _ ConfirmConversions:=False, _ ReadOnly:=False, _ AddToRecentFiles:=False, _ PasswordDocument:="", _ PasswordTemplate:="", _ Revert:=False, _ WritePasswordDocument:="", _ WritePasswordTemplate:="", _ Format:= wdOpenFormatAuto, _ XMLTransform:="" Selection.WholeStory chúng tôi chúng tôi Template:="Normal", NewTemplate:=False, DocumentType:=0 Selection.PasteAndFormat (wdPasteDefault) End Sub

Notice that there is an extraneous line that resets the next File Open directory. After that you see the Open command, and then there’s the series of commands to copy the text, create a new document, and paste the text into the new document. The code that the macro recording produced isn’t perfect, but it isn’t bad either. So I’ll take that, enhance it, and put it into VSTO.

Moving Macros to VSTO

To get started with VSTO, I first create a Word 2007 Add-in project. I open Visual Studio, start a new project, use the Word 2007 Add-in template for the project, and name the project PublishPrep. Upon successful creation of the new VSTO project, your Visual Studio 2008 environment should look similar to Figure 5.

Figure 5** New Word Add-In Project **

With the project created, I need to create a way for users to access the functionality in the add-in. For 2007 Office system applications, that means creating a Ribbon tab and buttons. First, add a new item to your project and select the Ribbon (Visual Designer) template from the Add New Item dialog. Name your new ribbon PublishPrep.

The next step is to customize the Ribbon. In this case, I’m just going to create a group that will live on the Add-ins tab instead of adding my own tab to the Ribbon. My group will contain three buttons.

Figure 6** Configuring the PublishPrep Ribbon **

Open the toolbox, scroll down to the Office Ribbon Controls group, and drag three button controls over onto the Publishing Preparation group. Note that the buttons will stack vertically by default.

Figure 7** Buttons and Group Configured for the Add-In **

Writing New Add-In Code

The first part of the code, a function called AppendFile (see Figure 8), takes a single parameter, the file name. At a quick glance the code doesn’t resemble the code the macro recorder wrote for me, but that’s mostly an illusion.

Figure 8 AppendFile

void AppendFile(string file) { if (string.IsNullOrEmpty(file)) return; Application app = Globals.ThisAddIn.Application; Document activeDoc = app.ActiveDocument; if (activeDoc == null) return; object fileObj = file; object confirmConversions = false; object readOnly = true; object addToRecentFiles = false; object passwordDocument = Missing.Value; object passwordTemplate = Missing.Value; object revert = true; object writePasswordDocument = Missing.Value; object writePasswordTemplate = Missing.Value; object format = Missing.Value; object encoding = Missing.Value; object visible = false; object openAndRepair = false; object documentDirection = Missing.Value; object noEncodingDialog = Missing.Value; object xMLTransform = Missing.Value; Document newDoc = app.Documents.Open(ref fileObj, ref confirmConversions, ref readOnly, ref addToRecentFiles, ref passwordDocument, ref passwordTemplate, ref revert, ref writePasswordDocument, ref writePasswordTemplate, ref format, ref encoding, ref visible, ref openAndRepair, ref documentDirection, ref noEncodingDialog, ref xMLTransform); app.Selection.WholeStory(); app.Selection.Copy(); activeDoc.Select(); object collapseEnd = WdCollapseDirection.wdCollapseEnd; app.Selection.Collapse(ref collapseEnd); app.Selection.Paste(); object saveChanges = WdSaveOptions.wdDoNotSaveChanges; object originalFormat = WdOpenFormat.wdOpenFormatAuto; object routeDocument = Missing.Value; newDoc.Close(ref saveChanges, ref originalFormat, ref routeDocument); object breakType = WdBreakType.wdPageBreak; app.Selection.InsertBreak(ref breakType); }

The first four lines just get the active document and perform error checking. If there’s no active document, you can’t exactly append to it, and if you don’t have a file name to append, there’s not much more you can do. The other thing that these lines are doing is getting references to the application-which is assumed in VBA-and the active document when the button method was called, something the recorded version of the macro didn’t need to do.

The next set of lines-the object variable declarations-are necessary because of the way C# makes the call to the COM components that Word exposes. I need to specify missing values, and since the values are all passed by reference, I need a variable to hold them.

The code then performs the same copy operation that the macro recorder generated. The real difference here is that my add-in code is responsible for the management of the active document, including making sure that the cursor position is set to the end of the active document.

The final block of code closes the document I opened, making sure not to save any changes. It also adds a page break after the inserted content because I want to make sure that the contents of the individual files don’t run together.

The second part of the code actually gets the list of files to assemble and calls AppendFile (see Figure 9). This isn’t code that the macro recorder captured, but it is where you see the power of VSTO because it allows you to leverage all of the .NET constructs. In this case, I’ll leverage the OpenFileDialog control, the ability to open and read from a text file, the use of generics to create a list of files, and calling another smaller method that iterates through the list.

Figure 9 VSTO Power Behind the Button

using Microsoft.Office.Interop.Word; using chúng tôi using System.Reflection;

Finally, replace the method stub created by Visual Studio with the code in Figure 9.

More Buttons

Figure 10** Enabling the Developer Tab in the Ribbon **

Sub AcceptAllChanges() ' ' AcceptAllChanges Macro ' WordBasic.AcceptAllChangesInDoc End Sub

The macro recorder recorded this action to use the WordBasic object. The WordBasic object is a holdover from before Word macros were modified to use VBA. It’s not the best way to approach the problem. It’s much better to use ActiveDocument. You can either employ the AcceptAllRevisions method or the Revision property’s AcceptAll method. I slightly prefer the Revisions.AcceptAll nomenclature, but either method works.

Here is the code necessary to accept revisions from within VSTO code:

One interesting thing to note is that the indexer here is 1-based, not zero-based as you would expect in C#.

Robert Bogue, Microsoft MVP for Microsoft Office Sharepoint Server, MCSE, and MCSA:Security, has contributed to more than 100 book projects and numerous other publishing projects. Robert blogs at chúng tôi you can reach him at Rob.Bogue@thorprojects.com. Find out more about his latest book project, The SharePoint Shepherd’s Guide for End Users, at sharepointshepherd.com.

Word Macro Examples &Amp; Vba Tutorial / 2023

This page contains:

Word VBA Tutorial PDF (Free Download)

Word VBA “Cheat Sheet” containing a list of the most commonly used Word VBA code snippets

Full Word VBA / Macro tutorial.

Searchable list of all of our Word VBA Macro Tutorials

You might also be interested in our Interactive VBA Tutorial for Excel. While some of the examples / exercises are specific to Excel VBA, much of the content is generic to all VBA and you may find it useful to learn concepts like If Statements, Loops, MessageBoxes, and more.

Download our free Microsoft Word VBA Tutorial! Or VBA Tutorials for other Office Programs!

Below you will find simple VBA code examples for working with Microsoft Word.

Select / Go To

Word VBA Macro Tutorial

This is a tutorial for using VBA with Microsoft Word. This tutorial will teach you how to write a simple Macro and interact with Documents, Ranges, Selections, and Paragraphs.

Note: If you’re brand new to Macros / VBA you might also find this article useful: How to write VBA Macros from Scratch.

VBA is the programming language used to automate Microsoft Office programs including Word, Excel, Outlook, PowerPoint, and Access.

Macros are blocks of VBA code that perform specific tasks.

This is a simple example of a Word VBA Macro. It performs the following tasks:

Opens a Word Document

Writes to Document

Closes and Saves the Word Document.

Word Macro Basics

All VBA code must be stored within procedures like this. To create a procedure in VBA type “Sub WordMacroExample” (Where “WordMacroExample” is your desired Macro name) and press ENTER. VBA will automatically add the parenthesis and End Sub.

When interacting with Microsoft Word in VBA, you will frequently reference Word “Objects”. The most common objects are:

Application Object – Microsoft Word itself

Document Object – A Word document

Range Object – A part of a Word document

Selection Object – A selected range or cursor location.

Application is the “top-level” object. All other objects in Word can be reached through it.

In addition to accessing other Word objects, there are “application-level” settings that can be applied:

This is an example of accessing the “Selection” of “Windows(1)” with in the Application:

However, the most common Word objects can be accessed directly, without typing the full hierarchy. So instead, you can (and should) just type:

ActiveDocument

Often, you will have two or more documents opened in Word and you will need specify which specific Word Document to interact with. One way to specify which document is to use ActiveDocument. For example:

…would print the ActiveDocument. The ActiveDocument is the document in Word which “has focus”

To switch the ActiveDocument, use the Activate command:

ThisDocument

Instead of using ActiveDocument to reference the active document, you can use ThisDocument to reference the document where the macro is stored. ThisDocument will never change.

Document Variables

However, for more complicated macros, it can be hard to keep track of the Active Document. It can also be frustrating to switch back and forth between documents.

Instead, you can use Document variables.

This macro will assign the ActiveDocument to a variable and then print the document using the variable:

Document Methods

To Open a Word Document:

We recommend always assigning a Document to a variable upon opening it:

To create a new Word Document:

We can instruct Word to create a new doc based on some template:

As always, it is useful and huge problem saver to assign document to variable upon creating or opening:

To save a document:

or SaveAs:

To close a Document and save changes:

or without saving changes:

This will print the active Document:

Range, Selection, Paragraphs

and are probably the most important objects in Word VBA, certainly the most used.

Range refers to some portion of document, usually, but not necessarily, text.

Selection refers to selected text (or other object like pictures) or, if nothing is selected, an insertion point.

Paragraphs represent paragraphs in document. Its less important than it sounds, because you can’t directly access paragraph text (you need to access particular paragraph range to make modifications).

Range can be any part of document, including entire document:

or it can be small as one character.

Another example, this range would refer to first word in document:

Usually, you would want to get range which refers to specific part of document and then modify it.

In the following example we will make the first word of second paragraph bold:

Set Range Text

To set the text value of a Range:

(Tip: Note the space after “Hello”. Because word object includes space after word, with just “hello” we would get “Hellonext word”)

There are hundreds of things which you can do with ranges. Just a few examples (these assume you are already made object variable oRange referring to range of interest):

Change font Display in message box number of characters in particular range Insert some text before it Add a footnote to range Copy it to clipboard

After above code, oRange would refer to text starting with fifth and ending with 50th character in document.

Selection is even more widely used than Range, because it is easier to work with Selections than Ranges, IF your macro ONLY interacts with the ActiveDocument.

First select the desired part of your document. For example select the second paragraph in active document:

Then you can use the Selection Object to type some text:

We can type some paragraphs bellow “Some text”:

Often, it’s necessary to know if some text is selected or we have just a insertion point:

When working with Selection object we want to place insertion point to particular place, and issue commands starting from this point.

Beginning of document: Beginning of current line:

The Extend parameter wdMove moves the insertion point. Instead, you could use wdExtend which will select all text between the current insertion point.

Move Selection

The most useful method for changing position of insertion point is Move. To move Selection two characters forward:

to move it backwards, use negative number for Count parameter:

Unit parameter can be wdCharacter, wdWord, wdLine, or more (use Word VBA help to see others).

To move words instead:

Selection is easier to work with (compared to ranges) because it is like a robot using Word, mimicking human user. Where Insertion point is – some action would take place. But, this means that you must take care where insertion point is! This is not easy after many steps in code. Otherwise, Word would change text in not desired place.

In the case you need some property or method not available in Selection object you can always easily obtain range associated with selection:

TIP: Using Selection is often easier than using ranges, but also it’s way slower (important when you deal with big documents)

Paragraphs

You can’t directly use Paragraphs object to change text:

Above wouldn’t work (actually it will throw an error). You need to first obtain range associated with particular paragraph:

But you can directly change its style:

or change its paragraph level formatting:

or maybe you want to keep this paragraph on the same line with next paragraph:

Make paragraph centered:

It is VERY useful to assign a particular paragraph to object variable. If we assign particular paragraph to variable we don’t have to worry if the first paragraph becomes the second because we inserted one paragraph before it:

Here is an example where we insert a paragraph above the first paragraph, but we can still reference the old first paragraph because it was assigned to a variable:

Paragraph object is very frequently used in loops:

Word VBA Tutorial Conclusion

This tutorial covered the basics of Word VBA. If you’re new to VBA, you should also review our general VBA Tutorial to learn more about Variables, Loops, MessageBoxes, Settings, Conditional Logic and much more.

Word Macro Examples

PowerPoint VBA FAQs

What is a Word Macro?

A Macro is a general term that refers to a set of programming instructions that automates tasks. Word Macros automate tasks in Word using the VBA programming language.

Does word have VBA?

How do I use VBA in Word?

Insert And Run Vba Macros In Excel / 2023

Most people like me and you are not real Microsoft Office gurus. So, we may not know all specificities of calling this or that option, and we cannot tell the difference between VBA execution speed in Excel 2019, 2016, 2013 and 2010. We use Excel as a tool for processing our applied data.

Suppose you need to change your data in some way. You googled a lot and found a VBA macro that solves your task. However, your knowledge of VBA leaves much to be desired. Feel free to study this step-by-step guide to be able to use the code you found:

Insert VBA code to Excel Workbook

For this example, we are going to use a VBA macro to remove line breaks from the current worksheet.

Open your workbook in Excel.

Press

Alt + F11

to open Visual Basic Editor (VBE).

Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor (“Module1” window).

Tip: Speed up macro execution

If the code of your VBA macro does not contain the following lines in the beginning:

Application.ScreenUpdating = False Application.Calculation = xlCalculationManual

Then add the following lines to get your macro to work faster (see the screenshots above):

To the very beginning of the code, after all code lines that start with Dim (if there are no “Dim” lines, then add them right after the Sub line): Application.ScreenUpdating = False Application.Calculation = xlCalculationManual

To the very of the code, before End Sub: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic

These lines, as their names suggest, turn off screen refresh and recalculating the workbook’s formulas before running the macro.

After the code is executed, everything is turned back on. As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells’ contents).

Save your workbook as “Excel macro-enabled workbook“.

Press

Alt + Q

to close the Editor window and switch back to your workbook.

How to run VBA macros in Excel

When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the “Macro” dialog.

You may also be interested in

How To Remove Macros From An Excel Workbook (3 Easy Ways) / 2023

Using VBA Macros in Excel can be a huge time saver. You can automate a lot of repetitive tasks and create new functions and functionalities in Excel with simple VBA macro codes.

But in some cases, you may want to remove all the macros from an Excel workbook (or delete specific macros only).

This may be the case when you get a workbook from someone else and you want to make it macro-free, or when you’re sending a file with macros to someone and the receipt doesn’t need these in the workbook.

In this tutorial, I will show you a couple of really simple ways to remove macros from a workbook in Microsoft Excel.

So let’s get started!

Remove All Macros by Saving the File in XLSX format

If you want to get rid of all the macros at once, the easiest way to do this would be to save the existing workbook with the XLSX format.

By design, you can not have any VBA macro code in the XLSX file format. In case you do, it would be removed automatically while saving the Excel file.

With Excel, you can only have the macros in the .XLSM, .XLSB, and the older .XLS formats. When you save the workbook in any other format, the macros are immediately lost.

Suppose you have a file called chúng tôi (with macros), below are the steps to remove all the macros from this file:

In the Save As dialogue box, enter the name of the file with which you want to save it. You can also keep the existing name if you want

Select the Excel Workbook (*.xlsx) option

That’s it! Your file is now macro-free.

This method is great as it removes all the macros from the current Excel workbook in one go. However, if you want to remove some macros and delete some, this method will not work for you (see the one using the Macro dialog box for this).

Another good thing about this method is that you still have a copy of the original file that has all the macros (in case you need it in the future).

Remove Specific Macros from the Macro dialog box

While the previous method would delete all the macros. this one allows you to choose the ones that you want to be removed.

And in case you want to delete all the macros, you can do that as well.

Suppose you have a file called chúng tôi that has some macros.

Below are the steps to delete a macro from this workbook:

In the ‘Macros in’ drop-down, make sure ‘This Workbook’ is selected.

Select the macro name that you want to delete from the macro list

If you want to remove multiple (or all) macros, repeat steps 4 and 5.

Remove the Module that has the Macro

Another way to remove macros is to go to the Visual Basic Editor and remove macros from there.

This method gives you the most control as you can access all the macros (be it in the module or objects or personal macro workbook).

Below are the steps to delete a macro from the Visual Basic Editor:

In the code window that opens, delete the macros you want to remove. If you want to remove all, just select everything and hit the delete key.

So these are three ways you can use to remove macros from a Microsoft Excel workbook.

I hope you found this tutorial useful!

Other Excel tutorials you may like:

Bạn đang xem bài viết Office Space: From Vba Macro To Word Add / 2023 trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!