Excel 2007 VBA Programming with XML and ASP

Excel 2007 VBA Programming with XML and ASP

by Julitta Korol
Released December 2008
Publisher(s): Jones & Bartlett Learning
ISBN: 9780763782764

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description


Excel 2007 Programming by Example with XML and ASP offers a hands-on approach for those looking to extend and customize Excel functionality. From recording a simple macro and writing VBA code to working with XML documents and using ASP to accss and display data, this book takes you on a progrmming journey that will change the way you work with Excel.
Learn how to automate spreadsheet tasks with macros; write VBA code to program PivotTables, generate charts, build dialog boxes, and customize the Ribbon; handle errors and debug programs; create hyperlinks and publish HTML files.
Retrieve data from the web directly into Excel; develop and manipulate smart tags using XML.

Table of contents

  1. Excel 2007 VBA Programming with XML and ASP
    1. Copyright
    2. Acknowledgments
    3. Introduction
      1. Who This Book Is For
      2. The Companion Files
    4. Part I: Introduction to Excel 2007 VBA Programming
      1. Chapter 1: Automating Spreadsheet Tasks with Macros
        1. What Are Macros?
        2. Common Uses for Macros
        3. Excel 2007 Versions and File Formats
        4. Macro Security Settings
        5. Planning a Macro
        6. Recording a Macro
        7. Executing Macros
        8. Editing Macros
        9. Macro Comments
        10. Analyzing the Macro Code
        11. Cleaning Up the Macro Code
        12. Testing the Modified Macro
        13. Two Levels of Macro Execution
        14. Improving Your Macro
        15. Renaming the Macro
        16. Other Methods of Running Macros
        17. Saving Macros
        18. Printing Macros
        19. Storing Macros in the Personal Macro Workbook
        20. Chapter Summary
      2. Chapter 2: Exploring the Visual Basic Editor (VBE)
        1. Understanding the Project Explorer Window
        2. Understanding the Properties Window
        3. Understanding the Code Window
        4. Other Windows in the Visual Basic Editor Window
        5. Assigning a Name to the VBA Project
        6. Renaming the Module
        7. Understanding Digital Signatures
        8. Creating a Digital Certificate
        9. Signing a VBA Project
        10. Chapter Summary
      3. Chapter 3: Excel VBA Fundamentals
        1. Understanding Instructions, Modules, and Procedures
        2. Calling a Procedure from Another Project
        3. Understanding Objects, Properties, and Methods
        4. Microsoft Excel Object Model
        5. Writing VBA Statements
        6. Understanding VBA Errors
        7. In Search of Help
        8. On-the-fly Syntax and Programming Assistance
        9. Using the Object Browser
        10. Using the VBA Object Library
        11. Locating Procedures with the Object Browser
        12. Using the Immediate Window
        13. Learning about Objects
        14. Working with Worksheet Cells
        15. Working with Workbooks and Worksheets
        16. Working with Windows
        17. Managing the Excel Application
        18. Chapter Summary
      4. Chapter 4: Using Variables, Data Types, and Constants
        1. Saving Results of VBA Statements
        2. What Are Variables?
        3. Data Types
        4. How to Create Variables
        5. How to Declare Variables
        6. Specifying the Data Type of a Variable
        7. Assigning Values to Variables
        8. Forcing Declaration of Variables
        9. Understanding the Scope of Variables
        10. Lifetime of Variables
        11. Understanding and Using Static Variables
        12. Declaring and Using Object Variables
        13. Finding a Variable Definition
        14. Using Constants in VBA Procedures
        15. Chapter Summary
      5. Chapter 5: VBA Procedures: Subroutines and Functions
        1. About Function Procedures
        2. Passing Arguments
        3. Locating Built-in Functions
        4. Using Master Procedures and Subprocedures
        5. Chapter Summary
    5. Part II: Controlling Program Execution
      1. Chapter 6: Decision Making with VBA
        1. Relational and Logical Operators
        2. If …Then Statement
        3. Decisions Based on More Than One Condition
        4. If Block Instructions and Indenting
        5. If…Then…Else Statement
        6. If…Then…ElseIf Statement
        7. Nested If…Then Statements
        8. Select Case Statement
        9. Chapter Summary
      2. Chapter 7: Repeating Actions in VBA
        1. Do Loops: Do...While and Do...Until
        2. Watching a Procedure Execute
        3. While...Wend Loop
        4. For...Next Loop
        5. For Each...Next Loop
        6. Exiting Loops Early
        7. Nested Loops
        8. Chapter Summary
    6. Part III: Keeping Track of Multiple Values
      1. Chapter 8: Working with Arrays
        1. What Is an Array?
        2. Declaring Arrays
        3. Array Upper and Lower Bounds
        4. Using Arrays in VBA Procedures
        5. Arrays and Looping Statements
        6. Using a Two-Dimensional Array
        7. Static and Dynamic Arrays
        8. Array Functions
        9. Errors in Arrays
        10. Parameter Arrays
        11. Chapter Summary
      2. Chapter 9: Working with Collections and Class Modules
        1. Working with Collections
        2. Creating a Class
        3. Chapter Summary
    7. Part IV: Error Handling and Debugging
      1. VBE Tools for Testing and Debugging
        1. Testing VBA Procedures
        2. Using the Watch Window
        3. Using Quick Watch
        4. Using the Locals Window and the Call Stack Dialog Box
        5. Stepping through VBA Procedures
        6. Chapter Summary
      2. Chapter 11: Conditional Compilation and Error Trapping
        1. Understanding and Using Conditional Compilation
        2. Navigating with Bookmarks
        3. Trapping Errors
        4. Chapter Summary
    8. Part V: Manipulating Files and Folders with VBA
      1. Chapter 12: File and Folder Manipulation with VBA
        1. Manipulating Files and Folders
        2. Chapter Summary
      2. Chapter 13: File and Folder Manipulation with Windows Script Host (WSH)
        1. Finding Information about Files with WSH
        2. Creating a Text File Using WSH
        3. Performing Other Operations with WSH
        4. Chapter Summary
      3. Chapter 14: Using Low-Level File Access
        1. File Access Types
        2. Working with Sequential Files
        3. Working with Random Access Files
        4. Working with Binary Files
        5. Chapter Summary
    9. Part VI: Controlling Other Applications with VBA
      1. Chapter 15: Using Excel VBA to Interact with Other Applications
        1. Launching Applications
        2. Moving between Applications
        3. Controlling Another Application
        4. Other Methods of Controlling Applications
        5. Creating Automation Objects
        6. Chapter Summary
      2. Chapter 16: Using Excel with Microsoft Access
        1. Object Libraries
        2. Connecting to Access
        3. Opening an Access Database
        4. Performing Access Tasks from Excel
        5. Retrieving Access Data into an Excel Worksheet
        6. Transferring the Excel Spreadsheet to an Access Database
        7. Chapter Summary
    10. Part VII: Enhancing the User Experience
      1. Chapter 17: Event-Driven Programming
        1. Introduction to Event Procedures
        2. Writing Your First Event Procedure
        3. Enabling and Disabling Events
        4. Event Sequences
        5. Worksheet Events
        6. Workbook Events
        7. PivotTable Events
        8. Chart Events
        9. Events Recognized by the Application Object
        10. Query Table Events
        11. Chapter Summary
      2. Chapter 18: Using Dialog Boxes
        1. Excel Dialog Boxes
        2. File Open and File Save As Dialog Boxes
        3. GetOpenFilename and GetSaveAsFilename Methods
        4. Chapter Summary
      3. Chapter 19: Creating Custom Forms
        1. Creating Forms
        2. Tools for Creating User Forms
        3. Placing Controls on a Form
        4. Sample Application 1: Info Survey
        5. Sample Application 2: Students and Exams
        6. Chapter Summary
      4. Chapter 20: Formatting Worksheets with VBA
        1. Performing Basic Formatting Tasks with VBA
        2. Performing Advanced Formatting Tasks with VBA
        3. Chapter Summary
      5. Chapter 21: Shortcut Menu Programming and Ribbon Customizations
        1. Working with Shortcut Menus
        2. A Quick Overview of the Ribbon Interface
        3. Ribbon Programming with XML and VBA
        4. Customizing the Microsoft Office Button Menu
        5. Customizing the Quick Access Toolbar (QAT)
        6. Chapter Summary
    11. Part VIII: Programming Excel Special Features
      1. Chapter 22: Printing and Sending E-mail from Excel
        1. Controlling the Page Setup
        2. Previewing a Worksheet
        3. Changing the Active Printer
        4. Printing a Worksheet
        5. Disabling Printing and Print Previewing
        6. Using Printing Events
        7. Sending E-mail from Excel
        8. Chapter Summary
      2. Chapter 23: Programming PivotTables and PivotCharts
        1. Creating a PivotTable Report
        2. Removing PivotTable Detail Worksheets with VBA
        3. Creating a PivotTable Report Programmatically
        4. Creating a PivotTable Report from an Access Database
        5. Formatting, Grouping, and Sorting a PivotTable Report
        6. Hiding Items in a PivotTable
        7. Adding Calculated Fields and Items to a PivotTable
        8. Creating a PivotChart Report Using VBA
        9. Chapter Summary
      3. Chapter 24: Using and Programming Excel Tables
        1. Understanding Excel Tables
        2. Creating a Table
        3. Creating a Table Using VBA
        4. Understanding Column Headings in the Table
        5. Multiple Tables in a Worksheet
        6. Working with the Excel ListObject
        7. Deleting Worksheet Tables
        8. Chapter Summary
      4. Chapter 25: Programming Special Features
        1. Tab Object
        2. Speech Object
        3. SpellingOptions Object
        4. CellFormat Object
        5. Characters Object
        6. AutoCorrect Object
        7. FormatCondition Object
        8. Graphic Object
        9. CustomProperty Object
        10. Sort Object
        11. Excel8CompatibilityMode Property
        12. Chapter Summary
      5. Chapter 26: Programming the Visual Basic Editor (VBE)
        1. The Visual Basic Editor Object Model
        2. Understanding the VBE Objects
        3. Accessing the VBA Project
        4. Finding Information about a VBA Project
        5. Working with Modules
        6. Working with Procedures
        7. Working with UserForms
        8. Working with References
        9. Working with Windows
        10. Working with VBE Menus and Toolbars
        11. Chapter Summary
    12. Part IX: Excel and Web Technologies
      1. Chapter 27: HTML Programming and Web Queries
        1. Creating Hyperlinks Using VBA
        2. Creating and Publishing HTML Files Using VBA
        3. Web Queries
        4. Refreshing Data
        5. Chapter Summary
      2. Chapter 28: Excel and Active Server Pages
        1. The ASP Object Model
        2. HTML and VBScript
        3. Creating an ASP Page
        4. Installing Internet Information Services (IIS)
        5. Creating a Virtual Directory
        6. Running Your First ASP Script
        7. Sending Data from an HTML Form to an Excel Workbook
        8. Sending Excel Data to the Internet Browser
        9. Creating Charts in ASP
        10. Chapter Summary
      3. Chapter 29: Using XML in Excel 2007
        1. What Is XML?
        2. Well-Formed XML Documents
        3. Validating XML Documents
        4. Editing and Viewing an XML Document
        5. Opening an XML Document in Excel
        6. Working with XML Maps
        7. Working with XML Tables
        8. Exporting an XML Table
        9. XML Export Precautions
        10. Validating XML Data
        11. Programming XML Maps
        12. Creating XML Schema Files
        13. Using XML Events
        14. The XML Document Object Model
        15. Working with XML Document Nodes
        16. Retrieving Information from Element Nodes
        17. XML via ADO
        18. Understanding Namespaces
        19. Understanding Open XML Files
        20. Chapter Summary
      4. Chapter 30: Using and Programming Smart Tags
        1. Checking Smart Tag Options in an Excel Workbook
        2. Creating Your First Simple Smart Tag
        3. Storing Smart Tag List Definition Files
        4. Using Custom Smart Tags
        5. Information Lookups via ASP Pages
        6. Dynamic Recognition of Smart Tag Terms
        7. Manipulating Smart Tags with VBA
        8. Chapter Summary
    13. Index

Product information

  • Title: Excel 2007 VBA Programming with XML and ASP
  • Author(s): Julitta Korol
  • Release date: December 2008
  • Publisher(s): Jones & Bartlett Learning
  • ISBN: 9780763782764