Bạn đang xem bài viết Office Space: From Vba Macro To Word Add được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. 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.
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 SubNotice 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 SubThe 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.
How To Add Macro Code To Excel Workbook
How to copy Excel macro VBA code to your workbook, from website or sample file. Different types of code, where to paste it. Step-by-step videos, written steps.
Copy Excel VBA Code to a Regular Module
To see the steps for pasting a macro into a workbook, and running the macro, please watch this short video tutorial. The written instructions are below the video.
Copy Excel VBA Code to a Regular Module
Instead of starting from scratch, if you need an Excel macro, you can often find sample code at reputable sites on the internet. To copy that code, and add it to one of your workbooks, follow these steps:
Copy the sample code that you want to use
Open the workbook in which you want to add the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
To run the code:
Copy Excel VBA Code to a Worksheet Module
Worksheet event code is stored on a worksheet module. To add worksheet event code to your worksheet, do the following:
Copy the code that you want to use
Select the worksheet in which you the code to run
Copy Excel VBA Code to a Workbook Module
Another type of code is Workbook Event code, which should be added to the workbook code module:
Copy the code that you want to use
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Copy Excel VBA Code From a Different Workbook
To see the steps for copying a macro from one workbook to another, in any version of Excel, please watch this short video tutorial. The written instructions are below the video.
Copy Excel VBA Code From a Different Workbook
You may find code in a sample workbook online, and decide to add it to one of your workbooks. You can copy all the code in a module by doing the following:
Open both workbooks
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and the workbook with the code that you want to copy. The screenshot at the right, the code is in chúng tôi and will be copied to MyForm.xlsm
Release the mouse button, and a copy of the module will appear in the workbook.
To run the code:
Allow Macros to Run in Your Workbook
To use macros in Excel, you might need to enable them when the file opens. If you are using macros for the first time on your current computer, you might also need to adjust the macro security settings.
Follow the instructions below, to make these changes.
Enable Macros When Opening the File
When you open a workbook that contains macros, you might see a security warning, at the top of the worksheet, above the Formula Bar.
Check Your Macro Security Settings
If you haven’t run macros before, you might need to change your macro security level. (You may have to clear this with your IT department.)
If you changed the setting, close the workbook, and then reopen it
Run an Excel Macro
After you copy a macro to a regular module, follow the steps below, to run the macro. If the macro does not run, check your macro settings.
To run an Excel macro:
Copy the macro code to a regular code module in your file.
Create a Worksheet Event Macro
To see the steps for creating an Excel Worksheet Change Event macro, watch this short video.
There are written steps on the Contextures Blog, and you can download the sample file used in this video.
Modify Copied Excel VBA Code
If you copy VBA code into your Excel file, you might need to make changes to the object names, or other settings, so that the code works correctly in your file. Here are three things to check, before you try to run the code in your file:
Check the Sheet Names and Ranges
If there are sheet names or range references in the code, you can modify them, to match your workbook.
In the code, look for references to “Worksheets” to “Sheets”, and change those to the sheet names in your workbook.
Also look for “Range” references, such as Range(“A1:G100”), and adjust those to match the location of your data.
These references might be at the top of the procedure, in a Set statement:
Set ws = Worksheets("SalesData")or elsewhere in the code.
If you run the code without modifying the reference, you might see an error message: Run-time error ‘9’: Subscript out of range
Change the sheet name in the line that was highlighted, save the changes, and try the code again.
Add and Name Objects
If the code refers to objects on the worksheet, be sure to add those objects in your workbook, and use the correct object name in the code.
For example, in the code for the Data Validation Combo Box, you’ll need to add a combo box to the worksheet, and name it as TempCombo. Or, if your combo box has a different name, change the code references to match.
Specify the Target Columns or Rows
Some code is designed to run when a cell in a specific row or column is changed. For example, in the sample code shown below, there is a red dot on the line that says column 3 is the only one where the change will occur.
NOTE: In all of these examples, you could use Row instead of Column, to limit the target to specific rows.
A) In your workbook, if you want the code to run when a cell in column E is changed, you could change the 3 to a 5.
If Target.Column = 5 ThenB) Or, add more columns in the code. For example:
If Target.Column = 3 _ Or Target.Column = 5 _ Or Target.Column = 6 ThenC) If you don’t want to limit the code to a specific column, you could delete the two rows (If…End If) that are marked with red circles. In that case, the code will run for a change in every column.
Get the Sample File
To see examples of workbook modules, worksheet modules and regular code modules, download the Add Code to a Workbook sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the macros.
Related Tutorials
Create an Excel UserForm Macro Troubleshooting Tips UserForm with ComboBoxes
Edit Your Recorded Macro
Last updated: April 14, 2021 1:25 PM
Excel Vba: Giới Thiệu Về Macros Trong Excel
Macro là một chuỗi các lệnh mà bạn có thể sử dụng để tự động hóa các tác vụ được sử dụng thường xuyên để tiết kiệm thời gian gõ phím và thao tác chuột. Một Macro có thể được tạo bằng cách sử dụng Visual Basic for Applications (VBA) và được viết bởi người sử dụng.
VBA (Visual Basic for Applications) là 1 ngôn ngữ lập trình được tích hợp trong ứng dụng văn phòng như Excel, Word, PowerPoint, … Vì vậy, tất cả các chương trình mà bạn thực hiện trong Excel hoặc trong ứng dụng văn phòng khác đã được thực hiện trong VBA.
Về mặt kỹ thuật, VBA là một ngôn ngữ lập trình hướng sự kiện của Microsoft. VBA cũng được gọi là một ngôn ngữ lập trình mở rộng được tạo thành từ một tập các lệnh cốt lõi và được mở rộng trên cơ sở mỗi ứng dụng để có thể làm việc trực tiếp với các đối tượng trong ứng dụng đó.
Visual Basic for Applications (VBA) trong Excel là một ngôn ngữ lập trình nhẹ nhàng, mạnh mẽ cho phép bạn viết các hàm hoặc lệnh của riêng mình trong một bảng tính Excel.
Sử dụng Excel VBA bạn có thể làm hầu như bất kỳ tác vụ nào tương tự trong Excel.
Các đối tượng trong Excel VBA
Chỉ định tên Workbook thao tác là: “Book1.xlsx”
Application.Workbooks("Book1.xlsx")Worksheets là một thành viên của đối tượng Workbook. Thuộc tính Worksheets() trả về một tập hợp tất cả các sheet của đối tượng Workbook đang được active. Chỉ định tên Worksheet thao tác là: “Sheet1”
Application.Workbooks("Book1.xlsx").Worksheets("Sheet1")Cuối cùng để trỏ đến Range “A1” của sheet “Sheet1” và gán bằng 100 như sau:
Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 100Các bước để viết code và run code VBA trong Excel: 1. Bật Developter tab. 2. Save As Excel file thành Excel Macro File (từ excel 2007 trở lên). 3. Viết code VBA. 4. Run code VBA.
1.Bật Developer Tab
Để mở Developter tab thực theo các bước sau:
Mở một file excel.
Bạn có thể thấy Developer tab bên cạnh View tab như sau:
2. Save As Excel file thành Excel Macro File
Để tạo một macro từ excel, bạn cần phải Save As file excel thành tập tin với phần mở rộng có định dạng .xlsm (excel 2007 trở lên) hoặc .xls (excel 2003)
Note: Để tạo một macro từ excel, nếu bạn đang dùng excel 2003 (.xls) thì bạn không phải làm việc này. Nếu bạn dùng excel 2007 (.xlsx) trở lên thì bạn làm theo các bước sau.
Đến đây là đã tạo được một macro file.
3. Viết code VBA
Mở Visual Basic Editor: chọn Vusual Basic hoặc bấm tổ hợp phím Alt + F11.
4. Run code VBA
Insert button
Tại Developer tab, bạn có thể insert một button như sau:
Gán thủ tục macro cho button
Chọn Desing Mode.
All Rights Reserved
Cách Lưu Macro Trong Excel, Lưu Vba
Khi lưu Macro trong Excel, lưu vba Excel, mã Macro mà bạn tạo chỉ hoạt động trên bảng tính đó sẽ bị mất đi. Tuy nhiên nếu muốn sử dụng Macro trên các bảng tính khác thì sao?
Khi lưu Macro trong Excel, VBA trong Excel, để Macro có sẵn mỗi khi mở Excel, bạn có thể tạo Macro trong một bảng tính có tên Personal.xlsb. chúng tôi là bảng tính ẩn được lưu trữ trên máy tính của bạn, mỗi khi bạn mở Excel sẽ mở cả bảng tính Personal.xlsb.
Tiếp theo tiến hành tạo macro.
Bước 3: Tại ô A1, nhập ” Some text “, sau đó nhấn Enter.
Lưu ý: tên viết liền không dấu cách.
Đây là bước duy nhất trong macro
Bước 9: Đóng bất kỳ bảng tính nào mà bạn đã mở, sau đó thoát khỏi Excel.
Lúc này trên màn hình hiển thị thông báo nói rằng lưu lại các thay đổi mà bạn thực hiện trên Personal Macro Workbook.
Macro mà bạn vừa tạo sẽ khả dụng trong những lần tiếp theo khi bạn mở Excel. Để xem macro:
Trong ví dụ này Macro có tên gọi là PERSONAL!.BoldMe .
Di chuyển Macro từ máy tính này sang máy tính khác
Giả sử bạn có một máy tính mới và bạn muốn di chuyển hoặc sao chép Macro sang máy tính đó hoặc bạn muốn chia sẻ Macro cho một người dùng khác để chạy Macro. Việc chia sẻ chúng tôi giữa các máy tính là không thể, nhưng bạn có thể lưu Macro trong Excel vào thư mục XLSTART hoặc sao chép một số hoặc tất cả Macro vào file chúng tôi trên máy tính khác và khí đó, người dùng chỉ cần mở VBA và chạy Macro là được.
Nếu muốn chia sẻ một hoặc một vài Macro với người dùng khác, bạn cũng có thể gửi cho họ một email đính kèm workbook có chứa macro. Ngoài ra bạn cũng có thể thiết lập workbook có sẵn trên hệ thống mạng chia sẻ hoặc từ thư viện SharePoint Services.
Bỏ ẩn personal workbook của bạn
Bất kỳ Macro nào mà bạn lưu trên personal workbook đều có thể chỉnh sửa được, chỉ cần bỏ ẩn personal workbook. Khi mở Excel, personal workbook cũng được mở nhưng bạn không thể nhìn thấy vì nó bị ẩn.
Bước 5: Và bạn có thể chỉnh sửa chúng tôi trong Visual Basic Editor (VBE).
Bất cứ khi nào tạo một Macro mới và lưu Macro trong Excel thông qua personal workbook của bạn hoặc cập nhật bất kỳ Macro nào mà personal workbook chứa, bạn sẽ được thông báo lưu lại personal workbook.
Cập nhật thông tin chi tiết về Office Space: From Vba Macro To Word Add trên website Hoisinhvienqnam.edu.vn. 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. Chúc bạn một ngày tốt lành!