More Pie for Everyone

So we've created a pie chart and modified some of its properties to make the data displayed more meaningful. We've got quite a few categories on our Sales By Category worksheet. Can we use what we've learned and the code we've created to generate charts for the remaining categories Of course Excel does not always place charts in the most appropriate place on a worksheet, so before we begin, let's be sure to move the Baked Goods & Mixes chart to the right of the data range on the Sales By...

Recording a Macro

In an Excel workbook, open the Developer ribbon and choose the Record Macro command to display the Record Macro dialog box, shown in Figure 1-18. The Record Macro dialog will display. The dialog box shows the default macro name, allows you to assign a shortcut key, lets you choose where to store the macro, and provides a text field where you can enter text describing the macro's function. By default, Excel 2007 stores macros in the current workbook. If you want your macros to be available to...

Retrieving Data Using NET

In this example, we'll reach out to the Northwind 2007 database and populate a worksheet with data from the Employees table. The end result will look a lot like one of our early data access samples in Chapter 2, where we imported data from the Northwind database and then resized the columns using the AutoFit command. We will create a data access component and then use it from within our add-in. 1. Open Visual Studio 2005, and the start page will appear. 2. In the Recent Projects area (shown in...

Relationships

An Excel 2007 document is made of parts. These parts are joined together via relationships as defined in the various XML files we've seen. The connection between a data table and the worksheet it resides in is defined by a relationship. Our root folder contains a _rels folder that contains a .rels file. This file defines relationships between our document properties files, app.xml and core.xml, and the xl workbook.xml file. The xl folder itself contains a _rels folder that has a relationship...

Contents

About the About the Technical chapter 1 The Macro Recorder and Code Modules 1 Macro Security Trusted Trusted The Remove Lowering the Security The Visual Basic Development The Immediate The Locals The Watch Recording a Formatting the Adding Same Task, Different Writing a Macro in the More Macro The Object Object Browser Window Standard Code Type Class Sample Class and The Class-y Way of Toolbox Window Object-Oriented Programming An Overview 39 OOP Is It Worth the Extra chapter 2 Data In, Data...

Creating the XML File That Contains the Markup to Modify the UI

Open a new file in the text editor of your choice, and save it as customUI.xml in the customUI folder. 3. Add the following code to the customUI.xml file < customUI < ribbon startFromScratch true> < tabs> < tab id DataFunctions label XML Data Functions' < group id NewDataControls label New Data> < button id Button1 size large label Get Emps Dept > < button id Button2 size large label Get HR Info > < group id AppendDataControls label...

Object Oriented Programming An Overview

I decided early on in the process of writing this book that I would concentrate my efforts on providing guidance in the creation of class-based solutions to Excel VBA coding problems where possible. I have found through programming in Access, VB 6, and then VB.NET that using OOP techniques has helped me visualize my applications more clearly, helped me to better organize my code, and has been invaluable in making my code easier to maintain. Standard code modules and procedural programming allow...

Writing a Macro in the VBE

In this example, you'll create a macro by typing code directly in the VBE. Open the file 1-MacroExample01.xlsx shown in Figure 1-21 , and open the VBE. Note You will find all the example files and source code for this book at www.apress.com in the Downloads section of this book's home page. Figure 1-21. Sales data for the first quarter of the year Figure 1-21. Sales data for the first quarter of the year We see tour sales for the fictitious band VBA, which are received quarterly by their...

Putting Data into a Pivot Table Report

In the Download section for this book on the Apress web site, find the file named PivotTable01.xlsx and open it. Remember our fictitious band VBA from Chapter 1 Well, they've been out touring and their manager wants to see what's selling and what's not, and where items are selling best. PivotTable01.xlsx contains sales data from the first quarter of their tour, as shown in Figure 6-1. A good way for the manager to look at this data is via an Excel 2007 PivotTable report. We're going to record a...

Macro Recorder Generated Text Import Code

After we stop the Macro Recorder, we are left with code that looks like Listing 2-2. Listing 2-2. Macro-Generated Text Data Import Code Sub GetTextData 2 files maillist.csv, .TextFileTextQualifier xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter False .TextFileColumnDataTypes Array 2, 2, 2 .TextFileTrailingMinusNumbers True Application.Goto Reference maillist Range A1 .Select End Sub One of the first differences to notice about this code when compared to the Access data import is how...

Blank Data Records

To see the effect of blank records on a PivotTable report, let's make Sheetl active and remove the data for Reading, PA's tank top sales. The Quantity and Sales Total values are 0, but we want to make them blank as though no data were added as shown in Figure 6-15 . Figure 6-15. Blank data for Reading, PA tank top sales Figure 6-15. Blank data for Reading, PA tank top sales 1. Activate the worksheet containing the PivotTable report. 2. Refresh the data either through the UI or the...

Of Excel Data and Object Orientation

Earlier in this book, I promised that we'd see object-oriented solutions to our coding problems in Excel 2007. Let's take our manager list-creation code and the code that lists a manager's staff, and convert them to classes. Normally, this is the way I would directly approach a solution, but up to this point we've been exploring some of the VBA possibilities in Excel 2007. Open DataAccessSample04.xlsm and save it as DataAccessSample06.xlsm. Open Modulel in the VBE and review the GetManagerList...

Using the cExcel Setup and cData Objects

Now that we've created the objects we need, let's put them to use in client code. In the VBE, add a new standard module and name it basManagers. Add two module-level variables to hold our cExcelSetup and cData objects Dim m_cData As cData Dim m_cXL As cExcelSetup These are placed at module level in case we need to use the objects across function calls. Create a new subroutine and name it GetManagers. Add the following code Dim sConnString As String Dim sSQL As String Set m_cXL New cExcelSetup...

Creating a Custom Task Pane and Data Input Form Using NET

Create Custom Input Forms Powershell

In this example, we'll look at a .NET project that creates a custom task pane and a simulated Excel UserForm. A task pane is a window that anchors itself to the right of an Office application and contains commands to perform various functions. A common Office task pane is the Getting Started task pane, shown in Figure 9-21. Figure 9-21. Getting Started task pane in Excel 2003 Figure 9-21. Getting Started task pane in Excel 2003 Our example task pane will contain commands used by a human...

Appending XML Data

Both the Excel Workbook object and the XmlMaps collection contain methods for appending or overwriting XML data in a workbook. The Workbook.XmlImport method and the Xmlmaps. Import method provide functionality to do either. Both methods take Overwrite arguments, which when set to False will append data to any existing data. The XmlImport method, however, will not append data when Overwrite is set to False if the optional Destination argument is used. In this case, nothing will happen the append...

C

CAccess class module adding to project, 161 code for, 168 cAddress class module adding to project, 161 code for, 165 call stack feature, 270-271 Call Stack window, opening, 270 Cancel button, coding, 348 Cancel command button, code for Userform1, 147 Candy chart, testing code for, 220-221 cCustSurvey class module, 140-142 coding into UserForm, 143-150 creating, 139-143 creating Save method in, 142-143 validation results, 148 cData class, code from ActiveX component, 318-319 cData class module...

ADO Example Importing SQL Data Based on a Selection

In this exercise, we'll see how we can use Excel to generate a list, and how by making a selection from that list we can view detailed information about the selected item. Adventure Works management wants to see a quick view of their reporting tree by manager. We're going to create a list of managers and then add code that will show the selected manager's reporting structure. On Modulel, add a new subroutine and name it GetManagerList. Add the following variable declarations Dim cnn As...

Laying Out the Wizard Form

Open the VBE and add a new UserForm. 2. Set the form's height to 320 and its width to 332. 3. Rename the form to HRWizard. 4. Add a Label to the top of the form, set its caption property to MyCompany - HR Wizard, and set the font to a large size like 18 pt. This will be the main heading for 5. Add a MultiPage control to the form. 6. Set its Height property to 216 and its Width property to 270. 7. Center it on the form, leaving room at the bottom. Your form in Design view should look...

User Forms

Excel provides us with UserForms as a means to provide a user interface UI to our Excel applications. UserForms are similar to Access or Visual Basic forms. They are containers for input and display controls. Both the forms and controls have properties, methods, and events that we can code against. Excel names new forms UserForm1, UserForm2, and so on, as they are added. They can be renamed as needed. UserForms are inserted into your project by choosing Insert gt UserForm or by right-clicking...

Summary

In Chapter 1, you looked at the Excel 2007 Macro Recorder and learned how to use it to let Excel generate code for you. This is useful when learning the Excel 2007 object model, and it can also be used to generate base code that you can then edit to suit your purpose. You worked with the Excel Visual Basic Editor VBE , where you created macros to enter and format data. The VBE is similar to the VB 6 code editor, and includes many of the same tools for debugging your code, such the Immediate...

Coding the User Form

Userform Excel Erstellen

We just created a class to handle our data and modified our Excel Utility class to help the cCustSurvey class. Let's put cCustSurvey to work by coding it into our UserForm. Open the Customer Survey form UserForm1 . Open the code view by clicking the View Code button on the Project Explorer toolbar, as shown in Figure 4-7. Figure 4-7. The View Code button displays the code window for UserForm1 selected . Add the following module-level variables in the UserForm code window Private m_oCustSurvey...

Note The path to the XML file will vary based on where you are storing the files that came with this book

In the GetXMLData subroutine, change the name of the XML file we're opening to cd.xml. The subroutine should look like Listing 7-8 now. Listing 7-8. GetXMLData Procedure After Modification Sub GetXMLData ActiveWorkbook.XmlImport URL C Chapter 3 files cd.xml, ImportMap Nothing, Overwrite True, Destination Range A 1 End Sub 5. In Excel, make Worksheetl the active sheet. 6. From the Macro dialog box, run the GetXMLData procedure. We get a very ugly error, as shown in Figure 7-38. Run-time error...

The Watch Window

The Watch window is a tool that allows you to set conditions on which you can put your code into break mode. The Watch window, shown in Figure 7-33, has a few options as to how to handle watched values. You enter an expression in the Expression text box, and then select the context of the expression from the Procedure and Module drop-down lists. Then you select a watch type. Watch types are described in Table 7-3. Watch Expression Code execution will not be stopped. In break mode, the values of...

Class Modules

If you've done any amount ofVBA or VB coding, you have more than likely used objects in your code. Any time you've gone out to a database and retrieved records using ADO, you may have declared and instantiated a variable like this Dim rs As ADODB.Recordset Set rs New ADODB.Recordset Some of the examples you've seen thus far have also used some of Excel's built-in objects, like the Selection object, which has a Font property, or the Range object, which has many properties and methods you can use...

The Application Folder

The next folder to explore is the application folder, whose name will reflect the program used to create the file. Ours is named xl for Excel , as displayed in Figure 3-24. The application folder contains application-specific document files. Some of these files are found in their own folder, but the root contains the workbook part and the sharedStrings data part.

The [Content Typesxml File

Another file of interest is the Content_Types .xml file found in the root folder we created unzippedExcelfiles . It lists the content types for the other parts included in the Excel file package. Content types are the types of parts that can be included in a package. Following is a list of the content types that can be found in an Office document

Using an ActiveX Component in Excel

Our ActiveX example allows us to put data on the worksheet of our choice in our current workbook and pass in a SQL statement to retrieve whatever data we need. This example will mimic the functionality of our ADO example from Chapter 2 in the file DataAccessSample03.xlsm. Listing 9-1 shows the original VBA code we wrote in Chapter 2. Listing 9-1. ADOTest Macro from Chapter 2 Sub ADOTest Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset Dim xlSheet As Worksheet Dim sConnString As String...

The rels Folder

The _rels folder is the first place you should look to see what your Excel file is made of. The _rels folder contains a file named .rels. This file contains the top-level relationships. In your file, they should look like Figure 3-23. i xml version 1.0 encoding UTF-8 standalone yes gt lt R amp lationships xm lt Relationship Id rld3 Type Target-'docProps app.xml . i Relationship Id rld2 Type- Target-'docPraps core.xmi gt lt RGlatlonshlp Id rldl Type-1 Target xl workbook.xml gt This file contains...

Creating a Simple Data Entry Form

Let's create a form for quick data entry for a call center doing a one-minute customer contact. The user's task is to call the customer and find out if they've heard of the Widget and whether they're interested in finding out more about the product. If the customer is interested, the user will mark that on the form as well so sales can follow up. Note The example files and source code for this book are available to readers atwww.apress.comin the Downloads section of this book's home page.

Macro Security Settings

Excel's default security settings do not allow any macro activity. Before you begin exploring macros in Excel and the Macro Recorder, you will need to tell Excel which security settings to use to control what happens when you open a workbook that contains macros or one that will contain macros . If you use antivirus software that works with Microsoft Office 2007 and you open a workbook that contains macros, the virus scanner will check the workbook for viruses before opening it. You can make...

ADO Example Importing SQL Data

For our first ADO example, we're going to use the AdventureWorks sample database provided by Microsoft. You can install a copy of the AdventureWorks database by running the file AdventureWorksDB.msi. You will be using SQL Server 2005 Management Studio Express to view the various database objects. To install Management Studio Express, run S0LServer2005_SSMSEE.msi. 1. Open a new workbook and name it DataAccessSample04.xlsm. 2. Before we begin using ADO in Excel 2007, we must add a reference to...

Adding Formatting to a Pivot Table Report

The default PivotTable report Excel generates looks okay, but Excel 2007 does provide us with 75 different formatting options. To change the look of a PivotTable report using VBA code, use the PivotTable object's TableStyle2 property. This property is named TableStyle2 because there is already a TableStyle property but it's not a member of the PivotTable object's properties go figure . Add a new subroutine to a standard code module and add the following code PivotStyleLight1 PivotStyleLight22...

Changing Field Names

By default, Excel uses the name Sum of field name or Count of field name when you add summary value fields to a PivotTable. You can change the names to something with more visual appeal using VBA code. Add the Amount field to the Values list in the PivotTable Field List. Change the Count value to Sum in the Value Field Settings dialog box as shown in Figure 6-21 by clicking the Amount field in the Values list and choosing Value Field Settings from the right-click shortcut menu. Figure 6-22...

User Form Toolbox Controls

Resizes or moves a control on a form. Holds text that is not editable except through code. Holds text that users can enter or modify. CommandButton TabStrip MultiPage ScrollBar A combination of a list box and a text box. Users can choose an item from a list or enter a value in the text box. Displays a list of items from which users can choose. Presents multiple choices, of which only one can be selected. A grouping for controls such as option buttons or check boxes. Users can only select one of...

Looking at the Code

Let's take a look at the code we've generated so far. The MakeBeverageSalesChart macro created a 3-D bar chart for us using a data range we selected. The ChartByRow macro switched the data orientation of the chart from the default, column, to row. Sub MakeBeverageSalesChart ' MakeBeverageSalesChart Macro Range A1 E7 .Select ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source Range _ 'Monthly Total Sales Amount' A 1 E 7 ActiveChart.ChartType xl3DColumnClustered End Sub The first...

Using DAO in Excel

Data Access Objects or DAO, as it's commonly known has been around Microsoft Office for many versions, going back to 1992, when Jet was introduced. DAO was the first data access tool available to VB and VBA programmers, and can still be used to manipulate data in older versions of Office and ODBC-compliant database systems. DAO is very easy to use, and you've probably encountered DAO code if you've done any work in versions of Access preceding the 2000 release, when it was the default data...

Standard Code Modules

A standard module is a code module containing only procedure Sub or Function , type, and data declarations and definitions. Module-level declarations and definitions in a standard module are public by default. In earlier versions ofVisual Basic, a standard module was referred to as a code module. Whenever a new macro is created in an Excel session, a standard module is inserted into the workbook to hold the macro. Any additional macros created in that session will also be inserted into this...

Using ADO in Excel

ActiveX Data Objects ADO was introduced by Microsoft in 1996 and has become the successor to DAO. Its database access technology is OLE DB Object Linking and Embedding Database , which is the successor to ODBC. The latest version of ADO is ADO 2.8. ADO lets us access, edit, and update data from many data sources by interfacing to these data sources via OLE DB providers. OLE DB providers speak to the database engine more directly than ODBC, and provide us with better performance. In the examples...

Simplifying the Code

The code Excel generates, while accurate, is certainly not something one would want to maintain. And you can forget about flexibility. The Array function used to pass in the connection string and database information is one scary looking piece of code. One of the first things we can do to simplify this is to create our own connection string and store it in a variable. This will give us the advantage of easier maintenance. Create a new function in Module1 and name it GetAccessData2. Paste the...

Creating the Pie Chart

In this example, we are going to create a pie chart based on the data for one product category. The chart will show the monthly sales for the category. Then we'll explore options to reuse the code and automate the creation of pie charts for each product line. 1. Select the Sales By Category worksheet. 2. Create a new macro and name it MakePieChart. 3. Select the data range that contains the data for the Baked Goods amp Mixes category A2 C5 , as shown in Figure 5-18. Figure 5-18. Selection for...

Changing the Number Format

The default number format in a new PivotTable is Excel's general number format. Most of us like to see commas or currency symbols, which make the data more readable. To change the number format, you use the PivotField.NumberFormat property. The NumberFormat property sets or returns the string value that represents the format code for the numeric value. The format code is the same string value given by the Format Codes option in the Format Cells dialog box shown in Figure 6-19. Figure 6-19. The...

Creating a Report in Word

In Chapter 5, we explored charting in Excel 2007. We are going to turn one of our chart reports into a summary report using the Word 2007 DOM from the Excel 2007 VBE. In this example, you'll learn how to do the following Open an instance of Microsoft Word programmatically Create a new document within the instance of Word Insert chart objects from Excel Let's start by opening an existing Excel project that contains numeric and chart data. We'll use the charts in our summary report in Word. 1....

Importing Access Data

Let's look at how Excel brings in external data by recording a quick macro to import data from an Access database. Create a new macro named GetAccessData. We're going to import the Extended Employees list query from the Northwind 2007 database onto Sheet1 in a new Excel workbook. 2. Select From Access from the Get External Data section of the Data ribbon. 3. Navigate to wherever you have the Northwind database stored. Note The files for these examples can be found in the Source Code Download...

MyCompany HR Wizard

Department combo box bound to named range 3. Stop running the form by clicking the X button. Our navigation buttons have the task of moving us from step to step in our wizard application. But they also need the ability to put the data from each screen into its place in the UserForm's cPerson object. Add the following code to the cmdNext_Click event Private Sub cmdNext_Click Dim iNext As Integer StoreData Me.MultiPage1.Value m_colSteps CStr iNext .Order - 1 - 1 .Caption The first...

Saving XML Data

Saving your data back to the existing XML file or a new file is as simple as a call to the Workbook object's SaveAsXMLData method. The SaveAsXMLData method takes two arguments, the file name to save to and the XML map object to retrieve the file schema from. Figure 3-14 shows the SaveAsXMLData method displayed in the Visual Basic Immediate window with its arguments shown via IntelliSense. SaveAsXMLData R7eft3ffje s String. Map A s Xrn IM a p Add a new procedure to the standard module you've...

Getting Started

As with many of the previous features we've explored, we'll manually create a few different charts and record macros to take a look at some of the chart object properties and methods. Then we'll write our own code to create charts for our users. 1. In the Download section for this book on the Apress web site, find the file named Chart01.xlsx and open the workbook. 2. Since we know we'll be inserting code into this workbook, let's save it in the macro-enabled format, as Chart01.xlsm. 3. Activate...

Importing XML in Excel

Importing XML data into an Excel workbook is a fairly straightforward process. The Workbook object has a method called XmlImport that does the work for us. Open a new workbook and save it with an .xlsm format file extension. Open the VBE, add a standard module, and create a new function named GetXMLData Sub GetXMLData ActiveWorkbook.XmlImport URL ImportMap Nothing, Overwrite True, Destination Range A 1 End Sub Caution Be sure to adjust the file path to reflect your location for the XML file....

Managing the Wizard

We'll build two classes to help us manage our wizard application. The first is a very simple class that will hold configuration data for each step. Then we'll create a class that will hold a collection of these wizard step objects. This class will manage the operation of the wizard process for us. Insert a new class module and name it cStep. Add the following code Private m_iOrder As Integer Private m_iPage As Integer Private m_sCaption As String Public Property Get Order As Integer Public...

Importing Text Data

Before we begin writing our own code to import data, let's record one more macro to see some of the settings available when we bring in data from a text file. 1. Create a new workbook and name it DataAccessSample02.xlsm. 2. Create a new macro and name it GetTextData. 3. On the Data ribbon, choose From Text. 4. Navigate to the myfilepath maillist.csv file, and then choose the Import command. The Text Import wizard will open, as shown in Figure 2-3. The file is comma-delimited the default...

Power Point Helper Functions

Our next chore is to create a title slide. The title slide will consist of a title line plus descriptive text, similar to the Word report we created in the last example. 1. Still working on the standard code module with your PowerPoint code, add a new subroutine and name it CreateTitleSlide. 2. Add the following line of code Set m_oPptSlide m_oPptShow.Slides.Add 1, ppLayoutTitle The PowerPoint Presentation object contains a Slides collection that naturally contains all of the slides in a...

DAO Example Importing Access Data Using

Example Access Worksheet

Open a new workbook and name it DataAccessSample03.xlsm. Be sure to use the .xlsm extension so your workbook is macro-enabled. Open the VBE by choosing the Visual Basic command from the Developer ribbon or by pressing Alt F11. Before we can retrieve data using DAO, we must add a reference to the DAO library in our project. 1. Select Tools gt References in the VBE. 2. Find the Microsoft DAO 3.6 Object library in the list, and select it, as shown in Figure 2-12. 4. Insert a new standard module by...

Using the Debug Object

The Debug object contains two methods that we can use to debug our code Print and Assert. The Print method directs output to the Immediate window, and the Assert method lets us set a condition that puts our code in break mode if the condition fails. Whereas the MsgBoxfunction interrupts the execution of our code, the Debug.Print method allows the code to run through to finish, sending its output to the Immediate window. This is especially useful when debugging code in a loop. 1. As a very...

Refreshing Data in an Existing Pivot Table Report

How do we handle keeping our data fresh in a PivotTable When rows are modified, added, or deleted, how do we pass that on to our PivotTable reports If our data had come from an external source like an Access or SQL Server database, refreshing the data would be as simple as running the following command with the PivotTable activated How do we handle updating our PivotTable data when the data does not sit in a DBMS If the data on Sheet1 in our example is modified, how do we refresh the PivotTable...

The Macro Code

Listing 6-1 shows the code the Macro Recorder generated for us. Listing 6-1. MakePivotTable Macro Code Sub MakePivotTable SourceData Sheet1 R1Q R43C6, TableName PivotTable1, DefaultVersion xlPivotTableVersion12 Sheets Sheet4 .Select Cells 3, 1 .Select With .Orientation xlRowField .Position 1 End With With .Orientation xlColumnField .Position 1 End With ActiveSheet.PivotTables w Sum of Qty, xlSum With .Orientation xlRowField .Position 2 End With End Sub The first thing the code does is add a new...

Adding Text to the Chart Slides

In this example, we'll use a different slide template for our text and chart. Figure 8-14 shows the empty template slide in PowerPoint. Figure 8-14. PowerPoint slide template for text and chart Let's begin coding the CreateChartSlidesText procedure. In it we will add the title text in the title placeholder. We'll put our descriptive text in the text placeholder on the left side of the slide. Finally, we'll place our chart in the chart placeholder on the right side of the slide. 1. Open the VBE...

DAO Example Importing SQL Data Using ODBC

The final example of using DAO to bring data into your Excel project will focus on getting data from an SQL server or other ODBC-compliant database . The process is identical to what we just did in our previous example, with the exception of a new connection string sConn We're still using the ODBC reference in the string, but now we're passing in the database name and the DSN name. Here's the complete code. You must reference a valid database and DSN for this to provide you with output. Sub...

Building an XML Data Class

Now that we've got an idea of what XML data services Excel provides us, let's build a class that will give us an easy-to-use interface to this functionality. Before we begin, though, let's think about what we'd like our class to do We want it to bring in data from various XML data sources. We want the ability to refresh the data in case the source file is updated either manually or through an automated process . We want to append data from another file that conforms to the same XML schema. We...