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

Creating and Publishing HTML Files Using VBA

Useform Menu

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

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

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

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

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

Creating a Pivot Table Report Using the Pivot TableWizard Method

Add a new module to a VBA project and enter the PivotTable_Exter-nall procedure shown below. This procedure is also available on the companion CD-ROM in the ProgramPivots.xls file. 2. Run the procedure to generate the PivotTable. Sub PivotTable_External1 Dim strConn As String Dim strQuery_1 As String Dim strQuery_2 As String Dim myArray As Variant Dim destRange As Range Dim strPivot As String strConn Driver Microsoft Access Driver .mdb amp _ DBQ amp C Program Files Microsoft Office Office...

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

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

Linking an Excel Spreadsheet to a Microsoft Access Database

Example Micro Soft Access Work Sheet

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

Step Creating a Timesheet Spreadsheet

Input Choice And Create Vba

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

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

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

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

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