Excel Ebook

101 Secrets of a Microsoft Excel Addict

This ebook from Francis Hayes gives you professional tools to help you get the most out of your Excel program. Any one of these secrets could be the only one that you ever need to know for Excel, but this ebook includes bunches of those tips and tricks! Just think of all of the useful information you can get from it! If you have ever been frustrated at your lack of progress in Microsoft Excel, this guide will teach you everything that you need to know to harness the powerful functions or time-saving elements of Microsoft Excel. Excel is used by offices all over the world, but so few people take the time to teach you anything important about it. Too much time is usually wasted searching the internet for tips on how to use it more efficiently Learn the best way to master Excel in this ebook!

101 Secrets of a Microsoft Excel Addict Summary


4.6 stars out of 11 votes

Contents: Ebook
Author: Francis Hayes
Price: $9.97

My 101 Secrets of a Microsoft Excel Addict Review

Highly Recommended

Recently several visitors of blog have asked me about this ebook, which is being advertised quite widely across the Internet. So I decided to buy a copy myself to find out what all the excitement was about.

All the modules inside this ebook are very detailed and explanatory, there is nothing as comprehensive as this guide.

Download Now

VBA Programming in Excel

Working with Excel-related objects Accessing and using Excel objects Changing the contents of individual cells Creating your own functions to use in Excel Adding pizzazz to your Excel worksheet Defining your own special-purpose worksheets icrosoft Excel provides a wealth of opportunities for custom VBA programs. You can do everything from creating special equations to designing eye-grabbing presentations. With some effort, you can create detailed graphs and charts on the fly. It's also possible to design self-checking worksheets, where a press of a button verifies the data you entered. Of all the Office products, the third-party add-in product market for Excel is the largest and includes a vast array of product types, including add-ins that you can use with your VBA programs. Although you probably wouldn't want to write War and Peace with Excel, products are available that you can use to turn it into a simple word processor. Many products provide advanced math modeling and other...

Understanding the Excel Related Objects

Excel relies on some essential objects to provide access to the various data elements. Because of the nature of worksheets, you'll find that each of these objects can actually perform multiple duties. For example, you don't have to access a worksheet through a Workbook object unless you don't know the name of the worksheet. Most objects also include links to objects above and below the current position in the hierarchy so that you can use the object of convenience to access a data element. This section focuses on Excel object issues. The demonstrations show how the various objects interact and what you need to consider while you design and build your Excel program. For example, you need to consider where to add charts and graphs while you build them so that they're most convenient in displaying the data.

Selecting Objects within Excel

You can place any object that you want in an Excel worksheet, including pictures and sounds. These kinds of objects work very much the same in Excel as they do in Word. (See the Selecting Objects in a Word Document section of Chapter 13.) The main difference is that you use the OLEObjects collection of the sheet that holds the object. However, Excel can also embed Chart objects in a worksheet. Because this is such a special feature, I show you in this section how to work with embedded Chart objects. The same data can say different things depending on how you present it. A pie chart tells the viewer about parts of a whole, and a bar chart compares individual values. The problem with charts that you create in Excel is that they're static they continue to say the same thing unless you redesign them. Fortunately, you can control the appearance of an embedded chart just as easily as you can control a standalone chart. The code in Listing 14-7

Sending Notes from Word to Excel

You might find the need to create a utility simply because a task becomes unwieldy in the host program. For example, many of my worksheets contain extensive notes. Editing those notes in Excel can become a real problem after the note gets to a certain size. However, Word provides a great editor, and I can make the notes as long as needed. The example shown in Listing 16-7 includes part of a program that I created for moving notes between Word and Excel so that they're easier to edit. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.)

Displaying Excels Builtin Dialog Boxes

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box although Excel doesn't actually display the dialog box. In some cases, however, you may want your code to simply display one of Excel's many built-in dialog boxes and let the user make the choices in the dialog box. You can do this by using the Application object's Dialogs property. Here's an example The preceding example uses the predefined constant xlDialogFormulaGoto. This constant determines which dialog box Excel displays. To display Excel's Open dialog box, use this statement 2. In the Project Library drop-down list (the one at the upper-left corner of the Object Browser), select Excel. Unfortunately, these dialog box constants are not documented in the Help system. Therefore, you may need to use a bit of trial and error to figure out which is appropriate for your needs. You'll find that some Excel dialog boxes don't have a corresponding dialog...

Excel searches through the selected libraries to find the reference for the object type If the type is found in more

This example opens an existing Word document from Excel. The declared variables, wdApp and wdDoc, are of Word object types. wdApp is used to create a reference to the Word Application in the same way the Application object is used in Excel. New Word.Application is used to create a new instance of Word.

Changing Excel Settings

Some of the most useful macros are simple procedures that change one or more of Excel's settings. For example, simply changing the recalculation mode from automatic to manual requires numerous steps. You can save yourself some keystrokes and menu choices (not to mention time) by creating a macro that automates this task. This section presents two examples that show you how to change settings in Excel. You can apply the general principles demonstrated by these examples to other operations that change settings.

When the Normal Excel Menus Arent Good Enough

Ou may not realize it, but you can change almost every aspect of Excel's menus. Typical Excel users get by just fine with the standard menus. Because you're reading this book, however, you're probably not the typical Excel user. In this chapter I describe how to make changes to the Excel menu system. Most of the Excel applications you develop get along just fine with the standard menu system. In some cases, however, you may want to add a new menu to make it easier to run your VBA macros. In other cases, you may want to remove some menu items to prevent users from accessing certain features. If these sorts of changes seem useful, you should read this chapter. Otherwise, you can safely skip it until the need arises.

Excels Data Import Tools

Excel 2007 has a rich set of data handling tools. On the Developer ribbon, you'll find quick access to Microsoft Access databases, web tables, and text files. Excel also includes data access to SQL Server and OLAP databases, XML data, and ODBC data sources. By recording macros, you can see how Excel connects us to various data sources. From there, you'll begin writing your own data access routines.

Managed Code in an Excel Project

We are going to look at two examples of running managed code in Excel. In our ActiveX examples, we revisited some of the functionality we'd already built directly in Excel using VBA. The first example .NET project will duplicate some of the functionality from our previous VBA projects, but you'll see how the .NET versions require absolutely no code on the Excel client workbook. Our second example will show how to create a custom task pane in an Excel 2007 Add-in project in conjunction with the .NET version of an Excel UserForm.

Getting Started With Excel Macros

Chances are you do not want to keep the default smiley face button image that Excel inserts on the toolbar. You can change the image by using one of two different options on the menu that appears when you right-click the mouse over the toolbar button when you have the Customize dialog box open. If you want to select an image from a list of existing images, you can click the Change Button Image option. If you do not like the images on the Change Button Image menu, you can also create your own button image on the Button Editor dialog box. To change the button image, click the Edit Button Image option on the menu.

Managing the Excel Application

In the beginning of this chapter, you learned that objects are organized in a special structure called the object model. The topmost object in an application's object model is the application itself. By controlling the Application object, you can perform many tasks, such as saving the way your screen looks at the end of a day's work or quitting the application. As you know, Excel allows you to save the screen settings by using the Save Workspace option from the File menu. The task of saving the workspace can be easily performed with VBA The above instruction saves the screen settings in the workspace file named Project. The next time you need to work with the same files and arrangement of windows, simply open the Project file and Excel will bring up the correct files and restore your screen to your liking. Change the title of the Excel application to My Application. Change the title of the Excel application back to Microsoft Excel. Find out the name of the folder where Excel.exe...

Why Excel Is Great for Developers

Excel is a highly programmable product, and it's easily the best choice for developing spreadsheet-based applications. For developers, Excel's key features include the following Visual Basic for Applications This macro language lets you create structured programs directly in Excel. This book focuses on using VBA, which, as you'll discover, is extremely powerful and relatively easy to learn. Easy access to controls Excel makes it very easy to add controls such as buttons, list boxes, and option buttons to a worksheet. Implementing these controls often requires little or no macro programming. Customizable user interface Developers have lots of control over the user interface. In previous versions, this involved creating custom menus and toolbars. In Excel 2007, it involves modifying the Ribbon. Changing the Excel 2007 interface is not as easy as it was in previous versions, but it's still possible. Powerful data analysis options Excel's PivotTable feature makes it easy to summarize...

Excels Role in Microsofts Strategy

Currently, most copies of Excel are sold as part of Microsoft Office - a suite of products that includes a variety of other programs. (The exact programs that you get depend on which version of Office you buy.) Obviously, it helps if the programs can communicate well with each other. Microsoft is at the forefront of this trend. All the Office products have extremely similar user interfaces, and all support VBA. Therefore, after you hone your VBA skills in Excel, you'll be able to put them to good use in other applications - you just need to learn the object model for the other applications.

Displaying Excels Developer tab

The Excel 2007 Ribbon does not display the Developer tab by default. If you're going to be working with VBA, it's essential that you turn on the Developer tab 1. Choose Office Excel Options. 2. In the Excel Options dialog box, click the Popular tab. After you perform these steps, Excel displays a new tab, as shown in Figure 7-1.

Exploring Other Excel Objects

To go into detail on all of the objects available would fill many more pages than I have targeted for this book. Besides, you already have Excel's detailed help files available to you. Therefore, I believe the way I can provide added value to you is to help you focus on some of the objects that you're likely to use in the course of your normal development activities and give you good advice regarding how you use these objects. So far, I've covered the most important objects Application, Workbook, Worksheet, and Range. Now I need to show you some of the more common supporting objects.

Adding Host Items in Excel

A VSTO solution can contain only one workbook, and the workbook that is created in a VSTO solution is automatically created as a host item. When you add a worksheet or chartsheet to a workbook at design time when Excel is hosted inside Visual Studio, VSTO automatically creates the worksheet or chartsheet as a VSTO object (a host item). VSTO does not provide a way for you to create host controls dynamically. If you add a worksheet to the workbook programmatically by adding a sheet to the Worksheets collection, it is created as an Interop worksheet rather than a worksheet host item. Keep in mind that you will not have any of the VSTO-specific functionality such as data binding capabilities and additional events of a worksheet that is added programmatically in this way. You also cannot add a host control or Windows Forms control to an Interop worksheet. Only VSTO worksheets can host these types of controls.

Excel VBA Programmers Reference

This book is aimed squarely at Excel users who want to harness the power of the VBA language in their Excel applications. At all times, the VBA language is presented in the context of Excel, not just as a general application programming language. The Primer has been written for those who are new to VBA programming and the Excel object model. It introduces the VBA language and the features of the language that are common to all VBA applications. It explains the relationship between collections, objects, properties, methods, and events and shows how to relate these concepts to Excel through its object model. It also shows how to use the Visual Basic Editor and its multitude of tools, including how to obtain help. The middle section of the book takes the key objects in Excel and shows, through many practical examples, how to go about working with those objects. The techniques presented have been developed through the exchange of ideas of many talented Excel VBA programmers over many...

The Excel Object Model

The Visual Basic for Applications programming language is common across all the Microsoft Office applications. In addition to Excel, you can use VBA in Word, Access, PowerPoint, and Outlook. Once you learn it, you can apply it to any of these. However, to work with an application, you need to learn about the objects it contains. In Word, you deal with documents, paragraphs, and words. In Access, you deal with databases, recordsets, and fields. In Excel, you deal with workbooks, worksheets, and ranges. Unlike many programming languages, you don't have to create your own objects in Office VBA. Each application has a clearly defined set of objects that are arranged according to the relationships between them. This structure is referred to as the application's object model. This section is an introduction to the Excel object model, which is fully documented in Appendix A.

Excel and the Internet

Until a few years ago, a typical Excel-based application was almost entirely contained within Excel itself the only external interaction would be with the user, from whom you obtained data and to whom you presented results. If you needed to store data, you'd use separate workbooks and try to mimic a relational database as best you could. As data access technologies developed, from ODBC drivers through DAO to the current versions of ADO (documented in Chapter 20), it became more commonplace to store data in external databases and retrieve data from (and update data in) other systems across the network. It is now quite common to see Excel used as a front-end querying and analysis tool for large corporate databases, using QueryTables and PivotTables to retrieve the data. The data available to Excel applications was, however, limited to what was available across the company network, and to those databases you could get permission to access. Starting with the release of Office 97,...

Interacting with Excel

VBA and Excel are two different programs that have had very different upbringings. VBA speaks American. Excel also speaks American. However, Excel can also speak in its users' language if they have the appropriate Windows settings and Office language pack installed. On the other hand, VBA knows only a little about Windows settings, and even less about Office 2007 language packs. So, either you can do some awkward coding to teach VBA how to speak to Excel in the user's language, or you can just let them converse in American. I very much recommend the latter. Unfortunately, most of the newer features in Excel are not multilingual. Some only speak American, and others only speak in the user's language. You can use the American-only features if you understand their limitations the others are best avoided. All of them are documented later in the chapter.

Reading Data from Excel

When reading a cell's value, using its Value property, the data type that Excel provides to VBA is determined by a combination of the cell's value and its formatting. For example, the number 3000 could reach VBA as a Double, a Currency, or a Date (March 18, 1908). The only international issue of concern here is if the cell's value is read directly into a string variable the conversion will then be done implicitly, and you may not get what you expect (particularly if the cell contains a Boolean value). As is the case when sending data to Excel, the translation between U.S. and local functions and formats occurs when reading data from Excel. This means that a cell's .Formula or .NumberFormat property is given in English, and with U.S. number and date formatting, regardless of the user's choice of language or regional settings.

The users installed version of Excel

With every new release of Excel, the issue of compatibility rears its head. As I write this, Excel 2003 is just about ready to be released yet many large corporations are still using Excel 97, and some use even earlier versions. Unfortunately, there is no guarantee that an application developed for, say Excel 97, will work perfectly with later versions of Excel. If you need your application to work with a variety of Excel versions, the best approach is to work with the lowest version and then test it thoroughly with all other versions. Things get even more complicated when you consider Excel's sub-versions. Microsoft distributes service releases (SRs) to correct problems. For example, users might have the original Excel 2000, Excel 2000 with SR-1, or Excel 2000 with SR-2. And it gets even more complicated with Excel 2003. Microsoft sells several different editions of Office. Many of the new features in Excel are available only in the Professional version.

Understanding Excels Events

This chapter explains the concept of Excel events, and I include many examples that you can adapt to your own needs. As you can see, understanding events can give your Excel applications a powerful edge. Here you will find An overview of the types of events that Excel can monitor Excel is capable of monitoring a wide variety of events, and executing your VBA code when a particular event occurs. Following are just a few examples of the types of events that Excel can recognize

Event Types That Excel Can Monitor

Excel is programmed to monitor many different events that occur. These events can be classified as the following Application events Events that occur for the application (Excel). Examples include NewWorkbook (a new workbook is created), WorkbookBeforeClose (any workbook is about to be closed), and SheetChange (a cell in any open workbook is altered). To monitor Application-level events, you need to use a class module.

Using Automation in Excel

You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, the Excel macro will control Word's automation server. In such circumstances, Excel is the client application, and Word is the server application. Or you can write a Visual Basic application to control Excel. The process of one application's controlling another is sometimes known as Object Linking and Embedding (OLE) or simply Automation. Some applications, such as Excel, can function as either a client application or a server application. Other applications can function only as client applications or only as server applications.

Few Words about Excels Menu

So how does a menu bar differ from a toolbar In general, a menu bar is displayed at the top of the Excel window, directly below the title bar. When clicked, the top-level controls on a menu bar display a drop-down list of menu items. A menu bar can also contain three window control buttons (Minimize, Restore, and These rules are definitely not cast in stone. You can, if desired, add traditional toolbar buttons to a menu bar or add traditional menu items to a toolbar. You can even move a menu bar from its traditional location and make it free-floating. Although Excel supports multiple menu bars, only one can be visible at any time. Beginning with Excel 2002, Excel's menu bar displays a Type a question for help box, which is a quick way to search the Help system. If you would prefer not to see this box, you can hide it by using the following VBA statement

What You Can Do with Excels Menus

Typical Excel users get by just fine with the standard menus. Because you're reading this book, however, you're probably not the typical Excel user. You might want to modify menus to make your life easier and to make life easier for the folks who use the spreadsheets that you develop. To modify Excel menus, you can remove elements, add elements, and change elements. In addition, you can temporarily replace Excel's standard menu bar with one of your own creation. You can change Excel's menus two ways manually, or with Visual Basic for Applications (VBA) code. When you close Excel, it saves any changes that you've made to the menu system, and these changes appear the next time that you open Excel. The information about menu modifications is stored in your XLB file.

Help Systems That Use Excel Components

Perhaps the most straightforward method of providing help to your users is to use the features contained in Excel itself. The primary advantage of this method is that In this section, I provide an overview of some help techniques that use the following built-in Excel components

Im very familiar with creating formulas in Excel Does VBA use the same mathematical and logical operators

I would like a particular workbook to be loaded every time that I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much VBA can't do it, but Excel's old XLM language can. Fortunately, you can execute XLM from VBA. Here's a simple example that retrieves the value from cell A1 on Sheetl in a workbook named myfile.xls in the c files directory A more general solution to avoid Excel prompts is to insert the following instruction

Where can I get Excel addins

You can get Excel add-ins from a number of places Excel includes several add-ins that you can use whenever you need them. Use the Tools Add-Ins command to install them. When I install my add-in from Excel's Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description Oddly, there is no way to remove unwanted add-ins from the list directly from Excel. You must edit the Windows Registry and remove the references to the add-in files that you don't want listed. Another way to do this is to move or delete the add-in files. Then when you attempt to open the add-in from the Add-Ins dialog box, Excel will ask whether you want to remove the add-in from the list. Activate any worksheet and then choose File Save As. Then select Microsoft Excel Add-in (*.xla) from the Save as Type drop-down list.

Interacting with Microsoft Excel

Microsoft Excel is a great program for playing what-if scenarios with data because it lets you plug data and formulas into cells in whatever manner you want. Excel isn't good, however, at managing large volumes of data. For large volumes of data, you need a database like Microsoft Access. Microsoft Access can certainly do any math calculations that Excel can do. Playing with what-if scenarios with data in Access isn't so easy, though, because you need to get queries and or forms involved. It's just plain difficult to experiment with what-if scenarios in Access. You could, of course, just open Excel and type in the total sales value or even copy and paste it from some form in Access. Optionally, you could automate the whole thing by creating a button on some Access form that opens the worksheet and plugs in the total sales amount for you. Look at an example of Automation that does just that. The first step is to create an Excel worksheet that contains a blank cell that gets its values...

Unleash the Power of Excel with VBA

The Power of Excel Visual Basic for Applications combined with Microsoft Excel is probably the most powerful tool available to you. This tool is sitting on the desktops of 400 million users of Microsoft Office and most have never figured out how to harness the power of VBA in Excel. Using VBA, you can speed the production of any task in Excel. If you regularly use Excel to produce a series of monthly charts, you can have VBA do the same task for you in a matter of seconds. There are two barriers to learning successful VBA programming. First, Excel's macro recorder is flawed and does not produce workable code for you to use as a model. Second, for many who learned a programming language such as BASIC, the syntax of VBA is horribly frustrating. Microsoft began to dominate the spreadsheet market in the mid-90s. Although they were wildly successful in building a powerful spreadsheet program toward which any Lotus 1-2-3 user could easily transition, the macro language was just too...

Excel File Formats Supported

As you probably know, Excel allows you to save a workbook in a format for earlier versions. In addition, you can save a workbook in a dual-version format that combines two file formats in a single file. These dual-version formats result in a larger file, but unfortunately, these dual-version formats sometimes introduce problems of their own. If your application must work with earlier versions of Excel, you need to make sure that your file is saved in the appropriate file format. The various Excel file formats that can be saved by Excel 2002 are Microsoft Excel Workbook (*.xls) The standard Excel 2003 file format. Can be opened by Excel 97, Excel 2000, Excel 2002, and Excel 2003. Microsoft Excel 5.0 95 Workbook A format that can be opened by Excel 5.0 and later versions. Microsoft Excel 97-2000 & 5.0 95 Workbook A dual format that can be opened by Excel 5 and later versions. Microsoft Excel 4.0 Worksheet (*.xls) Can be opened by Excel 4 and later versions. This format saves a single...

How VBA Works with Excel

Rich Tennant

No, that's not the icon or Excel, it's the icon tor Excuse, the database o reasons vihy you haven't learned the other programs in Oiiice* 7 he next four chapters provide the necessary foundation for discovering the ins and outs of VBA. You find out about modules (the sheets that store your VBA code) and are introduced to the Excel object model (something you won't want to miss). You also discover the difference between subroutines and functions, and you get a crash course in the Excel macro recorder.

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 Now let's see how you can use the GetString method to retrieve the data from the Excel file created in an earlier example (or simply substitute the file name with any Excel spreadsheet name you want to read). 3. Save the ASP file as C ExcelWithASP GetExcel.asp. strCon strCon & & strCon strCon & Extended Properties Excel 8.0 The above ASP script connects to the specified Excel file and retrieves the data located in Sheetl. After reading the column names from the Fields collection, the code uses the above-mentioned GetString method to pull the data

Using XML to Round Trip a Workbook from Excel to HTML and Back

Diagnosis Worksheet Template

Microsoft is thrilled that you can now save Excel 2003 and Word 2003 documents as XML files. They consider XML files to be native Office documents now. This is cool, because it means that any program that can write XML can, in theory, produce an Excel file on the fly. Round-tripping is the process of saving an Excel file as XML, and then opening the XML file back into Excel. Figure 15.5 shows the results of round-tripping an Excel file. The file on the left contains greenbar formatting, strange fonts, and a chart. The formatted spreadsheet on the left was saved as an XML Spreadsheet file, closed, and re-opened as the file on the right. Excel did warn that the chart and any VBA code would be lost. Other than the chart, all other formatting was preserved perfectly on the round-trip to XML and back. Excel 2003 now supports XML as a native file format. Excel 2003 now supports XML as a native file format. Figure 15.6 shows a subset of the XML code created when an Excel file is saved as an...

Understanding Excel menu terminology

Menu terminology is often a bit confusing at first because many of the terms are similar. The following list presents the official Excel menu terminology that I refer to in this chapter Menu bar The row of words that appears directly below the application's title bar. Excel has two menu bars One is displayed when a worksheet is active, and the other is displayed when a chart sheet is active or when an embedded chart is activated. Menu A single, top-level element of a menu bar. For example, both of Excel's menu bars have a File menu.

Excel s International Options

In the Office Menu O Excel Options dialog, the Advanced section contains Editing Options that allow the user to specify the characters that Excel uses for the thousands and decimal separators, overriding the Windows Regional Settings. These options can be read and changed in code, using Application.ThousandsSeparator, Application.DecimalSeparator, and Application.UseSystemSeparators. There is a big problem with this feature, in that while these options affect all of Excel's xxxLocal properties and functions (including the Application.International settings), they are ignored by VBA. The VBA Format function used almost every time a number is displayed to the user ignores these options, resulting in text formatted according to the Windows Regional Settings, not those used by Excel. 'Only do for Excel 2002 and greater If Val(Application.Version) > 10 Then 'Get the override separators used by Excel sXLThousand Application.ThousandsSeparator sXLDecimal Application.DecimalSeparator 'Swap...

Removing Excel menu elements

You can remove any part of the Excel menu system menu items, menus, and entire menu bars. For example, if you don't want the end users of your application fiddling with the display, you can remove the View menu from the Worksheet Menu Bar. You can also remove one or more menu items from a menu. If you remove the New menu item from the File menu, for example, users can't use the menu to create a new workbook. Finally, you can eliminate Excel's menu bar and replace it with one that you've created. You might do this if you want your application to be completely under the control of your macros.

Listing Automating Power Point Presentation Creation from Excel

CreatePresentation is the main procedure of this listing. Right off the bat, you can tell that this procedure is using early binding just by looking at the variable declarations. CreatePresentation uses two variables that represent a PowerPoint object. The first, ppt, represents the PowerPoint.Applica-tion object. Like the Application object from the Excel object model, PowerPoint.Application is at the top of the PowerPoint object model. The second variable that represents a PowerPoint object is named pres and represents a PowerPoint Presentation object. The Presentation object is roughly analogous to the Workbook object in Excel. At this point, I can copy in the data from Excel. Because I need to do a number of things when I'm copying from Excel to PowerPoint, it's a good idea to create a procedure that wraps this task up. To do so, I enter the CopyDataRange procedure. Copying from Excel to PowerPoint is a two statement process. The first step is to copy the range in Excel to the...

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. C projects Excel cds.xml, ImportMap Nothing, Overwrite True, Destination Range( A 1) End Sub Member of Excel .Application I Class Workbook Member of Excel If no schema exists for this data, Excel will create one for you, as shown in Figure 3-7. Excel refers to these schemas as XML maps. Click the check box so you won't see this message any longer, and let the import continue. When the import is finished, the data should look like that in Figure 3-8. C projects Excel cds.xml, ImportMap This is due to the fact that the first time we ran the code, Excel created a map for us and bound our data table to it. To view that map, right-click anywhere in the data range and choose XML > XML Source (Figures 3-10 and 3-11). The second time the code is run, we run into trouble because Excel automatically creates a new map for the data that is...

Quattro Pro Excel 2007

Like Lotus, Borland was slow to jump on the Windows bandwagon. When Quattro Pro for Windows finally shipped in the fall of 1992, however, it provided some tough competition for the other two Windows spreadsheets, Excel 4.0 and 1-2-3 Release 1.1 for Windows. Importantly, Quattro Pro for Windows had an innovative feature, known as the UI Builder, that let developers and advanced users easily create custom user interfaces. There was a time when Quattro Pro seemed the ultimate solution for spreadsheet developers. But then Excel 5 arrived.

Excels Builtin Matrix Functions

Performing matrix mathematics with Excel is very simple. Let's begin by assuming that the matrices A and B have been defined by selecting the 3R x 3C arrays of cells containing the values shown in Figure 3-1 and naming them by using Define Name. Remember, we're simply assigning a range name to a range of cells. We usually refer to it as a range or an array the fact that we are calling it a matrix simply indicates what we intend to do with it. Addition or Subtraction. To add a constant (e.g., 3) to matrix A, simply select a range of cells the same size as the matrix, enter the formula A+3, then press COMMAND+RETURN or CONTROL+SHIFT+RETURN (Macintosh) or CONTROL+SHIFT+ENTER (Windows). When you array-enter a formula by pressing e.g., CONTROL+SHIFT+ENTER, Excel puts braces around the formula, as shown below A+3 Do not type the braces if you do, the result will not be recognized by Excel as a formula. Subtraction of a constant, multiplication or division by a constant, or addition of two...

What Is an Excel Application

First of all, I should define what I mean by an Excel application. By Excel application, I mean a spreadsheet that you have developed using Excel and VBA for use by yourself or other users. VBA stands for Visual Basic for Applications, which is essentially Visual Basic that has been modified to run within other host applications such as Excel or Microsoft Word. Excel applications contain VBA code that may perform one or more of the following tasks Automate tedious processes. Many times, once you create a useful spreadsheet, in order to maintain it, you need to perform a set of tasks in a specific order on a frequent basis. Often, you can automate such simple tasks using Excel's macro recorder. For more complex tasks you need to write VBA code. Enhance the user experience. The experience and comfort level of people using Excel varies widely. One way to spread the wealth of a useful spreadsheet is to incorporate a user interface into the workbook that can serve to guide the less...

Opening Text Files in Excel

Even if you are already familiar with the mechanics of opening a text file in Excel, it is a good idea to refresh your knowledge here because the process of opening these files programmatically depends on your familiarity with the manual process. I will demonstrate the mechanics of opening up a delimited file using the file shown in Figure 15.1. For any text file, you begin the process by selecting File Open in Excel and then you change the Files of Text drop-down box to Text Files (*prn., *.txt, *.csv) as shown in the following graphic. Usually you can tell by looking at the preview in the bottom half of Figure 15.3 whether the file's type is delimited or fixed-width. This can be tricky with tab-delimited files, however, as they can look a bit like a fixed-width file. If in doubt, you can always select delimited and move on to the next step. If you select the tab delimiter and the file does not get parsed (the field separation lines do not appear), then go back to step 1 and choose...

Loading an XML Document in Excel

After saving an ADO recordset to an XML file on disk (see Hands-On 29-13 earlier in this chapter), you can load it into a desired application 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. The following hands-on exercise demonstrates how to open a persisted recordset and write its data to an Excel workbook.

Understanding Excel Events

Vba Form Some Desing Using Excel

Excel associates UserForm events not only with the form but also with each of the controls that exist on the form. You need to place event-handling procedures Excel activates the UserForm. Excel adds a run-time control to the UserForm. Excel detects a UserForm control error. Excel closes the UserForm. Excel removes a control from the UserForm at run-time. Excel terminates the UserForm. AUTOMATING PROCEDURES WITH EXCEL EVENTS Application events include all events that the Application object recognizes. To access an application event you create a class module to contain your application event-handling procedure code. See the section Run a Procedure when Excel Creates a The following table provides a list of the application-level events that occur within Excel. An event that occurs for the application, in this case Excel. For example, Excel triggers the NewWorkbook event when it creates a new workbook. Occurs when Excel creates a new workbook. See the section Run a Procedure when Excel...

Using XML with ExceLVBA Projects

If you have any experience with the World Wide Web, whether it's developing Web sites or just browsing, then I am sure you have heard of XML (eXtensible Markup Language). Although not a new technology, it has only been in recent years that XML has generated a lot of interest. This is partially evident by Microsoft's decision to add XML support to some of its Office programs (including Excel) starting with version 10.0 (XP) and extending that support in version 11.0 (2003). I expect the level of XML support to increase in subsequent versions of Office applications. In this chapter I will discuss the following topics Opening and saving XML files with Excel

Simply Exporting Access Data to Excel

Just as in earlier Office versions, Access offers two ways to do a quick-and-dirty export of table or query data to an Excel worksheet. You can use the Excel button in the Export group of the External Data tab of the Ribbon to export Access data without worrying about formatting, for an Office 2007 user who just wants the data. If you need to create worksheets that can be opened and edited by users running older versions of Office, or using a handheld device such as a BlackBerry, you can use the TransferSpreadsheet method to export data, selecting the desired output worksheet format. This can be useful when you work for an organization that has upgraded its software and you need to send a worksheet with client contact information to a sales representative who has not updated her laptop yet. For a quick export to the new .xlsx worksheet format, use the Excel button in the Export group of the External Data tab of the new Access Ribbon, as shown in Figure 7.1. Exporting a table to an...

Using the Excel OM to Create a New Workbook

To create a new worksheet in Excel, you will build the code in a few steps. To start you create an Excel application object. Then, you create a new worksheet object, as shown in the following code Dim xlApp As Excel.Application Dim xlWorkbook As Excel.Workbook 'Create the Excel Application object Set xlApp After creating the new worksheet, you'll want fill that worksheet with data. The next example uses a Recordset object (from DAO, see Chapter 6) to gather the data from an Access query in the database. The Recordset object enables you to get the data from any table or row returning a query for the desired records to be exported to Excel. The following code creates a new Recordset based on the selected query in the list box and uses that query's name as the name for the Excel worksheet. Once the Recordset object has been created, Excel's CopyFromRecordset method can be used to copy data from the Recordset to the Cells object of the new worksheet. The following code shows how to...

Invoking Excel Functions in VBA Instructions

If a VBA function that's equivalent to one you use in Excel is not available, you can use Excel's worksheet functions directly in your VBA code. Just precede the function with a reference to the WorksheetFunction object. For example, VBA does not have a function to convert radians to degrees. Because Excel has a worksheet function for this procedure, you can use a VBA instruction such as the following The WorksheetFunction object was introduced in Excel 97. For compatibility with earlier versions of Excel, you can omit the reference to the WorksheetFunction object and write an instruction such as the following There are no new VBA functions in Excel 2002 or Excel 2003. *Not available in Excel 97 and earlier editions

Exporting XML data from Excel

Contrary to what you might expect, you can't export an arbitrary range of data in XML format. For example, if you create a List Range on your worksheet, you can't export that List Range to an XML file unless you add an appropriate map to your worksheet first. And it's not possible to create (or modify) a map by using Excel. If you choose the Excel File Save As command,you'll notice that one of the options is XML Spreadsheet. This produces an XML file that uses the Microsoft XMLSS schema.It will not export the data to a normal XML file that is readable by other applications.

Example Listing Excels Command Bar Objects

This indicates that Excel's main menu bars are different for worksheets than for chartsheets, as is evident if you look at the menus themselves. The worksheet menu bar has different controls than the Chart menu bar. Thus, if you want to add a custom menu item to Excel's main menu bar, regardless of what type of sheet is currently active, you will need to do so for both the Worksheet Menu Bar and the Chart Menu Bar. There is a slight complication concerning the CommandBars property that we should discuss. When qualified with the Application object, as in Application.CommandBars, this property returns the collection of all available built-in and custom command bars for the application which in this case is Excel. This is why we used the fully qualified expression However, from a Workbook, the CommandBars property returns a different collection. In particular, there are two possibilities. When the workbook is embedded within another application and Excel is activated by double-clicking...

Switching Excel to Display RC Style References

Customs Form For Peru Spanish

Checking R1C1 reference style on the General tab of the Options box causes Excel to revert to R1C1 style in the Excel user interface. Checking R1C1 reference style on the General tab of the Options box causes Excel to revert to R1C1 style in the Excel user interface. Microsoft Excel - Chapter6.xls

Using DOM with ADO to Convert Excel Data to XML

To help demonstrate this, open the Programming XML.xlsm file, found in the XMLSampleFiles folder in this chapter's download page at www.wrox.com. In this file, you will find the following procedure. This procedure loads an Excel range (in this case range A1 D43) into an ADO recordset, and then saves the recordset into the DOMDocument object, which is then output to an XML file oMyconnection.Open & _ Data Source & oMyWorkbook & & _ Extended Properties excel 8.0 & _ Persist Security Info False An XML file generated by ADO typically contains one root element and two child nodes Schema and Data. The Schema node contains information about the recordset structure field names, data type, field length, position, and so on. The Data node contains the actual data. Although attribute-based documents are difficult for humans to read, they are well formed and pose no problem for Excel.

Excel Development Best Practices

As YOU ADVANCE YOUR Excel development skills, you'll experience dramatic increases in productivity. These increases come from knowing more about various Excel objects and their properties and methods, being able to apply the correct object to the task at hand, and realizing more efficient development methodologies.

Using an ActiveX Component in Excel

Arr_sPath(0) 2007.accdb arr_sPath(l) Public Sub PlaceData(TheWorksheet As Excel.Worksheet, WhichData As String) Dim xl As Excel.Application Set xl TheWorksheet.Application 'hook into the current Excel session 1. Open a new workbook in Excel. 0 Visual Basic For Applications 0 Microsoft Excel 12.0 Object Library 0 OLE Automation 1. Return to Excel. One benefit that we see from this example is minimal code in our project. Let's take a look now at how .NET technologies can actually take the code out of our Excel projects.

Emulating Excels SUM Function

In this section, I present a custom function called MySum. Unlike the SimpleSum function listed in the previous section, the MySum function emulates Excel's SUM function perfectly. Before you look at the code for MySum, take a minute to think about the Excel SUM function. It is, in fact, very versatile. It can have as many as 30 arguments (even missing arguments), and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded functions. For example, consider the following formula Function MySum(ParamArray args() As Variant) As Variant ' Emulates Excel's SUM function If an argument contains an error (for example, DIV 0 ), the MySum function simply returns the error just like Excel's SUM function. Excel's SUM function considers a text string to have a value of 0 unless By the way, I hope you understand that the point of this example is not to create a new SUM function. Rather, it demonstrates how to create custom worksheet...

The Excel Input Box method

Using Excel's InputBox method (rather than VBA's InputBox function) offers three advantages The syntax for the Excel InputBox method is CODES TO DETERMINE THE DATA TYPE RETURNED BY EXCEL'S INPUTBOX METHOD Excel's InputBox method is quite versatile. To allow more than one data type to be returned, use the sum of the pertinent codes. For example, to display an input box that can accept text or numbers, set type equal to 3 (that is, 1 + 2, or number plus text). If you use 8 for the type argument, the user can enter a cell or range address manually, or point to a range in the worksheet. Yet another advantage of using Excel's InputBox method is that Excel performs input validation automatically. In the GetRange example, if you enter something other than a range address, Excel displays an informative message and lets the user try again (see Figure 12-3). Figure 12-3 Excel's InputBox method performs validation automatically. Figure 12-3 Excel's InputBox method performs validation...

Excels Standard Controls

Figure 14-3 shows two toolboxes, each of which provides access to one type of control. (Below each toolbox is a control created using that toolbox.) The controls on the Control Toolbox (on the left in Figure 14-3) are ActiveX controls. These controls can be placed either on a UserForm or directly on a worksheet (but not a chartsheet). They are the same as the controls that are accessible from the VB editor's Toolbox when designing a UserForm. ActiveX controls are very flexible and generally support a wide range of events. The Control Toolbox can be opened from within Excel (not the Excel VBA IDE) by selecting the Customize option from the Tools menu and checking the Control Toolbox toolbar in the Toolbars tab. By selecting the Customize option from the Tools menu and checking the Forms toolbar in the Toolbars tab, you open the Forms toolbox. The controls on the Forms toolbox (on the right in Figure 14-3) are referred to as standard Excel worksheet...

Linking a Microsoft Excel Spreadsheet

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method of the DoCmd object, as shown in Hands-On 11-9. Note, however, that neither the DoCmd object nor its Transfer-Spreadsheet method are members of the ADO Object Model. The DoCmd object is built into the Microsoft Access library. Hands-On 11-9 Linking an Excel Spreadsheet

Excel Ribbon Customization

Excel 2007 offers its very own way of customizing the Ribbon, which is far more complex than manipulating the CommandBars collection. The new approach involves writing XML code in a text editor, copying that XML file into the workbook file (all outside of Excel ), editing a bunch of XML files (which also are stashed away inside the new Excel file format, which is really nothing more than a zipped container of individual but related files), and then writing VBA procedures to handle the clicking of the controls you put in the XML file. Explaining all the intricate details involved in customizing the Ribbon is well beyond the scope of this book. However, I walk you through a quick example that demonstrates the steps required to (manually) add a new Ribbon group to the Home tab. The new Ribbon group is named Excel VBA For Dummies, and it contains one button, labeled Click Me. Clicking that button runs a VBA macro named ShowMessage. 1. Create a new Excel workbook, insert a VBA module, and...

Using ADO in Microsoft Excel Applications

This section combines the understanding of Excel programming that you've gained from previous chapters with the SQL and ADO techniques discussed so far in this chapter. Excel applications frequently require data from outside sources. The most common of these sources are Access and SQL Server databases. However, I've created applications that required source data from mainframe text file dumps and even Excel workbooks. As you'll see, ADO makes acquiring data from these various data sources easy. To run the code examples shown in the sections that follow, you must set a reference from your Excel project to the ADO 2.5 Object Library. To do this, bring up the References dialog by selecting the Tools O References menu item from within the VBE. Scroll down until you locate the entry labeled Microsoft ActiveX Data Objects 2.5 Library. Place a check mark beside this entry and click OK (see Figure 20-4).

The Excel Startup Folder

When Excel loads, it automatically loads any spreadsheets stored in its startup and alternate startup folders. The default location of the startup folder is usually a subfolder of the main Excel folder named XlStart. By default, there is no alternate startup folder, although one can be defined using the General tab of the Options dialog to open it, select Options from the Tools menu. Because the contents of these folders are opened at startup as ordinary workbooks, their macros are easily accessible to all other Excel workbooks. This makes them ideal as a storage location for macros. The only drawback is that Excel actually opens the spreadsheets stored in these directories to prevent this, they should be hidden by selecting the Hide option from Excel's Window menu (not the Format menu) when the spreadsheet to be hidden is active. Macros that are stored in the startup and alternate startup folders are available from the Macro dialog, and we can assign them to toolbars and menus...

Members that Affect the Current State of Excel

The following members have an effect on the current settings of Excel The CutCopyMode property can return False, indicating that Excel is in neither Cut nor Copy mode, or else one of the two values from the following enum When this property is True (which is its default value), Excel allows editing in cells. Otherwise, it does not allow editing in the cells (but you can still edit in the formula bar). When this property is set to False, Excel will block all input from the keyboard and mouse except for input to dialog boxes that are displayed by code. This will prevent the user from interfering with the currently running macro. The default value of the Interactive property is True. Of course, considerable care must be taken with this property. For instance, if you forget to reset the property to True, or if your code terminates unexpectedly, the user may need to restart Excel. Note that the Alt-F4 key combination will work to shut down Excel, but the user will not be able to save any...

Sending Data to Excel

By far the best way to get numbers, dates, Booleans, and strings into Excel cells is to do so in their native format. Hence, the following code works perfectly, regardless of locale There is a boundary layer between VBA and Excel. When VBA passes a variable through the boundary, Excel does its best to interpret it according to its own rules. If the VBA and Excel data types are mutually compatible, the variable passes straight through unhindered. The problems start when Excel forces you to pass it numbers, dates, or Booleans within strings, or when you choose to do so yourself. The answer to the latter situation is easy don't do it. Whenever you have a string representation of some other data type, if it is possible, always explicitly convert it to the data type you want Excel to store before passing it to Excel. Excel requires string input in the following circumstances In these cases, you have to ensure that the string that VBA sends to Excel is in U.S.-formatted text you must use...

Programmatically Zipping an Excel Container

In the ZipPackage procedure demonstrated here, you are creating an empty .zip file and then filling it with the contents of a source directory. Notice that you are using the Sleep API function here. This lets you pause Excel for a specified number of milliseconds. Pausing Excel allows each file to be completely compressed and saved before moving on the next file. In this procedure, you are making Excel sleep for 500 milliseconds each time you copy a file to the .zip container

Changing Excel menu elements

If you get bored with Excel's standard menu text, you can change it to something else for instance, you can change the Tools menu to the Miscellaneous menu. You can also assign your own macros to built-in menu items. You have many other options for changing menu elements, including rearranging the order of the menus on a menu bar (for example, to make the Help menu appear first instead of last). Moving Up from Excel 5 95 If you've customized menus in Excel 5 or Excel 95, you can pretty much forget everything that you ever learned. Beginning with Excel 97, menu customization has changed significantly in the following respects The Excel 5 95 Menu Editor is gone. To edit a menu manually, you choose the View Toolbars Customize command. Understand, however, that Excel 5 95 workbooks that contain menus customized by using the old Menu Editor still work in Excel 97 and later. However, to make any changes to these modified menus, you must do so in Excel 5 95. Better yet, just remove the menu...

Creating a Vanilla Pivot Table in Excel Interface

Although they are the most powerful feature in Excel, Microsoft estimates that pivot tables are used by only 7 of Excel users overall. Based on surveys at MrExcel.com, about 42 of advanced Excel users have used pivot tables. Because a significant portion of you have never used pivot tables, I will walk through the steps of building a pivot table in the user interface. If you are already a pivot table pro, jump ahead to the next section.

Run A Procedure When Excel Creates A Workbook

How Open Module Sheet Excel

You use the NewWorkbook event to determine when Excel has created a new workbook. The NewWorkbook event has one parameter value that passes into the subroutine. The Wb parameter contains the new created workbook. You can access any of the methods and properties of the new workbook to customize the created workbook. For example, you can use the Name property to return the name of the new workbook. See Chapter 9 for more information on working with the Workbook object. RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK (CONTINUED) RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK (CONTINUED) _ In the Workbook_Open subroutine, type Set Test.AppEvent Excel.Application, replacing Test with the variable in step 7 and AppEvent with the variable created in step 3. , Close and reopen Excel. AUTOMATING PROCEDURES WITH EXCEL EVENTS When you open the workbook containing the code that activates the application event, the code continues to execute each time you trigger the event. You may find circumstances...

Using Excel Application Functions in VBA

Now that you know how to write functions in VBA and make them available to your spreadsheets, you are also aware that you can re-create any function already available in the Excel application. Although recreating Excel's functions would be a good way to improve your VBA programming skills, it's certainly not a practical use of your time. Why reinvent what's already been created for you It would be nice if you could use Excel's application functions in your VBA code, as they are mostly complimentary, not repetitive, to VBA's set of functions. That way, if you need a specific function performed in your program that is not already included with VBA, you don't have to write it yourself. Well, there is a method to use the Excel application functions, of course, and it is really quite simple. Replacing the call to the PowerDB() function in the TestPower() sub procedure shown earlier with the line of code above will give the exact same result. The difference is that this code uses Excel's...

Using the cExcel Setup and cData Objects

I mentioned a moment ago that neither of our helper methods made any direct VBA calls. The same is true of the GetManagers method. All of our work is being done by our objects from start to finish. The beauty of this is that we can drop these classes in any Excel project and have this functionality available instantly. In our current Excel project, we can change the database and or SQL statement and import any data we need via the cData object.

The Excel Locale Issue

When you create Excel solutions with VBA, the Excel object model expects English US formatting regardless of the user's locale settings. VSTO mimics this behavior by creating transparent proxies for the objects that always report that the locale is English US (locale ID 1033). Although the Excel object model expects locale ID 1033, it formats the data according to the user's locale settings when the data is displayed in the worksheet. The AssemblyInfo.vb file within your Excel solutions contains an attribute called ExcelLocale1033Attribute. The value of this attribute determines whether VSTO creates and uses the transparent proxies and whether or not it behaves in the same way as VBA. By default, this attribute is set to true, causing the Excel object model to always expect locale ID 1033 (the same behavior you get in VBA). If you change this attribute to false, VSTO does not automatically report the locale as English US instead, the current culture is reported. This can cause errors...

How To Draw In Vba Excel

Macro Script Drawing

The concept of using XY charts for drawing has been perfected by Mala Singh of XLSoft Consulting in India. Mala has been able to use XY charts to draw just about anything. This blueprint is actually an XY chart in Excel (see Figure 10.21). This blueprint is actually an XY Chart in Excel. It takes about 25 seconds to draw the chart. This blueprint is actually an XY Chart in Excel. It takes about 25 seconds to draw the chart.

Using Excel Templates to Create Formatted Worksheets Filled with Access Data

If you want to produce a more formatted worksheet, you can prepare an Excel template and format it as needed for example, adding a large, centered title and column headings with appropriate text, perhaps in a larger or bolder font than the data area. Then, instead of using the Excel command on the Ribbon, use VBA code to export the Access data row by row to the data area of a new worksheet created from the template. I created a set of queries for archiving data, again using the sample Northwind data, and a dialog form (fdlgArchiveOrders) that allows the user to select a date range for archiving Orders data, as shown in Figure 3.5. Once the start date and end date have been entered or selected, clicking the Archive button runs a procedure that creates a new Excel worksheet from a template (Orders Archive.xltx) in the same folder as the database, fills it with data from tblOrders in the selected date range, and deletes the archived records. Dim appExcel As Excel.Application Dim...

An Introduction To The Excel Object Model

Designed around the ability to access and manipulate objects, VBA has access to an Object Model in each Microsoft Office product, including Excel, that enables you to interact with each application. Using the Object Model, you can access everything from the entire application to an individual cell in a worksheet. EXCEL OBJECTS The Excel Object Model provides nearly 200 different objects and more than 5,000 corresponding properties and methods for use in your VBA code. Each object represents an element of the Excel application. For example, the Application object refers to the entire Excel application, but a Worksheet object refers to an individual worksheet. Most objects have child objects. A child object is an object that is part of a larger object. For example, a Worksheet object is a child object to a Workbook object because worksheets are part of a workbook. All objects in the Excel Object Model are the children of at least one other object, except the Application object. All...

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 subroutine. We can break its functionality down to just a few items. The problem with that is it's doing a number of unrelated things. It's setting up the worksheet for data import, opening a connection to the database, getting data, putting it on the worksheet, and then formatting and cleaning up the worksheet.

Programmatically Unzipping an Excel Container

The unzip procedure is little more than a series of simple steps that duplicate the manual act of copying files out of an Excel container and saving them into a destination folder. First, load the name of your target Excel file to the TargetFile variable. The NewFileName is defined as the TargetFile string concatenated with the .zip extension. Next, use these variables in a FileCopy statement, essentially copying the target Excel file and saving it with a .zip file extension. This converts the target Excel file to a temporary .zip file while keeping the target file intact. Then create a destination folder and copy each of the XML parts located in the temporary .zip file into the destination folder. Once all XML parts have been copied, delete the temporary .zip file

Opening and Importing XML Documents into an Excel Worksheet

To open an XML document from the Excel application, select File, Open and then choose the desired XML file (.xml file extension) from the Open dialog box. After selecting a file, you will be asked if you want to open the file as an XML list, read-only workbook, or to use the XML Source Task Pane (see Figure 8.3). Typically, you load the data into a worksheet as an Excel list in order to take advantage of the data management features a list provides. If the XML file does not reference an existing schema document (.xsd file extension), Excel will automatically create one (you may be notified of this fact as shown in Figure 8.4) and store it internally with the workbook. You don't have to see the schema, or know how it describes your XML document, but you should know that it's there working in the background defining your data elements for Excel. Microsoft Office Excel X Microsoft Office Excel X When you open an XML file as a list, Excel adds the data to a worksheet and creates a list...

Vba And Excel Object Model Quick Reference

EXCEL OBJECT MODEL CONSTANTS (CONTINUED) Excel 2.0 format. Excel 2.0 format - Far East version. Excel 3.0 format. Excel 4.0 format. Excel 4.0 workbook format. Excel 5.0 format. Excel 97 format. Excel 95 - 97 format. Excel international Add-in. Excel international macro. EXCEL OBJECT MODEL CONSTANTS (CONTINUED) Excel workbook format. Microsoft Excel Workbooks.

Controlling Excel from Word

Vba Controlling

As you might expect, you can also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object CreateObject(Excel.Sheet) Chart object CreateObject(Excel.Chart) An Excel VBA procedure created this Word document. An Excel VBA procedure created this Word document. The example described in this section is a Word macro that creates an Excel Workbook object (whose moniker is Excel.Sheet) from an existing workbook named projections.xls. The macro prompts the user for two values and then creates a data table and chart, which are stored in the Word document. Recalculating the worksheet updates a chart. The data and the chart are then copied from the Excel object and pasted into a new document. The results are shown in Figure 23-5. The...

Topics in Learning Excel Programming

In general, the education of an Excel programmer breaks down into a few main categories, as follows. First, you need to learn a bit about the environment in which Excel programming is done. This is the so-called Visual Basic Editor or Excel VBA Integrated Development Environment (IDE for short). We take care of this in Chapter 3 and Chapter 4. Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications (VBA). Actually, VBA is used not only by Microsoft Excel, but also by the other major components in the Microsoft Office application suite Access, Word, and PowerPoint. Any application that uses VBA in this way is called a host application for VBA. (There are also a number of nonMicrosoft products that use VBA as their underlying programming language. Among the most notable is Visio, a vector-based drawing program.) It is also used by the standalone programming environment called Visual Basic (VB)....

Sending Information from Access to Excel

Access 2007 provides the capability to create forms and reports, which include graphs and tables. However, you may want to leverage some of the powerful Excel features, such as the new Charting and Conditional Formatting features new to Office 2007. Also, users may find it useful to be able to export their data in an Excel spreadsheet. The code samples for working with Excel can be found in the code behind the Export Report Manager form in the sample database. As with Outlook, using Excel features from VBA in an Access database solution requires a VBA reference to the Microsoft Excel 12.0 Object Model in the Visual Basic Editor's References dialog box.

VBA Excel version compatibility

When Microsoft introduced Excel 97, some radical changes to both the language and the developers interface were made. Excel 97 was the first time that Active X components could be embedded with worksheets and user forms. Compatibility with previous versions of VBA is far less likely than with versions released after Excel 97. At the time of writing this book, these include Excel 2000 and Excel XP. The VBA macros written in this book should work with versions of Excel 97 onwards. However, sometimes reference will be made to commands that were developed for Excel 5.

Overriding Excel Commands

Using VBA, it is easy to override a built-in Excel command. All you need to do is to change the OnAction property of an existing button to point to a procedure that has your modification. Of course, you need to do this for the button as well as the menu item. Listing 7.9 shows how you might override the Save button in Excel using VBA. Listing 7.9. Overriding the Save button in Excel with VBA Sub OverrideButton() You cannot do this in VSTO because the OnAction property can point only to an Excel macro, and not to a method in your VSTO code. You could create callbacks into your VSTO code from VBA, as shown earlier in this chapter. An alternative is to use VSTO to write code that replaces the existing built-in button or toolbar with your own. For an example, see Chapter 6. Although the example shows you how to replace menu items and toolbar buttons in Word, the same principle applies to Excel. There are many objects in the Excel object model that you can manipulate from VSTO. However,...

Creating Word and Excel Smart Tags with VSTO

VSTO provides two smart tag classes (Microsoft.Office.Tools.Excel.SmartTag and Microsoft.Office.Tools.Word.SmartTag) that enable you to add smart tags to your document or workbook. You enable Word or Excel to recognize certain terms by using these classes to add strings to the Terms property or to add regular expressions to the Expressions property of the smart tag. You'll learn more about regular expressions later in this chapter in Introduction to Regular Expressions. 1. Create an Excel workbook project with VSTO. 3. Add the code in Listing 9.3 to the code file after the line Public Class Workbook. This code adds a variable that has events for a Microsoft.Office.Tools.Excel.Action. WithEvents AddControlToActionsPane As _ Microsoft.Office.Tools.Excel.Action 4. Add the code in Listing 9.4 to the Startup event handler of the ThisWorkbook class. This code turns on the smart tag feature, creates a variable named ControlSmartTag for an Excel smart tag, and then adds a number of terms to...

Listing Moving Notes from Word to Excel

GetFile.Filters.Add Excel Files, *.XL* ' Get the Excel file. GetFile.Show ' Open the Excel workbook. Dim TheBook As Excel.Workbook Set TheBook Excel.Workbooks.Open(Filename) Dim TheSheet As Excel.Worksheet The next step is to figure out which Excel file to modify. The GetFile object is a file dialog box. Unfortunately, the file dialog box filters for Word point to Word documents (not to the Excel documents you need), so the code has to change the filter by using the Filters.Clear and Filters.Add methods. It's also important to set GetFile so that it doesn't allow multiple selections this program works with only one file at a time. It's time to open the Excel file. You don't actually see Excel open everything takes place in the background. The code uses the value in Filename as input to the Excel.Workbooks.Open method. Notice that everything to do with Excel begins with the word Excel. Word and Excel often use objects with the same name. Adding the word Excel avoids confusion. After...

Short Survey of Excel Applications

Over the course of my career, I have had the opportunity to observe hundreds of ways that various corporations use Excel. I am continually amazed at their creativity. Many times Excel is used appropriately however, some applications in Excel are clearly the result of a developer (or otherwise) who did not know how to solve the problem using other technology better suited for the task at hand. In these cases, though I personally would not have chosen Excel as my canvas, I am often impressed that Excel solved the problem. To this I attribute the imagination of the creator and the broad and deep capabilities of Excel. I have seen or developed Excel applications that are used for a diverse range of activities. Some of the uses that I have seen or developed include these Investment research publication A blue-chip investment bank used Excel to pull data from a back-end database, assemble the data in a meaningful manner, and create a printer-ready, 100+ page document that included financial...

Access Excel and Outlook

As another example of integrating different Office applications, you will extract some data from Access, chart it using Excel, and e-mail the chart using Outlook. The code has been set up as four procedures. The first procedure is a sub procedure named EmailChart that establishes the operating parameters and executes the other three procedures. Note that the code uses early binding, and you need to create references to the ADO and Outlook object libraries Dim rngData As Excel.Range Dim sFileName As String Dim sRecipient As String Function rngSalesData(sSQL As String) As Excel.Range 'Function to extract data from database using 'SQL statement in sSQL

Automating Procedures With Excel Events

Vba Scripts

Understanding Excel Events Run a Procedure When Excel Creates a Workbook VBA and Excel Object Model Quick Excel Programming Your visual blueprint for creating interactive spreadsheets uses simple, straightforward examples to teach you how to create powerful and dynamic programs. To get the most out of this book, you should read each chapter in order, from beginning to end. Each chapter introduces new ideas and builds on the knowledge learned in previous chapters. When you become familiar with Excel Programming Your visual blueprint for creating interactive spreadsheets, you can use this book as an informative desktop reference. Indicates the use of Visual Basic for Applications (VBA) code such as tags or attributes, scripting language code such as statements, operators, or functions, and Excel Object Model code such as objects, methods, or properties. If you are interested in writing macros for Microsoft Excel using Visual Basic for Applications (VBA), Excel Programming Your visual...

Opening a Microsoft Excel Spreadsheet

You can open external data sources supported by the Microsoft Jet database engine by using ADO and the Microsoft Jet 4.0 OLE DB provider. Use the Extended Properties of the Connection object to pass the connection string. Hands-On 10-6 demonstrates how to open a Microsoft Excel spreadsheet named C Report.xls by using ADO. Hands-On 10-6 Opening an Excel Spreadsheet with ADO Dim conn As ADODB.Connection Set conn New ADODB.Connection conn.Open & _ Data Source & CurrentProject.Path & _ Report.xls & _ Extended Properties Excel 8.0 MsgBox Excel spreadsheet was opened. conn.Close Set conn Nothing MsgBox Excel spreadsheet was closed. End Sub

Using Excel Worksheet Functions

The Change The Function Argument

You can add almost all of the Excel worksheet functions to your VBA code. VBA provides a very limited number of built-in functions. By using the various functions available within Excel, you can add functionality that is not available with the existing VBA functions. For example, Excel provides several different financial functions that you can use within your macros. To place an Excel worksheet function in your VBA subroutine or function, you use the WorksheetFunction property along with the name of the function. One of the properties available for the Application object, the WorksheetFunction property is part of the Excel Object Model that VBA uses to access features of Excel. The Application object refers to the actual Excel program. The WorksheetFunction object stores all of the Excel Worksheet functions. To access one of the functions in the WorksheetFunction object, you use the WorksheetFunction property and precede the name of the function with the statement Application....

Excel Vba Formulaarray

Chapter 1 Primer in Excel VBA_1 The Excel Object Model 21 Older Excel Versions 144 The Basics of Using XML Data in Excel 240 Programmatically Zipping an Excel Container 267 Excel's Built-in Command Bars 322 Registering Automation Add-Ins with Excel 385 Registering a COM Add-In with Excel 395 Access, Excel, and, Outlook 420 Using ADO in Microsoft Excel Applications 447 Chapter 23 Browsing OLAP Data Sources with Excel_507 Chapter 24 Excel and the Internet_525 Interacting with Excel 545 Sending Data to Excel 545 Reading Data from Excel 548 The Rules for Working with Excel 548 Excel 2007's International Options 552 Appendix A Excel 2007 Object Model_635

Querying Microsoft Excel Workbooks

When using ADO to access data from Excel 2007 workbooks, you use the same OLE DB provider that you used earlier in this chapter to access data from Microsoft Access 2007. In addition to Access, this provider also supports most ISAM data sources (data sources that are laid out in a tabular, row and column format). You will use the Sales.xlsx workbook, shown in Figure 20-7, as the data source for the Excel examples. When using ADO to work with Excel, the workbook file takes the place of the database, while worksheets within the workbook, as well as named ranges, serve as tables. Compare a connection string used to connect to an Access database with a connection string used to connect to an Excel workbook. Connection string to an Excel workbook sConnect & _ Data Source C Files Sales.xlsx & _ Extended Properties Excel 12.0 Note that the same provider is used, and that the full path and filename of the Excel workbook takes the place of the full path and filename of the Access...

Convenient Excel Object Properties

The Application object has a number of properties that conveniently return Excel objects of interest. For example, you often need a way to refer to the currently selected range, the active workbook, or the workbook that the module resides in (this may or may not be equal to the active workbook). The Application object has properties that return objects representing all of these and more. Table 5.1 lists the properties that are useful for returning particular Excel objects. Table 5.1 Properties Returning Excel Objects of Interest

Formatting Excel Worksheets in VBA Code

If you need to sort, group, indent, or otherwise format exported data in an Excel worksheet, or create a total under the last row of data, you can write VBA code to use Excel commands to do the work in code. You can apply formatting to a worksheet created by the TransferSpreadsheet method, or one created from the Ribbon command, or a worksheet created programmatically from a template. The procedure starts by creating a new worksheet from a template (Northwind Orders.xltx), as for the ArchiveData procedure. Data from the query qryOrdersAndDetails is written to rows in the worksheet, and then a set of Excel commands is used to apply hairline borders to the data area, and a double bottom border to the column headings row. Dim rng As Excel.Range Dim rngData As Excel.Range Dim rngStart As Excel.Range Dim strPrompt As String Dim strDataRange As String Dim strRange As String Dim strSaveName As String Dim strSheetName As String Dim strStartAddress As String Dim strTemplate As String Dim...

Members that Enable Excel Features

Several Application members enable or disable certain Excel features Set to True to have Excel automatically format hyperlink text as a hyperlink. Set to False to turn off this often-annoying feature of Excel. This property determines whether Excel's AutoComplete feature is enabled its default value is True. This property controls how Excel handles the Ctrl -Break or Esc key combinations during a running procedure. It can be one of the following XlEnableCancelKey constants For these reasons, Excel always resets the EnableCancelKey property to xllnterrupt whenever Excel returns to the idle state and there is no code running.

VisuAL Basic for Applications with Excel

In this first chapter, I introduce you to the programming tools available in Excel. These tools include the VBA IDE (Integrated Development Environment), controls and functions available through the main Excel application, and VBA on-line help. After your introduction to the VBA programming environment, I take you through a very short and simple program that calculates some basic statistics from a sample data set. The program displays the statistics in a worksheet formatted with a large font, bright colors, and a border to complete the Colorful Stats project. Programming tools within Excel Don't concern yourself with syntax (the rules of the VBA language) at this time. In subsequent chapters, I will show you the tools needed to build VBA projects. For right now, I just want you to see how easy it is to make something work and recognize that many of the keywords we use in VBA programming projects in this book are already familiar to you as an Excel user.

Sending EMail Attachments from Excel

As you probably know, Excel has commands to send worksheets or workbooks via e-mail. And, of course, you can use VBA to automate these types of tasks. The procedure below sends the active workbook (as an attachment) to joeblow anydomain.com. The e-mail message has the subject My Workbook.