Excel Secrets Everyone Should Know

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

Of all books related to the topic, I love reading this e-book because of its well-planned flow of content. Even a beginner like me can easily gain huge amount of knowledge in a short period.

Do not wait and continue to order 101 Secrets of a Microsoft Excel Addict today. If anytime, within Two Months, you feel it was not for you, they’ll give you a 100% refund.

Download Now

Building an Excel Table

In the case of Excel, I've used a slightly different approach and worked with the application object. The code creates a new Excel spreadsheet, fills it with a database table, and formats the result. It uses an Excel internal object, Range, which is not to be confused with a similar type available in Word (the reason this type is prefixed with the name of the unit defining the Excel type library). Here is the complete code RangeE Excel97.Range I, Row Integer Bookmark TBookmarkStr begin ExcelApplication1.Visible 0 True ExcelApplication1.Workbooks.Add (NULL, 0) fill is the first row with field titles RangeE ExcelApplication1.ActiveCell for I 0 to Table1.Fields.Count - 1 do begin RangeE ExcelApplication1.Range 'A' + IntToStr (Row), RangeE ExcelApplication1.Range 'A1', 'E' + IntToStr (Row - 1) RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL) end

Using Office Programs

So far, we've built both the client and the server side of the OLE Automation connection. If your aim is just to let two applications you've built cooperate, this is certainly a useful technique, although it is not the only one. We've seen some alternative data-sharing approaches in the last two chapters (using memory-mapped files and the wm_CopyData message). The real value of OLE Automation is that it is a standard, so you can use it to integrate your Delphi programs with other applications your users own. A typical example is the integration of a program with office applications, such as Microsoft Word and Microsoft Excel, or even with stand-alone applications, such as AutoCAD.

Web Service Scenarios

Current Web Services provide simple information sources that you can easily incorporate into applications, such as stock quotes, weather forecasts, and sports scores. As the demand for access to business logic over the web increases, companies are finding ways of providing their customers with a class of applications to analyze and aggregate information. If a customer now keeps a Microsoft Excel spreadsheet in which they manually summarize their financial information, such as stock portfolio, 401 (k), bank account, and loan information, in the future, a Web Service will consolidate and continuously update this information for display by Excel or on a personal web site or digital assistant. Although much of this information is available through the web today, a Web Service will simplify access and consolidation and will ensure greater reliability.

When One Way Is Enough Printing Data

This is common in reporting, that is, to produce information for each record in sequence without needing any further access to the data. This broad category includes producing printed reports (via a set of reporting components or using the printer directly), sending data to other applications like Microsoft Excel or Word, saving data to files (including HTML and XML formats), and more.

In this chapter of the free database course youll see how to use Quick Report set of components to cr

QuickReport is designed to let us produce database reports as well as reports based on text files and string lists. QuickReport lets us create print previews where the user can check the result of a printout without wasting paper, and export data to other file formats, such as plain ASCII, comma separated values (CSV), MS Excel XLS, Rich Text RTF and HTML. By using the components on the QReport page of the Component palette, you can visually build banded reports to present and summarize the information in your database tables and queries. You can add summaries to group headers or footers to analyze the data based on grouping criteria.

Preparing Delphi for this example

For your convenience, Delphi has provided many common servers, such as Word, Excel, and PowerPoint, on the Component palette. To demonstrate how to import a server, we use Word. Since it already exists on the Component palette, this first step asks you to remove the package containing Word so that you can see how to install it on the palette. Step 4 describes how to return the Component palette to its normal state.

Sending Data to Microsoft Word

I've written a program, called DBOffice, which uses predefined server components to send a table to Word and to Excel. In both cases, you can use the application object, the document worksheet object, or a combination of the two. There are other specialized components, for tasks such as handling Excel charts, but this example will suffice to introduce use of the built-in Office components.

Mdac Ole Db Providers

The OLE DB Provider For OLAP can be used directly but is more often used by ADO Multi-Dimensional (ADOMD). ADOMD is an additional ADO technology designed to provide Online Analytical Processing (OLAP). If you have used Delphi's Decision Cube, or Excel's Pivot Tables, or Access's Cross Tabs, then you have used some form of OLAP.

Importing and Exporting

All export statements follow these same basic clauses, but you will find that some IISAM drivers have differing interpretations of what a database is. I'll do another couple of examples to demonstrate the differences. Here, we export the same data to Excel ADOConnection1.Execute('SELECT * INTO Customers ' + 'IN Northwind.xls Excel 8.0 FROM CUSTOMERS') A new Excel file called Northwind.xls is created in the application's current directory. A workbook called Customers is added, containing all of the data of the Customers table in Northwind.mdb. You can also export data to Excel by automating Excel, but if you have ever done this you will know that this ADO solution is simpler by far. In this example, the database is the directory, as it was for Paradox but not for Excel. The table name must include the .htm extension and, therefore, it must be enclosed in square brackets. Notice that the name of the IISAM driver is HTML Export , not just HTML , because this driver can only be used for...

Whats Next

In this chapter, I have discussed applications of Microsoft's COM technology, covering automation, documents, controls, and more. We've seen how Delphi makes the development of Automation servers and clients, and ActiveX controls, reasonably simple. Delphi even enables us to wrap components around Automation servers, such as Word and Excel.

Text Files

Of course, the grid indicates that the widths of the columns are 255 characters. You can change these just as we did in Excel by adding persistent fields or columns to the grid and then setting the relevant width property. Alternatively you can define the structure of the text file more specifically using SCHEMA.INI.

Using the Jet Engine

Now that you have some of the MDAC and ADO basics under your belt, we can take a moment out to look at the Jet engine. This engine is of great interest to some and of no interest to others. If you're interested in Access, Paradox, dBase, text, Excel, Lotus 1-2-3, or HTML, then this section is for you. If you have no interest in any of these formats, you can safely skip this section. Before we look at these formats, we should discuss the availability of the Jet engine. It was included with MDAC from v1.5c until, and including, v2.5. From MDAC v2.6, the Jet engine was dropped from MDAC (which accounts for the reduction in download size from 7.5 MB to 5.2 MB). You can download a distributable version of the Jet engine from www.microsoft.com data download.htm. Of course, if you have installed MDAC prior to v2.6, or Microsoft Access, Office, Excel, Visual Basic, or Visual C++ on your user's machine, then your user will already have the Jet engine. There are two Jet OLE DB providers the Jet...

OLE Automation

In the last chapter, we saw that you can use COM to let an executable file and a library share objects, and that this can be used to interact with the Windows shell. Most of the time, however, users want applications that can talk to each other. One of the approaches you can use for this goal is OLE Automation. After presenting a couple of examples that use custom interfaces based on type libraries, I'll cover the development of Word and Excel OLE controllers, showing how to transfer database information to those applications.