For 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

Rating:

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

The very first point I want to make certain that 101 Secrets of a Microsoft Excel Addict definitely offers the greatest results.

All the testing and user reviews show that 101 Secrets of a Microsoft Excel Addict is definitely legit and highly recommended.

Download Now

Why Excel Is Great for Developers

Excel 2003 is a highly programmable product, and it is easily the best choice for developing spreadsheet-based applications. Excel uses the VBA language, which is now in widespread use. For developers, Excel's key features include the following Visual Basic for Applications This macro language lets you create structured programs directly in Excel. Excel isn't the only spreadsheet to include a structured scripting language (1-2-3 offers LotusScript, for example), but it's certainly the best implementation. 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. Custom dialog boxes You can easily create professional-looking dialog boxes. Excel's UserForm feature (introduced in Excel 97) is a vast improvement over the old dialog sheets. Customizable menus You can change menu elements, add to existing menus, or create entirely new menus. Other...

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 mode for the other applications.

Displaying Excels BuiltIn Dialog Boxes

Code that you write in VBA can execute Excel's menu commands. And, if the command leads to a dialog box, your code can make choices in the dialog box (although the dialog box itself isn't displayed). For example, the following VBA statement is equivalent to choosing the Edit Go To command, specifying range A1 C3, and clicking OK. But the Go To dialog box never appears (which is what you want). In some cases, however, you may want to display one of Excel's built-in dialog boxes so that the end user can make the choices. There are two ways to do this

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.

Getting Started With Excel Macros

Stop Recording Macro Excel

When you create a new macro, you have the option of assigning it to a keyboard shortcut by typing the shortcut in the Keyboard Shortcut box on the Record Macro dialog box. When you do this, the macro runs when you press the Ctrl key and the specified lowercase key simultaneously. If you specify an uppercase letter for the key, you can run the macro by pressing Ctrl+Shift+the specified key. Unfortunately, Excel does not stop you from creating shortcuts that override other predefined Excel shortcut keys. If you specify a shortcut key combination that matches a Microsoft Excel shortcut, your new shortcut overwrites it each time you press the shortcut keys, your macro not the Microsoft key combination runs. For example, the Save command shortcut is Ctrl+s. If you create a macro with a shortcut key of s, your macro runs instead of the Save command when you press Ctrl+s. Excel does use many of the available shortcut keys, so you are bound to overwrite one. Keep in mind that if you use an...

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

The Excel object model

Vba Excel Objektmodell

Many real-world objects can contain objects that are themselves objects. For example, the hardware of a computer system contains parts such as a monitor, speakers, keyboard, mouse, and so on. These are themselves objects - sometimes collections and individual - that have properties and methods associated with them. The same analogy can be made for example, when VBA for Excel interfaces with objects such as workbooks, cell ranges, cells, charts, and so on. An object model is a description of the object hierarchy. Excel VBA contains a clearly defined set of objects that are arranged according to relationships between them. Figure 4.1 shows a portion of the Excel object model. This is not complete but should give you an idea of the relationships between Excel objects. As we can see from Figure 4.1, the Application object is at the top level. Contained within the Application object are the Workbooks collection and all the Workbook instances within this at the next lower level, we have the...

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

Using Automation in Excel

You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, Excel macros control the most important component of Word the so-called automation server. In such circumstances, Excel is called the client application, and Word is the server application. The concept behind automation is quite appealing. A developer who needs to generate a chart, for example, can reach into another application's grab bag of objects, fetch a Chart object, and then manipulate its properties and use its methods. Automation, in a sense, blurs the boundaries between applications. For example, using automation, an end user might be working with an Access object inside Excel and not even realize it. 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.

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

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. Like a light switch, a Boolean setting is either on or off. For example, you might want to create a macro that turns the worksheet page break display on and off. After you print or preview a worksheet, Excel displays dashed lines to indicate the page breaks. Some people (author included) find this annoying. Unfortunately, the only way to get rid of the page break display is to open the Excel Options dialog box, click the Advanced tab, and scroll down until you find the Show Page...

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.

Checking Smart Tag Options in an Excel Workbook

Notice that both built-in and custom smart tags are listed as a group in the Recognizers section. Microsoft Excel 2002 comes with the MSN MoneyCentral smart tag that allows you to check the latest stock prices. So if you enter a financial symbol, such as MSFT, in a spreadsheet cell and

End user interface styles VBA Excel applications

Application Vba Excel Interface

The standard Excel worksheet interface may be suitable for those who are familiar with Excel, supported perhaps with comments to guide the user. For example, Figure 10.15 illustrates how comments can add explanation to the meaning of a column of data in a spreadsheet. In the fragment shown of the weeklysales worksheet of the SALESMAN workbook, the comment elaborates on the meaning of the sales_to_date range. Clearly, the designer of this type of interface will not require much knowledge of VBA. A user with an intermediate knowledge of Excel might benefit from the use of a formatted workbook. That is a workbook using worksheets that have locked or protected cell ranges, or perhaps use different fonts or colours to distinguish parts of the system, and so on. Figure 10.15 shows how the SALESMAN workbook has been formatted to enhance usability. Notice how the Weekly Sales range of the weeklysales worksheet has been coloured A complete Excel novice user might b e b est served by designing...

Importing an Excel Spreadsheet to a Microsoft Access Database

In the previous section, you learned how to link your Excel spreadsheet to an Access database. Importing your spreadsheet data is just as easy. You can even use the same VBA procedure you used for linking with one minor change simply replace the acLink constant with aclmport, and you are done. The following procedure imports the spreadsheet presented in Figure 15-19 (see the previous section) into the Northwind database.

Using the Excel Macro Recorder

Recording your actions using the Excel built-in macro recorder Understanding the types of macros you can record Setting the appropriate options for macro recording Record it using the Excel macro recorder Write it with VBA This chapter deals specifically with the ins and outs of using the Excel macro recorder. Recording a macro isn't always the best approach, and some macros simply can't be recorded, no matter how hard you try. You see, however, that the Excel macro recorder is very useful. Even if your recorded macro isn't quite what you want, the recorder is an excellent learning tool.

Building XML Files Outside of Microsoft Excel

When you save a Microsoft Excel 2002 workbook in the XML spreadsheet file format, the program uses the XML Spreadsheet Schema (XML-SS) to encode the data and formatting of the spreadsheet. Based on what you've already learned in this chapter about XML spreadsheet elements and attributes, it is quite easy to create an XML spreadsheet file outside of Microsoft Excel. Assume that you are working on a computer where Excel is not installed and need to prepare a spreadsheet containing quarterly sales figures for the East region. For the following exercise, you can use Notepad as your editor. The following exercise demonstrates how to use Notepad to create an Excel spreadsheet in XML format. 3. Assume you've just received an e-mail with the EastRegion.xml file attachment. Open the EastRegion.xml document in Microsoft Excel 2000. The XML output is shown in Figure 17-10.

Help for Your Excel Applications

If you develop a nontrivial application in Excel, you might want to consider building in some sort of help for end users. Doing so makes the users feel more comfortable with the application and could eliminate many of those time-wasting phone calls from users with basic questions. Another advantage is that help is always available That is, the instructions can't be misplaced or buried under a pile of books. In the past, I've always referred to Excel's onscreen assistance as online help. In fact, that's the common name for this type of assistance. But in recent years, the term online has come to refer to information available via the Internet. Some people were confused by the expression online help because the help information is actually stored on their local drive. Therefore, I now use the expression Help system to refer to assistance provided by an application. But, with Excel 2003, things have come full circle. For the first time, you can access help information that truly is...

Programming Components within Excel

Command Caption

Not everything of interest to the VBA programmer can be found in the VBA IDE. There are a few programming-related components that you can access from the Excel application. The components I am referring to are the Macro items found under the Tools menu, and three of the available toolbars Visual Basic, Control Toolbox, and Forms found in the View menu in Excel. Now that you've had an introduction to the VBA IDE, it's time to look at development tools accessed directly from Excel. To begin, take a closer look at the Macro selection from the Tools menu, shown in Figure 1.3. Notice two other items displayed in Figure 1.3 that I have not yet discussed Macros and Record New Macro. Essentially the Record Macro tool will allow you to create a VBA program by simply selecting various tasks in Excel through the normal interface. The Record Macro tool is quite helpful, as you will see in Chapter 4 when I discuss it in detail. The Macros menu item will simply display a dialog box with a list of...

Try It Out Controlling an Excel Spreadsheet from Access

In this example, you will create a new Excel workbook and populate some of the cells with data. 1. Add the following code to your module. Dim objExcel As New Excel.Application Dim objWorksheet As New Excel.Worksheet 'show Excel to the user objExcel.Visible True 3. You should see an Excel screen similar to that shown in Figure 10.6.

Using Random Numbers in Excel

Excel provides several ways to generate random numbers. The worksheet function RAND returns a random real number greater than or equal to 0 and less than 1. RAND is a volatile function that is, a new random number is returned every time the worksheet is calculated. You can test this, after entering RAND() in a cell, by pressing F9 (Calculate Now) or by typing anything (even a space character) in a cell and pressing the Enter key. You will see that the value returned by the RAND function changes. If you load the older Add-In, Analysis ToolPak, the function appears in the function list in uppercase (e.g., RANDBETWEEN). If you load the newer Add-In, Analysis ToolPak-VBA, the function list contains both the older uppercase function names and the newer function names, in lowercase. This helps to distinguish between Excel's built-in worksheet functions, such as RAND, and the Add-In names, such as Randbetween.

Linking an Excel Spreadsheet to a Microsoft Access Database

Example Micro Soft Access Work Sheet

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method (refer to the Retrieving Data with the TransferSpreadsheet Method section in this chapter for the details on working with this method). The following example procedure links the spreadsheet shown in Figure 15-19 to the Northwind database. After opening the Access database with the OpenCurrentDatabase method, the procedure uses the TransferSpreadsheet method of the Microsoft Access DoCmd object to create a linked table named ExcelSheet from the specified range of cells (A1 D7) located in the mySheet worksheet in the Chap15.xls spreadsheet file. Notice that the -1 argument in the DoCmd statement indicates that the first row of the spreadsheet contains column headings. Next, the procedure opens the linked table in Edit mode, so the user can add or modify data. If you change back to Excel after adding one or more records, you'll notice that the changes made in the linked Access table...

Native Excel Database Integration

Once you put your data in a database, you need an efficient way to get it out. Without writing a single line of code, you can easily incorporate data from a database into an Excel workbook using Microsoft Query (MS Query). MS Query is included with every edition of Microsoft Office. As you can see in Figure 16.1, MS Query is a visual query tool that looks similar to the query design view in Access. Using MS Query, you can define a query that runs and returns data to Excel. A query is basically a question that is phrased in terms that a database can understand. The data that a query returns is referred to as a result set or a recordset. Data retrieved using MS Query is associated with an external data range. As you learned in the last chapter, an external data range is a range of data in Excel that is somehow associated with an external data source. You can set up an external data range so that it refreshes itself at specific times to ensure that it always contains the most up-to-date...

Almost Ten Excel Resources

7his book is only an introduction to Excel VBA programming. If you hunger for more information, you can feed on the list of additional resources I've compiled here. You can discover new techniques, communicate with other Excel users, download useful files, ask questions, access the extensive Microsoft Knowledge Base, and lots more.

Excel Resources Online

If I've done my job, the information provided in this book will be useful to you. It is, however, by no means comprehensive. In addition, new issues tend to crop up, so you'll want to make sure that you're up-to-date. Therefore, I've compiled a list of additional resources that could help you become more proficient in Excel application development. I've classified these resources into three categories

Excel and Active Server Pages

In Chapter 15 you learned various methods of retrieving data from a Microsoft Access database and placing this data in an Excel worksheet. This section explores another technology, known as ASP, that you can use for accessing and displaying data stored in databases. Let's see how you can use ASP to retrieve some data from the Shippers table in the sample Microsoft Access Northwind database and how to display this data as an HTML table in an Excel worksheet inside your Internet browser.

About Excel Utilities

A utility isn't an end product, such as a quarterly report. Rather, it's a tool that helps you produce an end product. An Excel utility is (almost always) an add-in that enhances Excel with new features or capabilities. Excel is a great product, but many users soon develop a wish-list of features that they would like to see added to the software. For example, some users prefer to turn off the dotted-line page break display, and they want a feature that toggles this attribute so that they don't have to scroll through the Excel Options dialog box looking for the command. Users who work with dates might want a pop-up calendar feature to facilitate entering dates into cells. And some users desire an easier way to export a range of data to a separate file. These are all examples of features that aren't currently available in Excel. You can, however, add these features by creating a utility.

HandsOn From Access to Excel Loading an XML File into an Excel Workbook

Dim rst As ADODB.Recordset Dim objExcel As Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim StartRange As Excel.Range Dim h as Integer Set objExcel New Excel.Application ' create a new Excel workbook Set wkb objExcel.Workbooks.Add ' make Excel application window visible objExcel.Visible True wkb.SaveAs When the procedure is complete the Excel application window should be visible with the ExcelReport.xls workbook file displaying products retrieved from the XML file (see Figure 29-26). 4. Close the Excel workbook and exit Excel. IE Microsoft Excel - ExralRep-ort Figure 29-26 An ADO recordset persisted to an XML file is now opened in Excel.

Output from VB to Excel

To finish with, here's an easy little application that places values from a VB application into an Excel spreadsheet. There are project-level (early bound) references created to both Excel and the ADODB 2.0 Reference Library. An ADO recordset has already been created and is passed as a parameter to the OutputToExcel function. The function creates an instance of a new Excel workbook and worksheet, then copies the values from the ADO recordset into the worksheet. Excel's functionality is used to perform a simple calculation on the data, the worksheet is saved, Excel is closed down, and all references are tidied up. This example illustrates the power of a glue language such as Visual Basic. Here VB is acting as the glue between ADO, which is an ActiveX server, and Excel controlling both to produce a simple yet patently powerful and seamless application ' Declare the Excel object variables Dim oXLApp As Excel.Application Dim oXLWBook As Excel.Workbook Dim oXLWSheet As Excel.Worksheet...

Working with Data in Excel

A common scenario in any company is to communicate public data via charts, tables, and graphs through periodic reports. By allowing users to export data directly to Excel, you empower them to leverage the rich set of Excel features to create their own charts and tables at a whim, without having to modify the data or design of the architecture of your Access database solution. This shows all of the Query object names in the list box that are not temp queries created by the system those are denoted by starting with a tilde ( ) character. If you don't want users to be able to choose every Query in the database, the names of the object can always be hard-coded into the Row Source property for the list box. Now that there is a list of queries in the list box control, code can be created to export the results of one of those queries to an Excel workbook. Exporting to Excel can be completed in several different ways, two of which are presented here. The first involves opening Excel, creating...

Developing Custom Functions in Excel

You can't find too many worksheets that are devoid of formulas because you need formulas to figure out new values based on your existing data. Microsoft includes a wealth of standard formulas in Excel. In fact, it's possible that you'll never need anything more than the Microsoft formulas. However, formulas are extremely important, so it's handy to know how to create one of your own. All formulas in Excel rely on functions. If you want to create a special formula for your worksheet, all you need is a function to perform the task. The functions that you create appear in the User Defined category of the Insert Function dialog box, which you access by using the following procedure Excel inserts the function into the current cell.

Placing Excel Data in an Access Table

What if, rather then linking or embedding your Excel spreadsheet, you wanted to create an Access table from scratch and load it with the data sitting in a worksheet Using several programming techniques that you've already acquired in this book, you can easily achieve this task. Let's look at the VBA procedure that dynamically creates an Access table based on the Excel worksheet presented in Figure 15-19 (see the section Linking an Excel Spreadsheet to a Microsoft Access Database). Notice that this procedure connects to the Access database using ActiveX Data Objects (ADO) and the MicrosoftJet.OLEDB.4.0 provider. After the connection is established, the procedure creates a new Access table by using the Catalog and Table objects from the ADOX object library. Next, the fields are added to the table that correspond to the names of the spreadsheet columns. Notice that each text field specifies the maximum number of characters that it can accept. If the spreadsheet cell's length is larger...

HandsOn Copying Records to an Excel Spreadsheet

Choose Tools References in the Visual Basic Editor window, scroll down to locate the Microsoft Excel Object Library, click the check box next to it, then click OK to exit. ' be sure to select Microsoft Excel Object Library ' in the References dialog box Public myExcel As Excel.Application Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim wbk As Excel.Workbook Dim wks As Excel.Worksheet Dim StartRange As Excel.Range Dim strConn As String Dim i As Integer Dim f As Variant ' declare a module-level object ' variable myExcel as Excel.Application ' at the top of the module Set myExcel New Excel.Application ' create a new Excel workbook Set wbk myExcel.Workbooks.Add ' make the Excel application window visible myExcel.Visible True ' close the workbook and save the file wbk.Close SaveChanges True, _ FileName C ExcelDump.xls ' quit the Excel application Figure 14-1 This Excel spreadsheet is created from Access data by running the procedure in Hands-On 14-5. Figure 14-1 This Excel...

Creating an Excel File from User Input

Recibo Baba

An ASP script can contain a form that is used for collecting user input. Assume that you need to gather information about patients visiting an urgent care center in your town. It's been requested that your data entry display screen has a web interface. Normally when you collect data on a web page, the information is saved into some sort of a database, like SQL Server or Access. However, your client particularly requested that the data from the input fields be saved directly to an Excel file. Let's see how you can provide this interface. Even though Excel is primarily a spreadsheet application, because of its layout (rows and columns), it can easily act as a database. It's not recommended to store a lot of records in a spreadsheet, but if the spreadsheet is currently the only container you have for storing the data, why not use it to your advantage The following example will demonstrate how to use Excel as a database. You will learn here how to query your Excel spreadsheet in order to...

Working With The Excel Object Model

Excel has over 240 different dialog boxes that display throughout its application. You can display any of these dialog boxes using the appropriate constant. The following table lists a few of the most commonly used Excel dialog boxes S Switch to Excel and run theOpen_DialogBox() S Switch to Excel and run theOpen_DialogBox()

Copying Records to an Excel Spreadsheet

The procedure in Hands-On 14-5 uses automation to copy records from the Employees table to an Excel spreadsheet. Once the recordset is opened, the Excel part is handled by object variables that point to the Excel Application object (myExcel), Excel Workbook object (wkb), Excel Worksheet object (wks), and Excel Range object (StartRange). Before you can use any of these objects you must set a reference to the Microsoft Excel Object Library. The result of copying a recordset to a worksheet is shown following the procedure code.