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

Passing Arguments

So far you've created simple VBA procedures that carried out specific tasks. These procedures did not require that you provide additional data before they could be run. However, in real life, procedures (both subroutines and functions) often take arguments. Arguments are one or more values needed for a procedure to do something. Arguments are always entered between parentheses. Multiple arguments are separated with commas. Having used Excel for a while, you already know that Excel's built-in...

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

New File Object

Microsoft Office XP applications contain a new feature known as a task pane. A task pane is a dialog box docked within the host application (see Figure B-1). This dialog box can contain one or more pages broken into sections. For example, the New Workbook page in Excel 2002 contains the following five sections Open a workbook, New, New from existing workbook, New from template, and an unnamed section at the bottom of the task pane. The NewFile object represents a new document that can be listed...

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

Assigning a Name to the VBA Project

A project is a set of Microsoft Excel objects, modules, forms, and references. Instead of the default name, VBAProject, that precedes the name of the workbook in the Project Explorer window, each project requires a unique name. Let's assign names to VBAProject (Chap01.xls) and VBAProject (Personal.xls) 1. Start Microsoft Excel, and open Chap01.xls where your WhatsInACell macro code is stored. The Personal Macro Workbook where you recorded the FormulasOnOff macro will be automatically loaded. 2....

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

Understanding Instructions Modules and Procedures

In Chapter 1, you learned that Microsoft Excel's macro recorder creates a series of instructions that are the exact equivalents of the actions you perform. These instructions are automatically placed in a workbook sheet called a module. Excel stores the module in a module folder located in the current workbook, a new workbook, or in the Personal Macro Workbook. To review the recorded macro code, you must activate the Visual Basic Editor window and double-click the module folder in the Project...

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

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 Objektmodell

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

Dialog Boxes and Custom Forms

Excel Dialog Boxes File Open and File Save As Dialog Boxes GetOpenFilename and GetSaveAsFilename Methods Creating Forms Tools for Creating User Forms Placing Controls on a Form Sample Application 1 Info Survey Adding Buttons, Check Boxes, and Other Controls to a Form Changing Control Names Setting Other Control Properties Preparing a Worksheet to Store Custom Form Data Displaying a Custom Form Setting the Tab Order Understanding Form and Control Events Writing VBA Procedures to Respond to Form...

Auto Correct Object

The AutoCorrect object has properties and methods that allow you to work with Excel's AutoCorrect features. The following two procedures will get you started working with AutoCorrect programmatically. The first procedure uses the ReplacementList method to retrieve common misspelled words and their automatic replacements into an array. The procedure then reads the values of this array and enters them into a worksheet so that you can print them out easily. If you need to update your AutoCorrect...

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

Diagram Object

You can illustrate various concepts using diagramming tools in Excel 2002. A diagram is a collection of shapes. Diagram types include Cycle, Target, Radial, Venn, and Pyramid. Each diagram contains nodes. A node represents an individual shape object in a diagram. To add a Diagram object programmatically to a worksheet, use the AddDiagram method of the Shapes collection. To add shapes to the diagram, use the DiagramNode object within the Shape object. The following procedure creates a diagram...

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

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

Vba Procedure Attributes

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

Nested Loops

So far in this chapter you have tried out various loops. Each procedure demonstrated the use of each individual looping structure. In programming practice, however, one loop is often placed inside of another. Visual Basic allows you to nest various types of loops For and Do loops within the same procedure. When writing nested loops, you must make sure that each inner loop is completely contained inside the outer loop. Also, each loop has to have its own unique counter variable. When you use...

Web Queries

If you are planning to retrieve data from a web page to use and analyze it in Excel, you can open Excel's Data menu and select Import External Data New Web Query. Web queries allow you to retrieve data from the web directly into Microsoft Excel. After placing data in a worksheet, you can use Excel tools for performing data analysis. Using a web query, you can retrieve into a worksheet a single table, a number of tables, or all the text that a particular web site contains. Microsoft Excel 2002...

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

Using the Input Box Method

In addition to the InputBox function, there is the InputBox method. If you activate the Object Browser window and type inputbox in the Search box and then press Enter, Visual Basic will display two occurrences of InputBox one in the Excel library and the other one in the VBA library Figure 4-12 . The InputBox method available in the Microsoft Excel library has a slightly different syntax than the InputBox function that was covered earlier in this chapter. Its syntax is expression.InputBox...

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

Creating Forms

Although ready to use and convenient, the built-in dialog boxes will not meet all of your VBA application's requirements. Apart from displaying a dialog box on the screen and specifying its initial settings, you can't control the dialog box's appearance. You can't decide which buttons to add, which ones to remove, and which ones to move around. Also, you can't change the size of the built-in dialog box. If you're looking to provide a custom interface, your only solution is to create a user...

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 an ASP Script

Open Notepad and enter the following ASP script ' declare variables Dim accessDB Dim conn Dim rst Dim sql Response.ContentType ' name of the database accessDB Northwind ' establish connection to the database conn DRIVER Microsoft Access Driver .mdb conn conn amp DBQ amp Server.Mappath accessdb Set rst ' select all records from Shippers table sql SELECT FROM Shippers ' Open Recordset and execute SQL statement above lt TABLE Border 1 gt lt lt Response.Write fld.Name gt lt TH gt lt...

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

Launching Applications

There's more than one way to launch an application. In fact, there are at least five ways you can manually start a program via the Start Programs menu, a shortcut menu, the Run command, the MS-DOS window, or by double-clicking an executable file in Windows Explorer. This section assumes that you are familiar with the manual techniques of launching applications and that you are anxious to experiment with additional techniques to start applications from inside the Microsoft Excel Visual Basic...

Recording a Macro

Now that you know what actions you need to perform, it's time to turn on the macro recorder and create your first macro. Before you follow the recording steps outlined below, be sure to remove the formatting from the example worksheet. To do this, press Ctrl A to select the entire worksheet. Then choose Edit Clear Formats. Select cells A1 A3 and choose Edit Delete. In the Delete dialog box, select the Entire row option button. Then click OK. To create your first macro, follow these steps Before...

Graphic Object

Use the Graphic object to place a picture in the header or footer area of an Excel worksheet. There are six properties of the PageSetup object Center-FooterPicture, CenterHeaderPicture, LeftFooterPicture, LeftHeader-Picture, RightFooterPicture, and RightHeaderPicture that can return the Graphic object. The following procedure displays the File Picker dialog box where the user can select a picture file. Next, the file is inserted in the left header of the active sheet. Notice that to make 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...

Working with Command Bar Controls

An empty toolbar does not serve any purpose. To make the toolbar useful, you need to place on it the desired controls and assign to them appropriate VBA procedures. There are three types of command bar controls, as shown in the following table. Table 12-3 Types of controls that can be placed on toolbars Object Name Description CommandBarButton This object represents toolbar buttons and menu options. When you click a button or select a menu option, an appropriate VBA procedure is executed....

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

Finding Out the Name of the Active Folder the Cur Dir Function

When you work with files, you often need to find out the name of the current folder. You can get this information easily with the CurDir function Drive is an optional argument. If you omit drive, VBA uses the current drive. The CurDir function returns a file path as Variant. To return the path as String, use CurDir where is the type declaration character for a string . To get some practice using these functions, let's perform a couple of exercises in the Immediate window 1. Open a new workbook...

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

Tip Character Encoding in XML

When you type an XML document into Notepad and save it, you can choose from one of several supported character encodings, including ANSI, Unicode UTF-16 , Unicode Big Endian , or UTF-8. The encoding declaration in the XML document identifies which encoding is used to represent the characters in the document. UTF-8 encoding allows the use of non-ASCII characters, regardless of the language of the user's operating system and browser or the language version of Office. When you use UTF-8 or UTF-16...

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

Worksheet Events

A Worksheet object responds to such events as activating and deactivating the worksheet, calculating a worksheet, making a change to a worksheet, and double-clicking or right-clicking a worksheet. This section discusses some of the events to which the Worksheet object can respond. This event occurs when the user activates a sheet. Dim shtName As String 'declared at the top Private Sub Worksheet Activate shtName ActiveSheet.Name Range B2 .Select End Sub The example procedure selects cell B2 each...

Sample Application Info Survey

Now that you've read through the theory of creating user forms and understand the differences between various controls available in the Toolbox, you are ready for some hands-on experience. As you already know, the best way to understand a complex feature is to apply it in a real-life project. In this section, you will create a custom form for a coworker who requested that you streamline the tedious process of entering survey data into a spreadsheet. As you work with this form Figure 10-11 , you...

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