Organize With Office 365
In addition to objects such as DoCmd, Err, Debug, as well as objects found in the ADODB library, VBA provides the Microsoft Office Object model for use in the suite of applications found in Microsoft Office. Most of the Microsoft Office Objects can be used across the Microsoft Office suite of applications such as Microsoft Word, Microsoft Excel, and Microsoft Access. Some office objects, however, are application specific. To work with the examples in this chapter, ensure that your Access program has a reference set to the Microsoft Office Object library. Setting a reference allows you to work with other application objects in your code. In older versions of Microsoft Access, the Microsoft Office Object references may not be set. You know if a reference is not set as soon as you try to access a property, constant, or method of an object that requires a reference. If references to Microsoft Office Objects are not set, VBA notifies you with an error message asking if you want Access to...
To personally use Microsoft Office XP web components, you must have the Microsoft Office XP web components installed and an appropriate Office XP license. If your applications utilize Office XP web components, your users do not need to have Office XP installed on their computers, but they do need to have a license for it. You can get an Office XP site license that permits intranet distribution and configure the components to allow the users to download them from your corporate intranet. If used on web pages, web components require Internet Explorer 4.01 or later. For best functionality, use Internet Explorer 5 or later. To use web components in other applications, make a reference to the Microsoft Office XP Web Components Library (OWC10). This library is stored in C Pro-gram Files Common Files Microsoft Shared Web Components 10 OWC-10.DLL. After setting up a reference to the OWC10 Library, you can open the Object Browser and take a look at numerous properties,...
The next several sections of this chapter demonstrate event procedures for PivotTable and PivotChart views. To successfully work with these events, follow the steps below to add a reference to the Microsoft Office Web Components 11 Object Library 4. Click the OWC11.DLL file and then click Open. A reference to Microsoft Office Web Components 11.0 should appear in the Available References list. After you add a reference to the Microsoft Office Web Components Object Library, you can use the Object Browser to view the properties, methods, events, and constants for each class of a web component. To see the available classes of a web component, select OWC11 in the Project Library box in the Object Browser.
Let's look at an example of creating custom command bars for your application using the Microsoft Office 11.0 Object Library. The CommandBars property of the Application object allows you to access the CommandBars collection. The CommandBars collection enables you to manage and create toolbars, menu bars, and shortcut menus. 2. Set a reference to Microsoft Office 11.0 Object Library in the References dialog box, if one is not already set. From the Visual Basic Editor, select Tools C References and scroll down the list. Select Microsoft Office 11.0 Object Library, as shown in Figure 10.1. Click the OK button to add the reference.
Currently, most copies of Excel are sold as part of Microsoft Office a suite of products that includes a variety of other programs. (The exact programs that you get depend on which version of Office you buy.) Obviously, it helps if the programs can communicate well with each other. Microsoft is at the forefront of this trend. All the Office products have extremely similar user interfaces, and all support VBA.
A VBA Developer's Guide to Microsoft Office Development Using Visual Studio 2005 Tools for Office 1. Microsoft Visual studio. 2. Microsoft Office. I. Stubbs, Paul R., 1969- II. Title. III. Title Visual Studio Tools for Office for mere mortals. TK5105.8885.M57M375 2006 005.5 dc22
Highlights the new features in Microsoft Office Access 2007 including new wizards and GUI (graphical user interface) elements that previously required VBA code, as well as new VBA features. The book also discusses how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error handling techniques, and advanced functions such as creating classes and using APIs. Key new objects such as using Macros and the Ribbon are explored, too, as are forms and reports, the two most powerful tools for working with and displaying data. Working with other applications is covered extensively both in a general nature and for working specifically with Microsoft Office applications, Windows SharePoint Services, and SQL Server. Of course, this book wouldn't be complete without discussing security issues and the Developer Extensions.
Finally replace the code you previously added to the This Application class with the code in Listing B
To handle other types of Inspectors, you could create additional classes similar to the ContactCommandBar class that derive from Command-BarBase, or you could create a generic class that handles all Inspectors. Note, however, that this code is not enabled for e-mail items when Word is selected as the editor for e-mail. You can change these settings by deselecting the Use Microsoft Office Word 2003 to Edit E-mail Messages check box and the Use Microsoft Office Word 2003 to Read Rich Text E-mail Messages check box, as shown in Figure B.2. Adding support for Word as the editor is beyond the scope of this example, but you could extend the basic framework of the example to support WordMail. Figure B.2. Clearing the Use Microsoft Office Word 2003 check boxes
In fact, Microsoft's Visual Basic is a more appropriate programming environment than Microsoft Office for creating applications that involve complex forms, since it was designed specifically for that purpose. And Visual Basic allows you to access any of the object models in the Microsoft Office suite, just as Excel does.
VBA is a programming language for manipulating objects in Microsoft Office application programs. This book describes using VBA to manipulate objects and their properties in Access. Before you can write code to manipulate objects programmatically, you must first understand the objects themselves.
If you wish to run your Active Document in another container application such as Microsoft Office Binder, select the Start program option and choose that application's EXE file in the box just below the option, as illustrated in Figure 14.8. Settings on the Debugging tab of the Project, Options menu dialog to allow you to test your Active Document project with a Microsoft Office Binder container. Settings on the Debugging tab of the Project, Options menu dialog to allow you to test your Active Document project with a Microsoft Office Binder container.
Introduced in Excel 5, the PivotTable Wizard continues to serve millions of Microsoft Office application users as a powerful tool for organizing and presenting information from various sources. If you are not familiar with this feature, now is the time to get your feet wet. Using PivotTables, you can analyze your data from multiple perspectives. PivotTables make it possible to drag headings around a table to rearrange them so that your data is displayed dynamically any way you (or your users) want it. If you need to focus on understanding your data, rather than on organizing it, you need to get to know PivotTables.
You write Visual Basic Applications, or VBA code, required to create complex macros, using the Visual Basic Editor (VBE), acessible via all Microsoft Office applications, including Excel. Arranged in a series of windows, which you can move around with your mouse to obtain the desired development layout, the VBE contains
Open the Acc2003_Chap24.mdb file from the book's downloadable files or, if you'd like to start from scratch, create a new Microsoft Office Access database. This database should contain tables, queries, forms, and reports from the sample Northwind database. You can import these objects by choosing File Get External Data Import or you can create a copy of the Northwind database to be used for this chapter's hands-on exercises. Me.Picture C Program Files & _ Microsoft Office Office11 & _ Bitmaps Styles Stone.bmp
So far you should feel pretty comfortable using ADO in most of your Microsoft Office Access programming endeavors. What's more, using the knowledge acquired during the last few chapters, you can switch to any other Office application (Excel, Word, PowerPoint, or Outlook) and start programming. Because you already know the ADO methods of accessing databases and manipulating records, all you need to learn is the object model that the specific application is using. Learning a new type library is not very hard. Recall that VBA offers the Object Browser that lists all the application's objects, properties, methods, and intrinsic constants that you may need for writing code. However, if you'd like to accomplish more with ADO, this chapter will introduce you to a couple of more advanced ADO features that will set you apart from beginning programmers. You will learn about fabricating, persisting, disconnecting, cloning, and shaping recordsets. You will also learn how to process data...
With frequent use over a period of time, the performance of your database may deteriorate. When objects are deleted from a database but the space isn't reclaimed, fragmentation may occur. To improve the database performance and to reduce the database file size, you can compact or repair Microsoft Office Access databases by using the ADO extension library, Microsoft Jet and Replication Objects (JRO). In order to work with this object library, choose Tools References in the Visual Basic application window and select Microsoft Jet and Replication Objects 2.5 Library.
Developing a property page is one way to add a user-interface to your Outlook COM Add-In. The other (and perhaps more common) way to provide a user interface is to modify the command bars for the Outlook Explorer and Inspector windows. If you want to modify command bars, you must be certain to set a reference to the Microsoft Office 10.0 Object Library with the Visual Basic Project References command. The Outlook COM Add-In template project has set this reference for you. If you've reviewed the material in Chapter 11, Using Visual Basic, VBA, or VBScript with Outlook, you'll know that the OnAction property is used to set the procedure that runs when a user clicks a CommandBarButton object. If you want a CommandBarButton to respond to a Click event on the Explorer toolbar, for example, you must code a call to the event procedure for a COM Add-in differently than you would in Outlook VBA.
The applications that support Automation are called Automation servers or Automation objects. The applications that can manipulate a server's objects are referred to as Automation controllers. Some applications can be only a server or a controller, and others can act in both of these roles. All Microsoft Office 2000 2002 applications can act as Automation servers and controllers. The Automation controllers can be all sorts of ActiveX objects installed on your computer. You will learn about these objects in the next chapter.
You can design complete applications within Microsoft Access and never have the need for another Microsoft Office program. After all, you can use Access forms to enter data, Access reports to print data, and the SendObject method to send Access information via e-mail. However, it's also possible that you'll want to utilize some of the other Office programs to enhance your applications. For example, you might want to use Outlook to generate a custom form with information from an Access table. If you allow users to export some data to Excel, they can further manipulate the data without causing any unwanted interaction with your application. Exporting information to Microsoft Word gives users the ability to add their own text, perform mail merges, and e-mail information to others in an easy-to-use format.
Select Save As from the File menu in Excel. In the Save as Type field, scroll all the way to the bottom and select Microsoft Office Excel Add-In (*.xla). As shown in Figure 25.2, the file name changes from Something.xls to Something.xla. Also note that the save location automatically changes to an AddIns folder. This folder location varies by operating system, but it will be something along the lines of C Documents and Settings Customer Application Data Microsoft AddIns. It is also confusing that, after saving the XLS file as an XLA type, the unsaved XLS file remains open. It is not necessary to keep an XLS version of the file, because it is easy to change an XLA back to an XLS for editing.
The main purpose of the .msi is to copy the files, the assemblies, and the manifest to the location where the add-in is deployed. You must take additional steps to verify that the computer in which the add-in is deployed has Microsoft Office 2003 installed. You must also ensure that security policy is set up correctly and verify that the VSTO runtime or Office PIAs are installed on the computer. In Chapter 11 you'll learn more about deploying an Outlook solution.
VBA is able to control objects in Access (and other programs in Microsoft Office) because of Automation (with a capital A) technology. The idea behind Automation is this A program, database, document, or some special capability exposes (makes available) its objects through an object library. The object library contains an organized set of names that VBA can refer to when it wants to manipulate some object.
Excel's first programming language, Excel 4 Macro Language (XLM) was introduced with version 4 of Excel. It was a rather cumbersome language, but it did provide most of the capabilities of a programming language, such as looping, branching and so on. This first programming language was quickly superseded by Excel's current programming language, Visual Basic for Applications, introduced with version 5 of Excel. Visual Basic for Applications, or VBA, is a dialect of Microsoft's Visual Basic programming language, a dialect that has keywords to allow the programmer to work with Excel's workbooks, worksheets, cells, charts, etc. At the same time, Microsoft introduced a version of Visual Basic for Word it was called WordBasic and had keywords for characters, paragraphs, line breaks, etc. But even at the beginning, Microsoft's stated intention was to have one version of Visual Basic that could work with all its applications Excel, Word, Access and PowerPoint. Each version of Microsoft Office...
For many Office versions, it has been possible to export Access data to Word documents from the Access toolbar. The name of the control and toolbar location have changed over Office versions in Access 2003 it was the OfficeLinks drop-down control on the Database toolbar, offering options to Merge to Word (Mail Merge), Publish to Word (RTF), or Analyze with Excel (XLS). In Access 2007, on the new Ribbon that replaces the old toolbars and menus, the External Data tab (shown in Figure 6.1) has an Export group with a variety of export options, including Excel, SharePoint, Word (RTF), Text File, and More. On the More drop-down menu, there are a number of export selections, including Merge It with Microsoft Office Word. 3f irtiior disabled) according to the type of object selected in the Object Bar, and whether the object is open or closed. With a form open, for example, you will see the Access Database, XML File, and HTML Document selections (these selections are enabled) and a disabled...
The two most well-known Document container applications on the market today are Microsoft Office Binder and Internet Explorer (version 3.0 and above). However Microsoft's 97 Office Suite provides examples of important end-user applications that can serve as Active Document applications You can already open a Word or Excel document in Internet Explorer without having to go through the Word or Excel programs. (Of course Word and Excel still need to be installed on the user's workstation for this to work )
Microsoft and in particular the Access program management went all out when they created and offered the Access Developer Extensions (ADE). This impressive set of tools offers an amazing set of cost-effective and stress-reducing features. Invest5 minutes to breeze through this intro and you will certainly concur. As mentioned earlier, the ADE is part of Visual Studio Tools for the Microsoft Office System. So, when you get the ADE, you automatically have additional resources that will enable you to expand your development horizons even further.
2002 were prompted to convert an Access 97 database to Access 2000 format before they could use the database. This often caused problems in corporate installations where often, multiple versions of the Microsoft Office software suite were installed on different computers or in different departments. Access
Interestingly enough, you can perform targeted searches with Google that work far better than vendor search engines in many cases. Don't bother with the standard search in this case use the advanced search at en. Now, here's the special search technique to remember. You enter your keywords as normal in the Find Results area. However, you filter those results by adding to the Domain field the Microsoft domain you want to search. For example, if you want to search the Microsoft Office content, type office.microsoft.com in the Domain field. Likewise, if you want to find technical information, type msdn.microsoft.com or msdn2.microsoft.com in the Domain field. Don't forget to check the blogs in the blogs.msdn.com domain. Lest you think that the blogs aren't helpful, I found over 3,100 blog entries related to VBA while writing this book.
Though VBA supports the look and feel of Visual Basic, it is not Visual Basic. A main difference being that Visual Basic allows for creation of executable programs, whereas VBA does not. Moreover, VBA for Access is specifically designed for Microsoft Access. Meaning, it has knowledge of and support for the Microsoft Access object model. The concept of an object model is different for each Microsoft Office application. For example, both Microsoft Excel and Microsoft Word support VBA, but each has its own object model.
Another item to consider is that when converting an Access 2000 file with DAPs, the pages are not automatically converted to the Microsoft Office 2002 Web Components format. This is the format shared by Access 2002 and 2003. However, when a DAP is opened in Design view, Access will convert it to the most recent version of Web Components and make a backup of the original page.
If you've ever deployed an application and then had trouble with missing references, you may want to (that is, really ought to) search for them now. This will check for references that might not be included with a Microsoft Office 2003 installation. Click Find potential missing VBA references to do this.
NOTE Microsoft has a newer software architecture called .NET. .NET is a totally new architecture that is not directly compatible with COM technologies (though you can have the two technologies co-exist by using a translation layer called an interop assembly). As Microsoft Office is COM-based software, the topics in this chapter stick to the COM side of the fence.
When you present dynamic data in web pages, you can enhance the comprehension of the data by the user by providing a nice chart. Although there are many ways to generate charts in ASP, in this section we will focus on using a tool that you are already familiar with. Simply put, you will use the Microsoft Excel Chart Wizard to create a chart. A word of caution Using Chart Wizard requires that you have a copy of Microsoft Office installed on your web server. Also, keep in mind that with this technique, Excel needs to be loaded into memory therefore, using this approach for a high-volume web site is not recommended.
Databases, 118 groups, 118 users, 118 Microsoft Office Access Class Objects, 29 Microsoft Office, 445 Microsoft ActiveX data Objects 2.7, 399 Microsoft DAO 3.6, 399 Microsoft Excel and Word, 399 Microsoft Office 11, 399 Microsoft Scripting Runtime, 399 Microsoft SQLDMO, 399 object list box, 98 object oriented programming encapsulation, 384 inheritance, 384 polymorphism, 384 object parameters, 409 object properties in Access, 74 object-oriented programming (OOP), 73
The third solution to creating an ACCDE file is to programmatically simulate a button click to the Make ACCDE menu option in the Access UI. Code can be created to automate the Access UI to give you the capability to programmatically open the Save As dialog box for an ACCDB file currently opened exclusively in an instance of Access. This functionality is desirable when the database to make the ACCDE is already known and the user just wants to select the output ACCDE file location. To implement this in VBA, the Access.Application object exposes the CommandBars collection, which enables you to work directly with the menus available in Access UI, including the old menus that are hidden in Access 2007 by default. Note that using the CommandBar and CommandBarPopup objects in code requires a VBA Reference be set to the Microsoft Office 12 Object Library in Access 2007. The following is a short example of creating a Make ACCDE function by simulating a click to the Make ACCDE button on the...
Still, this code seems a little inflexible. Ideally, the user should be able to specify the name and location of the new Excel workbook. Adding a reference to the Microsoft Office 12.0 Object Library enables you to leverage the FileDialog object in your VBA code so that you can reuse four common dialog boxes built into Office Open File, Save As, File Picker, and Folder Picker. The Save As dialog box would be perfect, except for one thing It does not allow the dialog box file type filters to be set. So, instead, using the File Picker dialog box and switching the text of the Title and Button properties to Save As will serve the purpose. The following code implements this scenario
In most cases, a VBA class module is identical to a VB class module code from VBA class modules can be imported into VB, and vice versa. The sole exception is custom-defined events, which missed the release of VBA5 in Microsoft Office 97. What you can do with a class module, though, is different in VBA and VB. You can't compile a VBA class module into a standalone DLL or EXE for this you need the retail version of VB.
A new and quite powerful object in Office XP is FileDialog. This object allows you to display the File Open and File Save As dialog boxes from your VBA procedures. Because the FileDialog object is a part of the Microsoft Office 10.0 Object Library, it is available to all Office XP Applications. Programmers in all previous versions of Excel have used two special methods for displaying File Open and File Save As dialog boxes. These methods (GetOpenFilename and GetSaveAsFilename) are explained later in this section. The constants that the FileDialog object uses are listed in the table below. The mso prefix denotes that the constant is a part of the Microsoft Office
Access 2007 Microsoft Office 2007 Access database engine Object Library ' 2. Access 2003 and older Microsoft DAO 3.6 Object library ' The application name can be used for tracing and ' troubleshooting the source of problems on the server. ' This can be anything, but usually the more specific the better. Const strAppName Microsoft Office Access 2007 ODBC DSN TestDSN APP Microsoft Office Access 2007 & _
Many Access developers are independent consultants or work for small companies and don't have the resources or need for a SharePoint server. Of course, there are more and more options and price points for purchasing a SharePoint space from a hosting company. But before investing elsewhere, it is worth checking out the services offered through Microsoft Office Live, a hosted SharePoint Service through which you can create and test applications and solutions. As with many Microsoft services, there are a range of plans from free to full-featured versions.
Visual Basic for Applications combined with Microsoft Excel is probably the most powerful tool available to you. This tool is sitting on the desktops of 400 million users of Microsoft Office and most have never figured out how to harness the power of VBA in Excel. Using VBA, you can speed the production of any task in Excel. If you regularly use Excel to produce a series of monthly charts, you can have VBA do the same task for you in a matter of seconds.
Another cutting-edge feature added to Microsoft Office Access 2007 is Publish to SharePoint. It enables you to move the physical Access database file into a Document Library on the SharePoint site. Once the database has been published, you can share the Access solution with other users of the site. The Publish to SharePoint feature enables you to better collaborate with your team, using SharePoint as the platform for distribution.
Access 2003 was the first release of Access to support any SharePoint integration. This SharePoint support was provided by the owssupp.dll, which is installed and registered on the default installation of Microsoft Office System 2003. Once this DLL was installed, the Access Web Datasheet could be used from within Internet Explorer to edit SharePoint lists. Importing from or linking to a SharePoint List could be completed by using the Access Web Datasheet Task Pane options. The same is true with Access 2007. By default, the owssupp.dll is installed in the Office program files directory when Microsoft Office 2007 Professional is installed. However, if desired, you can choose not to install the owssupp.dll component during the installation process by selecting the Not Installed option in Office 2007 installer O Office Tools node O Windows SharePoint Services components C Microsoft Office Access Web Datasheet Component.
To implement this in VBA, the Access.Application object exposes the CommandBars collection so you can work directly with all of the menus available in Access UI. Using the CommandBar, CommandBarPopup, and CommandBarButton objects in code requires a VBA Reference be set to the Microsoft Office 12 Object Library in Access 2007. Here's a brief example of creating an Encode Database function by simulating a click to the Encode Decode Database button in the Access menu bar
ProgramFiles Microsoft Office Office12 MSAccess.exe C Sample.accdb runtime Notice the ProgramFiles environment variable in the path to the msaccess.exe executable program file. The developer may not always know where Access has been installed on the machine, but a good bet is in the Microsoft Office directory in the machine's Program Files directory, which is where Office programs are installed by default. However, during the installation of Access, the user has the capability to choose almost any directory to install Access to, so the ProgramFiles environment variable may not always work. Unfortunately there is no environment variable that guarantees the path of MSAccess.exe. When that's the case, the user may need to manually update the shortcut's path to MSAccess.exe to make it work correctly.
Microsoft Office Access 2007 features a brand new database engine the Access Connectivity Engine (ACE) also called the Access database engine. If you've used previous versions of Access, you are probably very familiar with ACE's predecessor, the Jet database engine. ACE is a privatized version of the Jet database engine with a number of feature enhancements. Using ACE, Access 2007 supports creating the following file formats ACCDB (Access 2007 file format), MDB (Access 2000 and 2002-2003 file formats), ADP (Access Data Project), MDE (Access Complied MDB database), ACCDE (Access Complied ACCDB database), MDA (Access MDB Add-in), ACCDA (Access ACCDB Add-in), and ACCDC (Access Signed CAB file). This section discusses some of the differences between the ACCDB MDB and ADP file formats.
The regional settings are changed using the Regional Settings applet (Regional Options in Windows 2000) in Windows Control Panel, and the Office 2007 language is changed using the Microsoft Office 2007 Language Settings program. Unfortunately, the only way to change the Windows language is to install a new version from scratch.
Just today, I needed to write a small utility that would automatically create a Microsoft Office Excel OLAP Query File (*.oqy). A query file is a file that Excel uses to store information related to the details associated with a connection to a Microsoft Analysis Services OLAP database. Anyway, this utility is handy because it eliminates the need to physically create the file for users or instruct them how to create it themselves.
The FileSearch object is a member of the Microsoft Office object library. This object essentially gives your VBA code all the functionality of the Windows Find File dialog box. For example, you can use this object to locate files that match a file specification (such as *.xls) and even search for files that contain specific text. You can use this object with Excel 97 and later versions.
The fastest growing business software product in the Microsoft family, Windows SharePoint Services is one of the hottest technologies for digital team site management available today. Flexible and easy to use, SharePoint provides users with simple site creation and design, robust content management, and powerful security for business data. Microsoft Office 2007 contains a myriad of new features that integrate with SharePoint to help users communicate information, and one of the pillars of the Access 2007 release is to provide seamless integration with SharePoint. Access has added new features that fit into two basic categories features that work from the server and features that work from within the Access client. Starting with the 2003 release, the Microsoft Office teams began a campaign to support content management on SharePoint for site design, online documents, and list data. In many cases, that could be done directly from within the Office application, such as from an Access...
You'll notice two differences between Figure 2-1 and Figure 2-2. First of all, the Property Sheet shows all of the properties for the control you selected on your form. From this view you can change any number of properties of the control including size, ControlTipText, and Tablndex. The second difference is subtle there's a new heading, Microsoft Office Access Class Objects, in the upper-left corner of the Project Explorer window. Under that heading is the name of the loaded form that contains the control you're currently working with.
Excel 2002 offers a new method for working with databases. The OpenDatabase method, which applies to the Workbooks collection, is the easiest way to get the database data into a Microsoft Excel spreadsheet. This method requires that you specify the name of a database file you want to open. The following example procedure opens the Northwind database located in the C Program Files Microsoft Office Office10 Samples folder. When you run this procedure, Excel displays the dialog box listing all the tables and queries in the database (Figure 15-13). After making a selection Filename C Program Files Microsoft Office _ & 0ffice10 Samples Northwind.mdb Filename C Program Files Microsoft Office _ & 0ffice10 Samples Northwind.mdb Filename C Program Files Microsoft Office _
Any VBA code that the event is to execute needs to be typed between those two lines of code. After you write your code, choose FileOSave and then choose FileOClose and Return to Microsoft Office Access from the VBA Editor menu bar. The VBA Editor closes, and you're back in the form's Design view. There you see the words
Excel 2002, like other applications in Microsoft Office, allows you to create hyperlinks in your spreadsheets. After clicking on a cell that contains a hyperlink, you can open a document located on a network server, an intranet, or the Internet. Hyperlinks can be created directly in the user interface with the Insert Hyperlink option (Figure 16-1) or programmati-cally using VBA.
The least common denominator necessary to run an Access-based application on a computer is the Access Runtime. Prior to Access 2003, the runtime could only be distributed if you purchased the developer edition of Microsoft Office. Since Microsoft Office 2003 doesn't have the developer edition you'll need to acquire the ADE to get rights to redistribute the Access Runtime. With the ADE you get unlimited rights to redistribute the runtime.
The Microsoft Script Editor (MSE) has been part of Office for several versions now, but you might not be aware of its existence. It doesn't appear in either the Microsoft Office or Microsoft Office Tools program group, nor is it one of the Open With selections on the right-click menu of a VBS file.
Use the Microsoft Office Access 11.0 Object Library ExportXML method of the Application object to export XML data, schemas (XSD), and presentation information (XSL) from a Microsoft Access database (.mdb), Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), or Microsoft SQL Server 6.5 or later.
Ord is the Microsoft Office application that you use to create and edit text, which means spending hours typing. All that typing takes time. However, you have at your disposal a personal secretary that can type thousands of words per minute (without error), and you might not even realize it. The programs that you create for Word can do more than simply change the user environment, convert data to text or eXtensible Markup Language (XML), and perform other helpful, nontyping tasks. The fastest typist that you'll ever know is the Word program that you create by using VBA.
StrDb C Program Files Microsoft Office _ myDb C Program Files Microsoft Office & Office Samples Northwind.mdb strFrmName frmCustomForm Set obAccess New Access.Application obAccess.OpenCurrentDatabase myDb Set myForm obAccess.CreateForm myForm.Caption Form created by Excel myForm.RecordSource Employees obAccess.DoCmd.Save , strFrmName ' Create a label and text box on the form
Excel's default security settings do not allow any macro activity. Before you begin exploring macros in Excel and the Macro Recorder, you will need to tell Excel which security settings to use to control what happens when you open a workbook that contains macros (or one that will contain macros). If you use antivirus software that works with Microsoft Office 2007 and you open a workbook that contains macros, the virus scanner will check the workbook for viruses before opening it. 1. Click the Microsoft Office button, which looks like the following Note Macro setting changes made in Excel's Macro Settings section apply to Excel only they do not affect any other Microsoft Office applications. 2. If the Developer tab is not available, do the following to display it a. Click the Microsoft Office button (shown in the following image).
To create a certificate for yourself, simply run the SelfCert.exe program. This is available from Start C All Programs C Microsoft Office C Microsoft Office Tools C Digital Certificate for VBA Projects. You can also run this from the Office12 folder. For example, mine is located in C Program Files Microsoft Office OFFICE12 SELFCERT.EXE. If SelfCert.exe is not installed on your computer, use the Microsoft Office 2007 installation disk to install it.
Benefits of an enhanced IntelliSense feature for the Outlook object model (or the Interop object models of Word and Excel), you should install the IntelliSense XML files that are available as a download on www.microsoft.com. In the Download Center, search for IntelliSense and select Microsoft IntelliSense XML Files for Microsoft Visual Studio Tools for the Microsoft Office System Solution Developers. You can then download and install VSTOIntelliSenseXML.msi.
The new open XML file formats for Microsoft Office represent a significant step for the computing community. For the first time, it's relatively easy to read and write Excel workbooks using software other than Excel. For example, it's possible to write a program to modify thousands of Excel workbook files without even opening Excel. Such a program could insert a new worksheet into every file. The programmer, of course, would need to have excellent knowledge of the XML file structures, but such a task is definitely doable.
On the Install Uninstall page, click Microsoft Office XP the exact title will vary depending on the version of Office XP installed on your computer and then click Add Remove on computers running Windows2000. On computers running Windows 98, Windows Me, and Windows NT 4, click Change. 5. In the Microsoft Office XP Maintenance Mode dialog box, click Add Or Remove Features. 6. Double-click the Office Tools item to expand the item in the Microsoft Office XP Update Features dialog box.
One of the most common uses of special applications is to perform complex calculations. You can create many types of equations by using any of the Microsoft Office products. Sometimes, however, you need to change the data before you can use it or perform the calculation differently depending on the value of one or more inputs. Whenever a calculation becomes too complicated for a simple equation, use VBA to simplify things by solving the calculation problem using small steps rather than one big step. Chapters 4 and 14 show a number of ways to work with calculations.
ADO connects to Microsoft Access databases through the use of the Microsoft Office 12 Access Database Engine OLE DB Provider. To connect to a Microsoft Access database, you simply specify this provider in the ADO connection string and then include any additional provider-specific arguments required. The following is a summary of the connection string arguments you will most frequently use when connecting to an Access database
The following custom project illustrates how you can use the Replication command on the Tools menu in the Microsoft Office Access user interface to create and synchronize replicas. To learn about conflicts during the synchronization of replicas, we will create two replicas, Replica_England.mdb and Replica_France.mdb, and enter some conflicting data into the same record in both of these replicas.
If you are writing a COM Add-in, you must explicitly add a reference to the Microsoft Office 9.0 Object Library or Microsoft Office 10.0 Object Library using the appropriate command in your development tool. If you are using Outlook VBA to write code in the VBAProject.otm file, Outlook automatically creates a reference to the Microsoft Office 10.0 Object Library.
In addition to Microsoft Excel objects, you can use the Microsoft Office, Microsoft forms, and DAO and ADO object models. Objects that belong to these libraries can be used in Excel, as well as in other applications that are members of the Microsoft Office family of products. See Chapter 15 for examples of using DAO and ADO object models in accessing the Microsoft Access databases from Excel.
Microsoft Office 2003 uses Microsoft Authenticode technology to enable you to digitally sign your Access database by using a digital certificate. A person using your signed database can then confirm that you are the signer and that your database has not been altered since you signed it. If that person then trusts you, they can open your database without regard to their Access macro security level setting.
You can install a free, fully supported add-on for Visual Studio that lets you develop application-level solutions for several 2007 Microsoft Office system and Microsoft Office 2003 applications. This add-on is available as a free download in the Microsoft Download Center. This download is Visual Studio 2005 Tools for the 2007 Microsoft Office System. In this book, it is referred to as Visual Studio Tools for Office Second Edition (VSTO 2005 SE) to distinguish it from Visual Studio Tools for Office (VSTO). VSTO 2005 SE provides an add-in model that enables you to create application-level customizations (add-ins) for six 2007 Microsoft Office system applications Microsoft Word, Excel, Outlook, InfoPath, PowerPoint, and Visio. You can also create add-ins for five Microsoft Office 2003 applications Microsoft Word, Excel, Outlook, PowerPoint, and Visio. VSTO 2005 SE also ensures that solutions you've built with VSTO for Office 2003 will run on the 2007 release of Office, provided that you...
Designed around the ability to access and manipulate objects, VBA has access to an Object Model in each Microsoft Office product, including Excel, that enables you to interact with each application. Using the Object Model, you can access everything from the entire application to an individual cell in a worksheet.
When sharing information between multiple Microsoft Office programs, you can write code two ways. The first way is to write the code within Access to push the data into the other Office programs. The second way is to write code within those other programs to pull code from Access into the other program. This two-way street works for Word, Excel, Outlook, and PowerPoint. Because this is an Access 2003 VBA book, we'll spend more time covering the push direction, but don't worry, we'll provide examples of the pull direction as well. Many of the examples in this chapter are based on an Inventory Control application for pallets of material from a manufacturing plant. The sample database with a large amount of the code discussed here is available. Our Inventory Control application launches with the switchboard shown in Figure 15-1. The application allows users to receive new material into a particular location, move material from one location to another, allocate material to a particular...
An Access add-in is a library database (an Access database with the extension .mda for Access 97-2003, or .accda for Access 2007) containing the objects and modules needed to support the add-in's functionality, and a special system table called UsysReglnfo with the Registry key information needed to install the add-in. Add-ins are typically stored in the default Microsoft Addlns folder (C Documents and SettingsWser Name Application Data Microsoft AddIns), which was also the default Access add-ins folder for Access 2003). In Access 2007 the default folder for Microsoft's own add-ins is the ACCWIZ folder under the Office folder (on my system, this is E Microsoft Office 2007 Beta Office12 ACCWIZ). However, it is a good idea to keep your own add-ins in the main AddIns folder (C Documents and SettingsWser Name Application Data Microsoft AddIns for Windows XP or C Users User Name AppData Roaming Microsoft AddIns for Windows Vista) rather than mixed in with the ones installed by Office.
Before learning how to program in VBA, you have to learn how to use the software required for creating your projects. The VBA development software is included with each component of the Microsoft Office suite of programs, including Excel. Starting the VBA development software places you in the VBA programming environment IDE, which provides you with a number of tools for use in the development of your project.
Access 2003, when teamed with SharePoint Portal Server or SharePoint Team Services 2.0 can provide some pretty awesome data sharing capabilities. Both flavors of SharePoint (Portal Server and Team Services) have the ability to share and track Microsoft Office documents among multiple users. And, with built-in document check-in check-out, version tracking, subscription services, e-mail updates and alerts, Windows SharePoint Services (WSS) makes it easy to share and manage information. It can even be used to ensure that users are automatically kept up-to-date on their projects.
Microsoft Visual Studio 2005 Tools for the Microsoft Office System (VSTO) Client Pre-Requisites Analysis Microsoft Office Product Microsoft Office 2003 Analysis Microsoft Office 2003 Analysis Microsoft Office 2003 Products Installed Microsoft Office OneNote 2003 (90A10409-6000-11D3-8CFE-0150048383C9 Microsoft Office Professional Edition 2003 (90110409-6000-11D3-8CFE-01500483 83C 9 Microsoft Office Project Professional 2003 (903B0409-EOOO-11D3-8CFE-01500483 S3C9 Microsoft Office FrontPage 2003 (9017040MOOO-11D3-8CFE-0150048383C9 Microsoft Office Visio Professional 2003 (9O5104OMOOO-11D3-BCFE-0150048383C91 Microsoft Office 2003 Program Installation
Most Microsoft Office products provide a Properties dialog box, similar to the one shown in Figure 3-10, that contains a Summary tab for documents. You can also find a variation of this Properties dialog box for most third-party products. The Summary tab can provide a lot of valuable information for your programs. You can find out basic statistics, such as the author's name and the company that created the document. The document information also includes statistics, such as the number of words that the document contains. See the BuiltinDocumentProperties help topic in the VBA help file for additional information. This is the first example where you work directly with an object. The property that you want to use is BuiltinDocumentProperties. This property is available for most of the Microsoft Office products, but it's attached to a different object in each one. When using Word, you find this property attached to both the Word.Document and Word.Template objects. Excel users find the...
Vou must explicitly enable access to the Microsoft Office Visual Basic for Applications project system before you can create or open a Visual Studio Tools for the Microsoft Office System project. By default access is disabled to help stop the spread of macro viruses. After enabling accessj you will still be protected by Microsoft Office macro security levels.
Check out Appendix D on using Microsoft Office web components. By publishing a PivotTable report or PivotTable chart as a web page, you can work with your PivotTable or PivotChart interactively inside Microsoft Internet Explorer. When you select the PivotTable report, Excel uses the Microsoft PivotTable web component to publish your data. When you save your PivotChart report as a web page, the publishing job is handled by the Microsoft Chart web component. To manually save your PivotTable report and PivotChart report as a web page, perform the following tasks (for programming examples, see Appendix D) Note To browse a web page interactively with the Office web components, you must have a Microsoft Office 2000 XP license. Figure A-15 When you publish a PivotChart report from Excel as a web page, the browser uses the Microsoft Office web chart component to graph the data and allow you to interact with the chart. Also, presented on the same page (inside the Microsoft Office PivotReport...
Start Microsoft Office Access and create a new blank database called SpecialDb.mdb. Save this database in the BookProject folder you created in step 1. Keep this database open and proceed to Part 2 below. C Program Files Microsoft Office OFFICE11 MSACCESS.EXE C Program Files Microsoft Office OFFICE11 MSACCESS.EXE
Open the Acc2003_Chap01.mdb database file from the book's downloadable file. This file contains a copy of the Customers table and the Customers form from the Northwind database that comes with Microsoft Office Access. In the Visual Basic Code window, choose File Close and Return to Microsoft Office Access. Notice that Event Procedure now appears next to the On Got Focus event property in the property sheet for the selected ContactName text box control (see Figure 1-9).
Before you can consume a Web service, you must download and install the Microsoft Office 2003 Web Services Toolkit. At the time of this book's creation, the easiest way to obtain the toolkit is to go to and then click the link to the Microsoft Office 2003 Web Services Toolkit 2.01. If you cannot locate the toolkit at the preceding link, you can go to the downloads area of Microsoft.com http www.microsoft.com downloads. Then, select the Office and Home Application Download Category in the left navigation pane and then run the following search Select Office System for Product Technology and type Web Services Toolkit in the Keywords text box. Click the Search button. You will see a link to the Microsoft Office 2003 Web Services Toolkit 2.01.
Restricts the scope and visibility of the contents of a module (i.e., its variables, classes, functions, and procedures) in VBA-enabled applications that allow references across multiple projects (e.g., Microsoft Office applications) to the module's project. Option Private Module has no effect in the standalone version of Visual Basic.
One of the main points of entry to code in Microsoft Office 2003 applications is through their menus and toolbars. Using the code you created in the preceding section to add a task, you can add additional code to call this code from a menu item that you create. In this example, you will delete the toolbar if it already exists and then re-create it.
You're creating the cEmployee class shown earlier. You'd never think of adding a Part Number property or a CalculateHorsepower method in an Employee class. The object defines its interface. These are words to live by. Classes initially built for Access applications were moved to VB 6 with little or no modification (and the reverse is also true). The same is true of bringing those classes into Excel applications. Of course, the .NET world with its new syntax changes that, but the concepts still apply (which helps to make a more effective transition to the .NET environment). That said, the classes provided in this book should work in almost any Microsoft Office environment (prerequisites and differences in component or Office versions notwithstanding). That's the beauty of using class-based code. Objects are defined as an instance of a class. Objects have properties (nouns) and methods (verbs), and can fire events. Each object instance holds its own values for its properties (private...
Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications (VBA). Actually, VBA is used not only by Microsoft Excel, but also by the other major components in the Microsoft Office application suite Access, Word, and PowerPoint. Any application that uses VBA in this way is called a host application for VBA. (There are also a number of nonMicrosoft products that use VBA as their underlying programming language. Among the most notable is Visio, a vector-based drawing program.) It is also used by the standalone programming environment called Visual Basic (VB).
You can control security settings relating to Outlook Today and folder home pages with the System Policy Editor available with the Microsoft Office XP Resource Kit. The settings that relate to folder home pages and Outlook Today are listed under Default User. For additional information regarding the System Policy Editor, see the documentation that accompanies the Office XP Resource Kit. The following table lists some of the security settings that relate to folder home pages and Outlook Today for Outlook 2002.
To create an Outlook COM Add-in, you should obtain Visual Studio 98 Professional Edition or Visual Basic 5 Professional Edition. Visual Studio 98 is the preferred development tool because you can use Visual InterDev 6 to customize folder home pages, as well as Visual Basic 6 to create COM Add-ins. You can also create COM Add-ins for Outlook with Microsoft Office XP Developer. If you don't have Office XP Developer, you can develop a project using ThisOutlookSession and Outlook Visual Basic for Applications (VBA), but a solution based on ThisOutlookSession will have several limitations, including Outlook's inability to run more than one ThisOutlookSession project in a given Outlook session. If you install a custom VBAProject.otm (the project that contains ThisOutlookSession), you will overwrite the existing VBAProject.otm for the current user. On the other hand, multiple COM Add-in solutions can run in a given Outlook session. The following table outlines the solutions you can create...
Consider VBA an interpreted language because as it runs, the environment in which it runs interprets the code to determine what to do next. Microsoft Office XP provides VBA in all its applications. Many non-Microsoft applications also use VBA, as the platform for developing code that interfaces with the object model for the specific application. By interfacing with the application object model, VBA can manipulate different objects directly, such as changing the value in a cell within Excel. You interface with the particular application object mode by writing macros, but you can also use VBA to develop applications that interface with the corresponding Microsoft application. Each Microsoft Office XP application has its own object model for interfacing with the program functionality. See Chapter 4 for more on the Excel object model. Because VBA replaced XLM, the original macro language of Excel, developers commonly refer to it as a macro language. Although the book uses VBA only to work...
This book takes you through the basics of using the Visual Basic Editor that comes with Microsoft Office, and familiarizes you with the essentials of Visual Basic for Applications programming. The book also covers the Excel Object Model, and illustrates how to use the various objects, properties, and methods to create macros. Although this book requires no prior experience with programming, a familiarity with the Microsoft Windows operating system installed on your computer and Microsoft Excel is an asset. Chapter 2, Using the Visual Basic Editor, shows you how to navigate and work with the Visual Basic Editor that comes with Microsoft Office applications. This chapter shows you how to set up your Visual Basic Editor window to quickly create and modify code modules.
When you record a macro, you can specify storing the macro code in the Personal Macro Workbook. When you store a macro in the Personal Macro Workbook, Excel creates a file named Personal.xls and places it in the XLStart folder, which is a subdirectory of Program Files Microsoft Office Office. Files that were saved to the XLStart folder are loaded automatically each time you start Excel. The Personal Macro Workbook is a convenient place to store general-purpose macros like the following one.
If the PIAs have not been correctly installed on your development machine because a complete installation of Office 2003 was not performed, you can run a reinstall or repair of Office 2003, assuming that .NET Framework 1.1 or later is already installed on your computer. Alternatively, you can use Add or Remove Features in the Maintenance Mode Options section of the Microsoft Office 2003 Setup, and click Next (see Figure 3.8). For information on redistributing the PIAs when you deploy your solutions, see Chapter 11. Figure 3.7. Microsoft Office PIAs displayed in the GAC Microsoft Office Excel .NET Programmability Support Microsoft Office Outlook .NET Programmability Support Microsoft Office Professional Edition 2003 Sjtf Urnnstal Microsoft Office 2 03 from this computer. Figure 3.8. Maintenance Mode Options in Microsoft Office 2003 Setup Microsoft Office Word .NET Programmability Support Microsoft OFFice 2003 Setup Microsoft Office Professional Edition 2003 B * I Microsoft Office...
The code is built and the application opens. When you double-click Sheetl, the exception that should occur when you try to convert a null variable to a string is swallowed by the debugger, and no error is displayed in a message box. By default, Visual Studio does not display errors that are thrown by Microsoft Office 2003 applications. This is why it's important to set the debugging options correctly before you start debugging your application.
The Microsoft Outlook 2002 Object Model This library contains the objects, properties, methods, and events for almost all the objects that you work with in Outlook, with the exception of the Forms 2.0 Object Library objects and the Microsoft Office XP Object Library objects. When you set a reference to this object library in the References dialog box, select Microsoft Outlook 10.0 Object Model. 3. The Microsoft Office XP Object Model This library contains the objects, methods, properties, and constants you use to work with the CommandBars, Assistant, COMAddlns, LanguageSettings, and AnswerWizard objects. When you set a reference to this object library in the References dialog box, select Microsoft Office 10.0 Object Library. To view the objects available in the Microsoft Outlook 2002 Object Model and the Microsoft Forms 2.0 Object Library, you can use the Help file described earlier in this chapter, or you can use an object browser. To view the objects available in the Microsoft...
MS Query is included with every edition of Microsoft Office. As you can see in Figure 16.1, MS Query is a visual query tool that looks similar to the query design view in Access. Using MS Query, you can define a query that runs and returns data to Excel. A query is basically a question that is phrased in terms that a database can understand. The data that a query returns is referred to as a result set or a recordset. NOTE Microsoft Query is not installed by default. You may require your Microsoft Office setup CD to install MS Query the first time you try and use it. Once you're satisfied that the query is retrieving the data you want the way you want it, select File Return Data to Microsoft Office Excel and then indicate where to place the data as shown in the following screenshot.
Excel 2007 builds on its history of XML support with a new file format called the Office Open XML format. This new format improves file and data management, data recovery, and interoperability. Any application that supports XML can access and work with information in an Excel 2007 workbook. This means it is now possible to work with your Excel data in systems outside of Microsoft Office products as long as they provide XML support.
Part I introduces you to Access 2003 VBA programming. Visual Basic for Applications (VBA) is the programming language for Microsoft Office Access. In this part of the book you acquire the fundamentals of VBA that you will use over and over again in building real-life Microsoft Access database applications. The skills obtained in Part I of this book are fairly portable. They can be utilized in programming other Microsoft Office applications that also use VBA as their native programming language (Excel, Word, PowerPoint, or Outlook). Part II introduces you to a set of programming objects known as ActiveX Data Objects (ADO) that enable Microsoft Office Access and other client applications to access and manipulate data. In this part of the book you learn how to use ADO objects in your VBA code to connect to a data source create, modify, and secure database objects as well as read, add, update, delete, and replicate data.
In Windows Vista, first select Programs in the Control Panel, then Programs and Features, then select the Microsoft Office item, as shown in Figure 16.1. If you are running Windows XP, start by selecting the Add or Remove Programs applet in the Control Panel, then the Microsoft Office 2007 item. In the Installation Options dialog, drop down the Microsoft Office Access list if the .NET Programmability Support item has a big red X, that means that it is not installed. To install it, drop down its list and select the Run from My Computer item (see Figure 16.3). gig Screen Shot Standard 3 1 Microsoft Office Word 2007 gig Screen Shot Standard 3 1 Microsoft Office Word 2007
Through the object model, the software vendor allows you to control the application, to set and retrieve properties, and to invoke methods. It's up to the software vendor to decide how much or how little of the application you have access to via its object model, and in the case of the Microsoft Office applications, 100 of their functionality is presumably exposed via the various object models. In addition to the object model, each host application has its own set of predefined (intrinsic) constants to speed development and make code easier to read and maintain.