Using Custom Smart Tags

Before you distribute your smart tag solutions to other users, be sure to test the solution on your computer to ensure that it performs as desired. After moving or copying your smart tag definition file (.xml) into the Pro-gram Files Common Files Microsoft Shared Smart Tag Lists folder on your computer, launch Excel and open a blank worksheet. Next, click in any cell and type one of the terms that you defined in the terms file, using upper- or lowercase letters as required. Recall that the...

Syntax Versus Grammar

Now that you know the basic elements of VBA (objects, properties, and methods), it's time to start using them. But how do you combine objects, properties, and methods into correct language structures Every language has grammar rules that people follow in order to make themselves understood. Whether you speak English, Spanish, French, or any other language, you apply certain rules to your writing and speech. In programming, we use the term syntax to specify language rules. You can look up the...

Step Creating an Access Database

The first file to place in the TimeTrack folder is the Microsoft Access database. This database will contain two tables for storing and retrieving employee timesheet data. 1. Launch Microsoft Access (version 2000 or 2002) and create a new database named 2. Create two tables, as shown in Figures 17-32 and 17-33. 3. Set up primary keys in both tables. In the tblTimeSheets table the PrimaryKey is TSheetId. In tblTimeSheetDetails the PrimaryKey is based on two fields TSheetId and DayOfWeek. Because...

Workbook Events

Workbook object events occur when the user performs such tasks as opening, activating, deactivating, printing, saving, and closing a workbook. Workbook events are not created in a standard VBA module. To write code that reponds to a particular workbook event, double-click ThisWorkbook in Visual Basic Editor's Project Explorer. In the Code window that appears, open the Object drop-down list and select the Workbook object. In the Procedure drop-down list, select the event you want. The selected...

Using the VBA Object Library

In the previous example, you used the properties of objects that are members of the Shapes collection in the Excel object library. While the Excel library contains objects specific to using Microsoft Excel, the VBA object library provides access to many built-in VBA functions grouped by categories. These functions are general in nature. They allow you to manage files, set the date and time, interact with users, convert data types, deal with text strings, or perform mathematical calculations. In...

Step Writing VBA Procedures for the Command Buttons

Start by writing a simple VBA procedure that will clear the timesheet when the user clicks the Clear button. This procedure should clear unprotected cells in the spreadsheet, except for the Employee name stored in cell C4. Note that when you are done writing VBA procedures, you should protect your timesheet so users can enter data only in the designated cells and cell ranges. The procedure assigned to the Clear button will also be automatically called after the timesheet data has been...

Creating a Pivot Chart Report Using VBA

A PivotChart represents the data in a PivotTable report. Using VBA code, you can create a PivotChart based on an existing PivotTable report, and you can change the layout and data displayed in a PivotChart just as easily as you can reformat a PivotTable report. Similar to PivotTable reports, PivotCharts are interactive and allow you to view data in different ways by changing the position or detail of the PivotChart fields. Excel creates a PivotChart report on a separate chart sheet. You may,...

Posting Excel XML Data to a Web Server

Armed with your newly acquired XML skills, you can now write a useful business application that will handle the required processing on the server without human intervention. Take, for example, a common problem related to collecting employee timesheets. Each week, an employee fills in a timesheet that reports regular hours worked and overtime. The timesheets are collected by a designated person, often an administrative assistant, who must then summarize the data and perhaps rekey each timesheet...

Figure

This XSL stylesheet will be used to format the raw XML data shown in Figure 17-11. The XSL document contains many HTML formatting instructions. (Take a look at Figure 17-13 and notice the following tags < HEAD> , < TITLE> , < BODY> , < CENTER> , < STRONG> , < TABLE> , < TR> , < TD> , < H6> , < HR> , and < FONT> .) The example stylesheet also uses two special XSL formatting instructions. The first one < xsl for-each tells the XSL processor to loop...

Using Automation to Access Microsoft Outlook

To access Outlook's object model directly from Excel, begin by establishing a reference to the Microsoft Outlook 10.0 or 9.0 Object Library. The example procedure that follows will insert your Outlook contact information into an Excel spreadsheet. Set objOut New Outlook.Application Set objNspc Headings Array(Full Name, Street, City, _ State, Zip Code, E-Mail) For Each cell In Range(A1 F1) cell.FormulaR1C1 Headings(i) i i + 1 For Each objItem In objNspc.GetDefaultFolder _...

Selected Objects in the Excel Object Model

The Excel 2002 objects presented here will show you how to make worksheets more attention grabbing with special formatting (see the following objects Tab, CellFormat, Characters, PageSetup, Format-Condition, Graphic, and Diagram). Other objects, such as Speech, SpellingOptions, and AutoCorrect, can help you make sure that the worksheets your users distribute are void of as many errors as possible. Objects such as Reference, VBProject, and VBComponent will give you a taste of what it is like...

Doing Things with Spreadsheet Ceils

When you are ready to write your own VBA procedure to automate a particular spreadsheet task, you will most likely begin searching for instructions that allow you to manipulate spreadsheet cells. You will need to know how to select cells, how to enter data in cells, how to assign range names, how to format cells, and how to move, copy, and delete cells. Although these tasks can be easily performed with the mouse or keyboard, mastering these techniques in Visual Basic for Applications requires a...

Transferring the Excel Spreadsheet to an Access Database

Many of the world's biggest databases began as spreadsheets. When the time comes to build a database application from your spreadsheet, you can resort to a tedious manual method to transfer the data, or you can use your new VBA programming skills to automatically turn your spreadsheets into database tables. Once in a database format, your Excel data can be used in advanced company-wide reports or as a stand-alone application (needless to say, the latter requires that you possess database...

Creating an Excel File from User Input

An ASP script can contain a form that is used for collecting user input. Assume that you need to gather information about patients visiting an urgent care center in your town. It's been requested that your data entry display screen has a web interface. Normally when you collect data on a web page, the information is saved into some sort of a database, like SQL Server or Access. However, your client particularly requested that the data from the input fields be saved directly to an Excel file....

Creating a Text File from Microsoft Access Data

You can create a comma- or tab-delimited text file from Access data by using a VBA procedure in Excel. Text files are particularly useful for transferring large amounts of data to a spreadsheet. The example procedure below illustrates how you can create a tab-delimited text file from an ADO recordset. For this procedure to work correctly, you must establish a reference to the Microsoft ActiveX Data Objects 2.6 Library. Refer to Chapter 8 for details on working with text files. After running...

Placing Excel Data in an Access Table

What if, rather then linking or embedding your Excel spreadsheet, you wanted to create an Access table from scratch and load it with the data sitting in a worksheet Using several programming techniques that you've already acquired in this book, you can easily achieve this task. Let's look at the VBA procedure that dynamically creates an Access table based on the Excel worksheet presented in Figure 15-19 (see the section Linking an Excel Spreadsheet to a Microsoft Access Database). Notice that...

Working with Sequential Files

The hard drive of your computer contains hundreds of sequential files. Configuration files, error logs, HTML files, and all sorts of plain text files are all sequential files. These files are stored on disk as a sequence of characters. The beginning of a new text line is indicated by two special characters. One is called the carriage return and the other line feed. When you work with sequential files, you start at the beginning of the file and move forward character by character, line by line,...

Excel and Active Server Pages

In Chapter 15 you learned various methods of retrieving data from a Microsoft Access database and placing this data in an Excel worksheet. This section explores another technology, known as ASP, that you can use for accessing and displaying data stored in databases. Active Server Pages (ASP) is a technology developed a few years ago by Microsoft. Using this technology, you can design powerful and dynamic web applications that change every time they are viewed. ASP is platform independent. This...

Contents

Chapter 1 Introduction to Spreadsheet Automation 1 Understanding Common Uses for Macros 2 Planning a Recording a Running the Modifying the Macro 8 Adding Analyzing the Macro Code 13 Cleaning Up the Macro Code 14 Testing the Modified Macro 15 Two Levels of Macro Execution 16 Improving Your Macro 17 Renaming the Other Methods of Running Macros 20 Saving Printing Storing Macros in the Personal Macro Workbook 27 Opening Workbooks Containing Macros 29 The Visual Basic Editor Window 31 Understanding...

Running a Microsoft Access Query

The two example procedures that follow demonstrate how to run Microsoft Access queries from within an Excel VBA procedure. The most popular types of queries that are executed in the Access user interface are Select and Parameter queries. Both example procedures use the CopyFrom-Recordset method of the Range object to place the data returned by the query into an Excel worksheet. The connection with the database is established via the ADO. The ADOX Object Library (see Figure 15-4 at the beginning...

Linking an Excel Spreadsheet to a Microsoft Access Database

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method (refer to the Retrieving Data with the TransferSpreadsheet Method section in this chapter for the details on working with this method). The following example procedure links the spreadsheet shown in Figure 15-19 to the Northwind database. After opening the Access database with the OpenCurrentDatabase method, the procedure uses the TransferSpreadsheet method of the Microsoft Access DoCmd...

Building XML Files Outside of Microsoft Excel

When you save a Microsoft Excel 2002 workbook in the XML spreadsheet file format, the program uses the XML Spreadsheet Schema (XML-SS) to encode the data and formatting of the spreadsheet. Based on what you've already learned in this chapter about XML spreadsheet elements and attributes, it is quite easy to create an XML spreadsheet file outside of Microsoft Excel. Assume that you are working on a computer where Excel is not installed and need to prepare a spreadsheet containing quarterly sales...

Importing an Excel Spreadsheet to a Microsoft Access Database

In the previous section, you learned how to link your Excel spreadsheet to an Access database. Importing your spreadsheet data is just as easy. You can even use the same VBA procedure you used for linking with one minor change simply replace the acLink constant with aclmport, and you are done. The following procedure imports the spreadsheet presented in Figure 15-19 (see the previous section) into the Northwind database. Dim objAccess As Access.Application Dim strName As String strName...

Checking Smart Tag Options in an Excel Workbook

Let's start by checking whether your workbook is set to recognize smart tags. To do this, choose Tools AutoCorrectOptions and click the Smart Tags tab (Figure C-2). Notice that both built-in and custom smart tags are listed as a group in the Recognizers section. Microsoft Excel 2002 comes with the MSN MoneyCentral smart tag that allows you to check the latest stock prices. So if you enter a financial symbol, such as MSFT, in a spreadsheet cell and the smart tag lists are selected as shown in...

Onthefly Syntax and Programming Assistance

The Edit toolbar in the Visual Basic Editor window contains several buttons that let you enter correctly formatted VBA instructions with speed and ease. If the Edit toolbar isn't currently docked in the Visual Basic Editor window, you can turn it on by choosing View Toolbars. Buttons located on the Edit toolbar make it easy to write and format VBA instructions. Buttons located on the Edit toolbar make it easy to write and format VBA instructions. Writing procedures in Visual Basic requires that...

Web Charthtm

< Title> Spreadsheet and Chart Component< Title> < body> < object id Spread1 > < h5> Data Source Northwind Database < h5> < p> < object id chrtSpace width 500 height 230> < object> < SCRIPT LANGUAGE VBScript> Sub Window_OnLoad() ' Declare variables Dim strConnection, rst, strSQL, count, r, c, myData, cons, objChart ' the connection string data source & _ C Program Files Microsoft Set rst ' define the SQL query to supply the data for charting strSql...

Improving Your Macro

After you record your macro, you may realize that the macro can perform some additional tasks. Adding new instructions to the macro code is not very difficult if you are already familiar with the Visual Basic language. In most situations, however, you can do this more efficiently when you delegate the extra tasks to the macro recorder. You may argue that Excel records more instructions than are necessary. One thing is for sure, however the macro recorder does not make mistakes, and you can rely...

Storing Macros in the Personal Macro Workbook

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. You...

Modifying the Macro

Before you can modify your macro, you must find the location where the macro recorder placed its code. As you recall, when you turned on the macro recorder, you selected This Workbook for the location. The easiest way to find your macro is by opening the Macro dialog box shown in Figure 1-4. 2. Select the name of the macro (WhatsInACell, in this case). Microsoft Excel opens a special window called Visual Basic Editor (also known as VBE), as shown in Figure 1-5. Using the keyboard shortcut...

Xml Do Excel

This book shows you what's doable with Microsoft Excel 2002 beyond the standard user interface. If you ever wanted to open a new worksheet without using the menu or create a fully automated custom form to gather data and store the results in a spreadsheet, you've got to learn some programming. This book shows you how to become more productive by delegating many time-consuming and repetitive tasks to Excel. Using Excel's built-in language, VBA (Visual Basic for Applications), you can bring a lot...

Builtin Constants

Both Microsoft Excel and Visual Basic for Applications have a long list of predefined constants that do not need to be declared. These built-in constants can be looked up using the Object Browser window that was discussed in detail in Chapter 2. Let's open the Object Browser to take a look at the list of Excel constants 1. In the Visual Basic Editor window, choose View Object Browser. 2. In the Libraries Projects list box, click the drop-down arrow and select Excel. 3. Enter Constants as the...

The Pivot Table Web Component

The PivotTable web component brings the PivotTable report capability to your web page, Microsoft Access, or Visual Basic form. With this component, users can easily sort, filter, group, outline, and manipulate data. The PivotTable web component can get its data from a tabular data source such as a Microsoft Excel spreadsheet, a database table, or an SQL query executed against the SQL Server, Oracle, or Microsoft Access database , an OLAP multidimensional cube data source, or an XML stream see...

Decisions Based on More Than One Condition

The SimplelfThen procedure that you worked with in the previous section evaluated only a single condition in the If .Then statement. This statement, however, can take more than one condition. To specify multiple conditions in an If .Then statement, use the logical operators AND and OR see Table 5-2 at the beginning of this chapter . Here's the syntax with the AND operator If conditionl AND condition2 Then statement In the above syntax, both conditionl and condition2 must be true for Visual...

Using the Msg Box Function

The MsgBox function that you have used so far was limited to displaying a message to the user in a simple, one-button dialog box. You closed the message box by clicking the OK button or pressing the Enter key. Create a simple message box by following the MsgBox function name with the text enclosed in quotation marks. In other words, to display the message The procedure is complete, you should prepare the following statement MsgBox The procedure is complete You can quickly try out the above...

Common Uses for Macros

Microsoft Excel 2002 comes with dozens of built-in, timesaving features that allow you to work faster and smarter. Before you decide to automate a worksheet task with a macro, make sure there is not already a built-in feature that you can use to perform that task. Consider, however, creating a macro when you find yourself performing the same series of actions over and over again or when Excel does not provide a built-in tool to do the job. Macros enable you to automate just about any part of...

Creating a Function Procedure

Like Excel functions, function procedures perform calculations and return values. The best way to learn about functions is to create one. So let's get started. After setting up a new VBA project, you will create a function procedure that sums up two values. 1. Open a new Excel workbook and save it as Chap04.xls. 2. Switch to the Visual Basic Editor window and select VBAProject Chap04.xls . 4. Select MyFunctions Chap04.xls in the Project Explorer window, and choose Insert Module. 5. In the...

Deleting a Custom Toolbar

If you create a toolbar and decide that you don't want to keep it, you can get rid of it without closing the Excel application. Simply use the Delete method. For example, to delete the Budget Plans toolbar you can enter the following statement in the Immediate window CommandBars Budget Plans .Delete Note You cannot delete built-in toolbars. Using the CommandBar Properties The CommandBar object has a number of properties. You'll work with some of them in the Immediate window. 1. Use the...

Executing a Function Procedure

In Chapter 1, you learned various ways to execute a subroutine procedure. Unlike a subroutine, a function procedure can be executed in just two ways. You can use it in a worksheet formula or you can call it from another procedure. Function procedures that you create in VBA cannot be accessed by choosing Tools Macro Macros in the Microsoft Excel window. And they cannot be run by pressing the F5 key when the mouse pointer is located inside the code of the function procedure. In the following...

Chart Events

As you know, you can create charts in Excel that are embedded in a worksheet or located on a separate chart sheet. In this section, you will learn how to control chart events no matter where you've decided to place your chart. Before you try out selected chart events, perform the following tasks 1. Open a new Excel workbook and save it as ChartEvents.xls. 2. Enter sample data, as shown in Figure 14-6. 3. Select cells A1 D4, and click the Chart Wizard button on the Standard toolbar. 4. Prepare a...

Saving a Range of Cells as an XML Document

In the beginning of this chapter you learned how to save an Excel spreadsheet in the XML-SS spreadsheet format, both by using File Save As and programmatically via the Save As method of the Workbook object and the special parameter xlXMLSpreadsheet. At times, however, it may be desirable to save only a specific range of cells in the XML format. While the user interface does not offer such an option, Excel 2002 now offers a Value property of the Range object with the xlRangeValueXMLSpreadsheet...

Saving Pivot Table Report and Pivot Chart Report as a Web Page

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...

XML Support in Excel

Microsoft Excel 2002 offers extensive XML support. To begin with, instead of saving your workbook file as a standard .xls file, you can just as easily save it as an XML spreadsheet. To do this, no programming skills are required. In the Microsoft Excel application window, simply choose File Save As and select XML Spreadsheet in the Save as type drop-down box of the Save As dialog box Figure 17-1 . You can also open the XML spreadsheet back into Excel and use it as if it were a standard Excel...

Format Condition Object

You can add conditional formatting to your spreadsheet by using the For-matCondition object. Conditional formatting is associated with a particular range of cells. The FormatCondition object is a member of the Format-Conditions collection. This collection can contain up to three FormatCondition objects for a given range. Use the Count method of the FormatConditions collection to return the number of objects in the collection. Use the Add method of the FormatConditions collection to create a new...

Understanding Linking and Embedding

Before you learn how to control other applications from a VBA procedure using Automation, let's take a look at how the manual method is used to link and embed an object. Object linking and embedding, known as OLE, allows you to create compound documents. A compound document contains objects created by other applications. For example, if you embed a Word document in a Microsoft Excel worksheet, Excel only needs to know the name of the application that was used to create this object and the...

The XML Flattener

It's quite interesting to see how Excel 2002 deals with XML files that have been created outside of Excel but do not use the XML-SS that Microsoft uses to describe spreadsheet data. Let's again assume that you've just received an e-mail with an XML file attachment listing the VBA course schedule for the coming year. The Courses.xml file is shown in Figure 17-11. You don't care how this file was generated. It could have been typed directly in a text editor or outputted from a database using a...

Retrieving Data with the Get Rows Method

To place Microsoft Access data into an Excel spreadsheet, you can use the GetRows method. This method returns a two-dimensional array where the first subscript is a number representing the field, and the second subscript is the number representing the record. Record and field numbering begins with 0. You can programmatically return data to a Microsoft Excel worksheet by using Data Access Objects DAO in your VBA procedure. The following example procedure demonstrates how to run the Invoices...

Tip What is XPath

XML Path Language XPath is a query language used to create expressions for finding data in the XML. These expressions can manipulate strings, numbers, and Boolean values. They can also be used to navigate an XML tree structure and process its elements with XSLT instructions. XPath is designed to be used by XSL Transformations XSLT see Tip 17-9 . With XPath expressions, you can easily identify and extract from the XML document specific elements nodes based on their type, name, values, or the...

Establishing a Reference to an Object Library

If you decide to use early binding to connect to another application via Automation, you should start by establishing a reference to the object library whose objects you are planning to manipulate. Follow these steps to create a reference to the Microsoft Word Object Library 1. Activate the Visual Basic Editor window. 2. Select the current project in the Project Explorer window, and choose Tools References. 3. In the References dialog box, choose the name of the application in the Available...

Creating a Pivot Table Report

Before you can create a PivotTable, you need to prepare the data. You can get the data from one of the following sources A range on an Excel worksheet type in your data or paste from other sources External data source such as a Microsoft Access or SQL Server database Multiple consolidation ranges Another PivotTable or PivotChart report Figure A-1 displays the data that was dumped into a Microsoft Excel worksheet from an SQL Server database. The workbook file named WarrantyCounts.xls is located...

Creating an Embedded Chart from Microsoft Access Data

Using VBA, you can easily create a chart based on the data retrieved from a Microsoft Access database. The ChartData procedure shown below uses the data fetched from the Microsoft Access Northwind database to create an embedded chart. The chart is created by using the Add method of the Charts collection. The source of the chart data is provided by the Range object. The CurrentRegion method returns all the non-blank cells surrounding cell A1. The remaining part of the procedure formats the chart...

Learning about Objects Properties and Methods

Excel Range Object

When you learn new things, theory can give you the necessary background, but how do you really know what's where The majority of people think in pictures. To make it easy to understand the Microsoft Excel object hierarchy, the Visual Basic online help offers a diagram of the object model, as shown in the following figures. Figure 2-4 Microsoft Excel object hierarchy Page 1 Figure 2-4 Microsoft Excel object hierarchy Page 1 Notice that the Application object is positioned at the very top of the...

Modifying a Builtin Shortcut Menu

Microsoft Excel offers 60 shortcut menus with different sets of frequently used menu options. The shortcut menu appears when you right-click on an object in the Microsoft Excel application window. Using VBA, you can return the exact number of the shortcut menus, as well as their names. 1. Enter the ShortcutMenus procedure in the current project's module, as shown below Dim myBar As CommandBar Dim counter As Integer For Each myBar In CommandBars If myBar.Type msoBarTypePopup Then counter counter...

The Spreadsheet Web Component

Because you already know how to use a Microsoft Excel Workbook object, you will find it extremely easy to work with the Spreadsheet component. This component is very useful for displaying formatted data, performing calculations, or simply providing your users with a quick way to enter, calculate, and store data. The current XP version of the Spreadsheet component supports multiple worksheets containing 262,144 rows and 18,278 columns ZZZ each. Creating business solutions that utilize the...

Doing Things with Workbooks and Worksheets

Now that you've got your feet wet working with worksheet cells and ranges, it's time to move up one level and learn how you can control a single workbook, as well as an entire collection of workbooks. You cannot prepare a new spreadsheet if you don't know how to open a new workbook. You cannot remove a workbook from the screen if you don't know how to close a workbook. These important tasks are handled by two VBA methods Add and Close. The next series of drills will give you the language skills...

Creating a New Microsoft Access Database

If you want to programmatically transfer Excel data into a new Access database, you may need to create a database from scratch by using VBA code. The following example procedure demonstrates how to use the Data Access Objects DAO to establish a connection with Microsoft Access. The CreateDatabase method of the Workspace object is used to create a new database named ExcelDump.mdb in the root folder of the C drive. The CreateTableDef method of the Database object is then used to create a table...

Understanding Form and Control Events

In addition to having properties and methods, each form and control has a predefined set of events. An event is some type of action, such as clicking a mouse button, pressing a key, selecting an item from a list, or changing a list of items available in a list box. Events can be triggered by the user or the system. To specify how a form or control should respond to events, write event procedures. When you design a custom form, you should anticipate and program events that can occur at run time...

Do Loops DoWhile and DoUntil

Visual Basic has two types of Do loop statements that repeat a sequence of statements either as long as or until a certain condition is true. The Do While loop lets you repeat an action as long as a condition is true. This loop has the following syntax Do While condition statement1 statement2 statementN When Visual Basic encounters this loop, it first checks the truth value of the condition. If the condition is false, the statements inside the loop are not executed. Visual Basic will continue...

Saving the ADO Recordset to XML in Memory

If you are working with ADO 2.5 or higher, you can take advantage of the Stream object to save your ADO recordset in XML format directly to memory, instead of a disk file. You can then process your recordset as needed. The contents of the Stream object can be printed out using the Stream object's ReadText method. Saving the recordset to memory is much faster and more scalable than saving to a disk file, especially when all you need to do is manipulate the recordset and you don't need a physical...

Working with Binary Files

Unlike random access files that store data in records of fixed length, binary files are a collection of records with variable lengths. For example, the first record can contain ten bytes, the second record can have only five bytes, while the third record can have 15 bytes. This method of storing data saves a lot of disk space. Because Visual Basic doesn't need to add additional spaces to the stored string to ensure that all the fields are of the same length as when writing data to a random...

Opening a Microsoft Access Form

Access Form Sample

You can open a Microsoft Access form from Microsoft Excel. You can also create a new form. The following example uses Automation to connect to Microsoft Access. Once the connection is established, the OpenCurrent-Database method is used to open the sample Northwind database. Next, the Customers form is opened with the OpenForm method of the DoCmd object. The form is opened in the normal view acNormal . To display the form in the design view, use the acDesign constant instead. The Restore method...

Retrieving Data with the Copy FromRecordset Method

To retrieve an entire recordset into a worksheet, you can use the CopyFromRecordset method of the Range object. This method can take up to three arguments Data, MaxRows, and MaxColumns. Only the first argument, Data, is required. This argument can be the Recordset object. The optional arguments, MaxRows and MaxColumns, allow you to specify the number of records that should be returned MaxRows and the number of fields MaxColumns . If you omit the MaxRows argument, all the returned records will...

Transforming Attribute Based XML Data into an HTML Table

As you've seen in earlier examples, after creating an XML file from the ADO Recordset, the generated output is quite far from what you'd like to present to users. How about converting this output directly to an HTML table You can create a generic XSL stylesheet that draws out a simple table for the users when they open the file in their browser. Before we get started, open the Products.xml file in your browser and print it out. Keep the copy handy as we analyze the code of the stylesheet. The...

Setting the Tab Order

The user can move around a form by using a mouse or Tab key. Because many users prefer to navigate through the form using the keyboard, it is important to determine the order in which each control on the form is activated. Follow these steps to set the tab order in the Info Survey form 1. In the Forms folder in the Project Explorer Window, double-click the InfoSurvey form. 2. Choose View Tab Order. The Tab Order dialog box appears. This box displays the names of all the controls on the...

Microsoft Office XP Web Components

Some of the powerful functionality of Microsoft Excel 2002 can be made available outside of Microsoft Excel via a collection of ActiveX controls known as Microsoft XP web components. This collection is composed of four components a spreadsheet, chart, pivot table, and data source. Web components can be used not only in web pages as the name implies but also in other applications and environments, such as Microsoft Access, Visual Basic, and C . Let's say you need to display information on a web...

Creating a Pivot Table Report Using the Create PivotTabie Method of the Pivot Cache Object

When you use the macro recorder to generate the code for creating a PivotTable programmatically, Excel uses the Add method of the PivotCaches collection to create a new PivotCache. A PivotCache object represents the data behind a PivotTable. It is an area in memory where data is stored and accessed as required from a data source. The example procedure connects to the Microsoft Access Northwind database using the MicrosoftJet.OLEDB.4.0 provider. To use this type of connection, you must set up a...

Retrieving Information from Element Nodes

Let's assume that you want to read the information only from the text element nodes and place it in an Excel spreadsheet. Use the getElements-ByTagName method of the DOMDocument object to retrieve an IXMLDOMNodeList object containing all the element nodes. The getEle-mentsByTagName method takes one argument specifying the tag name for which to search. To search for all the element nodes, use the as the tag to search for as illustrated in the procedure below . The following exercise demonstrates...

HTMLProject Object and HTMLProjectltem Object

Both Office 2000 and Office XP contain a special development tool called Microsoft Script Editor MSE that allows you to work with documents as web pages. To access this tool in Excel 2002, open the Tools menu, point to Macro, and choose Microsoft Script Editor. The MSE can also be accessed by pressing Alt Shift F11 or programmatically by using the HTML-Project object. Try the latter by opening any workbook you want in Microsoft Excel, switch to the Visual Basic Editor screen, activate the...

Creating a Text File Using WSH

Windows Scripting Host WSH offers three methods for creating text files CreateTextFile, OpenTextFile, and OpenAsTextStream. The syntax of each of these methods and example procedures are presented in the following table. Table 8-7 Various methods of creating text files Method Syntax Example overwrite , unicode Object is the name of the FileSystemObject or the Folder object. Filename is a string expression that specifies the file to create. Overwrite optional is a Boolean value that indicates...

Creating Hyperlinks Using VBA

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. fol My Digital Camera J 3V amp Floppy A Local Disk C Compact Disc D 3 Removable Disk E DirectCD F Figure 16-1 A dialog...

Loading an ADO Recordset

After saving an ADO recordset to an XML file on disk, you can load it back and read it as if it were a database. To gain access to the records saved in the XML file, use the Open method of the Recordset object and specify the filename including its path and the persisted recordset service provider as Provider MSPersist. Let's look at an example. The following exercise demonstrates how to open a persisted recordset. 1. In the same module where you entered previous procedures, enter the procedure...

Adding Calculated Fields and Items to a Pivot Table

You can customize a PivotTable report by defining calculated fields and items. Using the contents of other numeric fields in a PivotTable, you can create a calculated field that performs the required calculation. For example, the procedure demonstrated below creates two calculated fields named Change 2001 2000 and Change 2000 1999 to calculate the difference in number of products sold from year to year. Figure A-8 shows the source data and the PivotTable generated by the procedure listed below....

Using the Open Database Method

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...

Well Formed XML Documents

When you create or modify an XML document, you must make sure that your XML file is well-formed. See Tip 17-5 for what makes a document well-formed. The well-formedness of an XML document is similar to syntax checking in VBA. When you try to open an XLM file in Excel that is not well-formed, you will receive an error message similar to the one in Figure 17-5. I have forced this error by removing the end tag lt CELL gt from the Units.xml file while it was opened in Notepad. I then resaved the...

Calling a Procedure from Another Project

You can call a procedure located in any module in the same project by specifying the procedure name. Let's suppose that the procedure FormulasOnOff is located in another module in the same project as the WhatsInACell macro. To call the procedure FormulasOnOff from the WhatsInACell macro, all you need to do is specify the procedure name, as shown in the following example lt place recorded macro instruction here gt FormulasOnOff End Sub However, if two or more modules contain a procedure with the...

Retrieving Data with the Transfer Spreadsheet Method

It is possible to use the TransferSpreadsheet action to import or export data between the current Microsoft Access database .mdb or Access project .adp and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program. The TransferSpreadsheet method...

Finding Out about Ceil Formatting

A frequent spreadsheet task is applying formatting to a selected cell or a range. Your VBA procedure may need to find out the type of format applied to a particular worksheet cell. To retrieve the cell formatting, use the NumberFormat property Upon entering the above question in the Immediate window, Excel displays the word General, which indicates that no special formatting was applied to the selected cell. To change the format of a cell using VBA, enter the following instruction Range A1...

Using an XSLT Template

Earlier in this chapter, you prepared an XSL stylesheet using the lt xsl for-each gt formatting instruction. In addition to for-each processing of XML elements, you can use templates to perform tranformations of XML documents. The XSL stylesheet can contain one or more XSLT templates. You can think of templates as special blocks of code that apply to one or more XML tags. Templates contain rules for displaying a particular branch of elments in the XML document. The use of templates is made...

Printing Excel Data to an Internet Browser using the Get String Method

You can use the Recordset object's GetString method to print the data contained in an Excel spreadsheet in an Internet browser. This method returns a set of records into a string and is faster than looping through the recordset. The GetString method has the following syntax variant recordset.GetString StringFormat, NumRows, _ ColumnDelimiter, RowDelimiter, NullExpr The first argument StringFormat determines the format for representing the recordset as a string. The second argument NumRows...

Creating a Query Table from Microsoft Access Data

If you want to work in Excel with data that comes from external data sources and you know that the data you'll be working with often undergoes changes, you may want to create a query table. A query table is a special table in an Excel worksheet that is connected to an external data source, such as a Microsoft Access database, SQL Server, web page, or text file. To retrieve the most up-to-date information, the user can easily refresh the query table. Microsoft Excel offers a special menu option...

Web Queries with Parameters

C Rich text formatting only c Full HTML formatting Import settings for preforrratted lt PRE gt blocks P Import lt PRE gt blocks Into columns I Treat cor amp ecubve delmiterr- .3 gt one Use tb same import setting For the etfire section Other Import settings-- disable date recognition Disable Web query redirections Figure 16-15 The Web Query Options dialog box Often, in order to retrieve data from a web page, you need to specify parameters. To send parameters to the web server in your web query,...

Working with Random Access Files

When a file contains structured data, open the file in the Random mode. A file opened for random access allows you to Read write data at the same time Quickly access a particular record In random access files, all records are of equal length, and each record has the same number of fixed-size fields. The length of a record or field must be determined prior to writing data to the file. If the length of a string that is being written to a field is less than the specified size of the field, Visual...

Creating Shortcuts

When you start distributing your VBA applications, users will likely request that you automatically place a shortcut to your application on their desktop. VBA does not provide a way to create Windows shortcuts. Luckily for you, you now know how to work with WSH, and you can use its Shell object to create shortcuts to applications or web sites without any user intervention. The WshShell object exposes the CreateShortcut method, which you can use in the following way Set myShortcut...

Using the Command Bar Object

CommandBars is a collection of objects that represents all the toolbars in the active application. Each object in this collection is called CommandBar. The term CommandBar is used to refer to a menu bar, a shortcut menu, or a toolbar. Because the CommandBar object can refer to various tools toolbar, menu bar, shortcut menu , this object comes with a special Type property that can be used to return the specific type of the toolbar as shown in Table Table 12-1 Types of CommandBar objects in the...

The Data Source Web Component

Unlike the other Office XP web components Spreadsheet, Chart, and PivotTable , the Data Source web component is an ActiveX control without a user interface. Its main purpose is managing connections to the underlying data source and fetching records for display by other controls on a web page. This section demonstrates how to set up the Data Source control for providing data to a chart control. The next section in this appendix shows how to bind the Data Source control to a PivotTable. You can...

Using Multi Page and Tab Strip Controls

Image Control Excel Vba

The central object of the custom form shown in Figure 10-16 is a MultiPage control that consists of two pages. The first page contains text and combo boxes to gather student data, such as Social Security number SSN , first and last name, year of study, and major. A group of option buttons at the top of the form allows you to specify the student's status New or Active. If the student's data has not yet been entered in a worksheet, the student's status is New. When the form is first loaded, the...

The XML Document Object Model

You can create, access, and manipulate XML documents programmatically via the XML DOM Document Object Model . The DOM has properties, methods, and constants for interacting with XML documents. The XML DOM is supplied with Internet Explorer. To use the XML DOM from your VBA procedures, you need to set up a reference to the MSXML object library. To do this, switch to the Visual Basic Editor window and choose Tools References. In the References dialog box, locate and select Microsoft XML, v3.0 see...

The Chart Web Component

The Office XP Chart component can display data from various sources, such as other web components Spreadsheet, PivotTable, and Data Source controls , arrays, tab-delimited strings, and ADO recordsets. When the Chart component is bound to other web components, changes that are made to the data placed in those controls are immediately displayed on the chart. To create complex charts, you need to know numerous properties and methods that the chart object exposes. You can learn a lot about the...

Working with XML Document Nodes

As you already know, the XML DOM represents a tree-based hierarchy of nodes. An XML document can contain nodes of different types. For example, an XML document can include a document node that provides access to the entire XML document or one or more element nodes representing individual elements. Some nodes represent comments and processing instructions in the XML document, and others hold the text content of a tag. To determine the type of node, use the nodeType property of the IXMLDOMNode...

Excel Dialog Boxes

Before you start creating your own forms, you should spend some time learning how to take advantage of dialog boxes that are built into Excel and are therefore ready for you to use. I'm not talking about your ability to manually select appropriate options but how to call these dialog boxes from your own VBA procedures. Microsoft Excel has a special collection of built-in dialog boxes that are represented by constants beginning with xlDialog, such as xlDialogClear, xlDialogFont,...

Get OpenFilename and Get SaveAsFilename Methods

For many years now, Excel has offered its programmers two handy VBA methods for displaying the File Save As and File Open dialog boxes GetOpenFilename and GetSaveAsFilename. These methods are available only in Excel and can still be used in Excel 2002 if backward compatibility is required. The GetOpenFilename method displays the Open dialog box, where you can select the name of a file to open. The second method GetSaveAsFilename shows the Save As dialog box. 1. Try out the following...

Using Automation to Connect to a Microsoft Access Database

When working with Microsoft Access from Excel or another application using Automation, take the following steps 1. Set a reference to the Microsoft Access 10.0 Object Library. Refer to Setting Up References to Object Libraries earlier in this chapter . 2. Declare an object variable to represent the Microsoft Access Application object. In this declaration line, objAccess is the name of the object variable, and Access.Application qualifies the object variable with the name of the Visual Basic...

Creating and Publishing HTML Files Using VBA

Like previous versions, Excel 2002 allows you to save files in the HTML Hypertext Markup Language format. This format is recognized by Excel just like its standard .xls format. When you save an Excel file in the HTML format, you can view the spreadsheets using an Internet browser, such as Internet Explorer or Netscape Navigator. When you save a workbook or its part as HTML, Excel saves the options of the original workbook. Thanks to this, the user can view the file either in the browser or...

Opening a Microsoft Access Report

Microsoft Access Report

You can open a Microsoft Access report from Microsoft Excel. The following procedure demonstrates how you can display an existing Access report straight from Excel. ' declare at the top of the module Dim objAccess As Access.Application Sub DisplayAccessReport Dim strDb As String Dim strRpt As String strDb C Program Files Microsoft Office _ amp strRpt Products by Category Set objAccess New Access.Application With objAccess .OpenCurrentDatabase strDb .DoCmd.OpenReport strRpt, acViewPreview...

Query Table Events

A query table is a table in an Excel worksheet that represents data returned from an external data source, such as an SQL Server database, a Microsoft Access database, a web page, or a text file. A query table is represented by the QueryTable object. Excel provides two events for the QueryTable object BeforeRefresh and AfterRefresh. To try out the example procedures demonstrated in the table at the end of this section, perform the tasks outlined below. This exercise assumes that you have...

Creating a Pivot Table Report Programmatically

Although the PivotWizard utility has undergone many improvements to make it easy to use, some users still find the process of creating PivotTable reports confusing. For those users, you may want to generate PivotTables via VBA code. Also with VBA, you can make many formatting changes to the existing PivotTables. This section demonstrates how you can work with PivotTables programmatically. We will start by creating the PiviotTable report shown earlier in Figure A-3 using the data source...

Returning and Setting File Attributes the Get Attr and Set Attr Functions

Files and folders can have characteristics such as read-only, hidden, system, and archive. These characteristics are called attributes. To find out the attributes of a file or folder, use the GetAttr function. The only argument of this function is the name of the file or folder that you want to work with The above function returns an integer that represents the sum of one or more of the constants shown below Table 8-2 File and folder attributes The above function returns an integer that...

Using the Get Object Function

If you are certain that the Automation object already exists or is already open, consider using the GetObject function. This function looks like this The GetObject function has two arguments, both of which are optional. Use the first argument to specify the name of the file that you want to open. The full path should be given. If you omit this argument, you have to specify the class argument that indicates the type of object to work with. For example To create an Excel object based on the...

Step Creating a Timesheet Spreadsheet

Excel Overtime Formula

Now is the time to set up the user interface for your web application Figure 17-34 . Because most users are familiar with the spreadsheet format, they will welcome the idea of keeping track of their time in a worksheet, especially if you make it quick and easy for them to use. 1. Create a worksheet, as shown in Figure 17-34 above. Use the following Each day of the week has a corresponding date that is calculated, as shown below Mon Tue Wed Thu IF C 5 lt gt ,C11 1, Fri Sat Sun Use the SUM...

VBProject Object VBComponent Object and Reference Object

To program and manipulate Visual Basic Editor VBE in code, you need to access objects contained in the Microsoft Visual Basic for Applications Extensibility 5.3 Library VBIDE . Before you can use the objects from the VBIDE library, you must do two things 1. Enable access to VBProjects. In the Microsoft Excel application window, choose Tools Macro Security. Click the Trusted Sources tab, select the Trust Access to Visual Basic Project check box, and click OK. Note If access to VBProjects is not...

Embedded Chart Events

To capture events raised by the chart embedded in a worksheet, you must first create a new object in the class module using the keyword WithEvents. To see how this is done, let's follow the steps outlined below 1. Activate the Visual Basic Editor window. 2. In the Project Explorer, select VBAProject ChartEvents.xls . 3. Choose Insert Class Module. 4. In the Class Modules folder, you will see a module named Class1. 5. In the Properties window, rename Class1 to clsChart. 6. In the Code window of...