Automating Excel Power In

10 Charts 175

Overview 175

Embedded Charts Versus ChartSheets 176

Embedded Charts in a ChartObject Container 176

Charts on a Chart Sheet Have No Container 178

Creating a Chart with VBA 178

Moving from Embedded to Chart Sheet and Vice-Versa 181

The Default Chart Type 181

Using Object Variables to Streamline Code 182

The Anatomy of a Chart 183

The Chart Area—VBA Name: ChartArea 183

The Plot Area—VBA Name: PlotArea 186

The Data Series—VBA Name:Series 188

The Chart Axes—VBA Name:Axis 189

Grid Lines—VBA Name: HasMajorGridlines and HasMinorGridlines 191

Data Labels—VBA Name: DataLabels and DataLabel 191

Chart Title, Legend, and Data Table—VBA Name: ChartTitle, HasLegend, and

HasDataTable 192

Trendlines and Error Bars—VBA Name:Trendlines and ErrorBar 193

Table of Chart Types 195

Details of Various Chart Types 198

Settings for 3D Charts 198

Settings for Pie Charts 199

Interactive Charts 201

Using Events with Charts 201

Exporting Charts as Images 202

Drawing with X-Y Charts 203

Custom Charts with VBA 204

Pie Bubble Chart 204

Speedometer Chart 204

Supply Curve Chart 205

Hierarchical Donut Chart 205

Next Steps 206

11 Data Mining with Advanced Filter 207

Advanced Filter Is Easier in VBA Than in Excel 207

Using Advanced Filter to Extract a Unique List of Values 208

Extracting a Unique List of Values with the User Interface 208

Extracting a Unique List of Values with VBA Code 209

Getting Unique Combinations of Two or More Fields 214

Using Advanced Filter with Criteria Ranges 215

Joining Multiple Criteria with a Logical OR 217

Joining Two Criteria with a Logical AND 217

Other Slightly Complex Criteria Ranges 218

The Most Complex Criteria—Replacing the List of Values with a Condition Created as the Result of a Formula 218

Being Prepared for No Records After the Filter 224

Using "Filter in Place" in Advanced Filter 225

Catching No Records When Using Filter In Place 225

Showing All Records After Filter In Place 226

Using Filter In Place with Unique Records Only 226

The Real Workhorse:xiFiiterCopy with All Records Instead of Unique Records Only 226

Copying All Columns 227

Copying a Subset of Columns and Reordering 228

AutoFilters 233

Next Steps 234

12 Pivot Tables 235

Versions 235

Creating a Vanilla Pivot Table in Excel Interface 236

Building a Pivot Table in Excel VBA 238

Getting a Sum Instead of Count 240

Cannot Move or Change Part of a Pivot Report 241

Figuring the Size of a Finished Pivot Table 241

Revenue by Customer for a Product Line Manager 243

Eliminating Blank Cells in the Data Area 245

Using AutoSort to Control the Sort Order 246

Controlling the Sort Order Manually 247

Changing the Default Number Format 247

Suppressing Subtotals for Multiple Row Fields 249

Suppressing Grand Total for Rows 250

Handling Additional Annoyances 250

Creating a New Workbook to Hold the Report 250

Moving the Summary to a Blank Report Worksheet 251

Filling in the Outline View 252

Final Formatting 253

Adding Subtotals 254

Putting It All Together 255

Product Profitability—Issues with Two or More Data Fields 258

Defining Calculated Data Fields 260

Avoid Calculated Items 263

Summarizing Date Fields with Grouping 264

Grouping by Week 267

Measuring Order Lead Time by Grouping Two Date Fields 269

Advanced Pivot Table Techniques 271

Using the Top 10 AutoShow Feature to Produce Executive Overviews 271

Using Pivot Table showDetaii to Filter a Recordset 274

Using a Page Field to Create Reports for Each Region or Product 276

Manually Filtering to Two or More Items in a PivotFieid 280

Sum,Average,Count,Min,Max,and More 284

Reporting Percentages 286

Percentage of Total 286

Percentage Growth from Previous Month 286

Percentage of a Specific Item 287

Running Total 287

Next Steps 289

13 Excel Power 291

Using VBA to Extend Excel 291

Conditional Formatting with More Than Three Conditions 291

AutoFilter with More Than Two Conditions 292

File Operations 293

List Files in a Directory 293

Delete a Workbook After a Specific Date 295

Close and Delete 295

Import CSV 297

Read Entire CSV to Memory and Parse 298

Combining and Separating Workbooks 299

Separate Worksheets into Workbooks 299

Combine Workbooks 300

Filter and Copy Data to Separate Worksheets 301

Export Data to Word 302

Working with Cell Comments 303

List Comments 303

Resize Comments 304

Resize Comments with Centering 305

Place a Chart in a Comment 306

Utilities to Wow Your Clients 308

Using Conditional Formatting to Highlight Selected Cell 308

Highlight Selected Cell Without Using Conditional Formatting 309

Custom Transpose Data 310

Select/Deselect Non-contiguous Cells 311

Techniques for VBA Pros 313

Speedy Page Setup 313

Calculating Time to Execute Code 315

Disable Cut,Copy,and Paste 316

Custom Sort Order 318

Cell Progress Indicator 319

Protected Password Box 320

Change Case 322

Custom Delete Event 323

Selecting with Speciaiceiis 324

Delete Rows with Conditions 325

Hide the Formula Bar 326

Cool Application 327

Historical Stock/Fund Quotes 327

Disable Cut,Copy,and Paste 328

Next Steps 330

14 Reading from and Writing to the Web 331

Getting Data from the Web 331

Manually Creating a Web Query and Refreshing with VBA 332

Using VBA to Update an Existing Web Query 333

Building a New Web Query with VBA 334

Using Streaming Data 336

Using Appiication.onTime to Periodically Analyze Data 337

Scheduled Procedures Require Ready Mode 337

Specifying a Window of Time for an Update 338

Cancelling a Previously Scheduled Macro 338

Closing Excel Cancels All Pending Scheduled Macros 339

Scheduling a Macro to Run x Minutes in the Future 339

Scheduling a Macro to Run Every Two Minutes 339

Publishing Data to a Web Page 341

Using VBA to Create Custom Web Pages 343

Using Excel as a Content Management System 344

Bonus—FTP from Excel 347

Next Steps 347

15 XML in Excel 2003 Professional 349

What Is XML? 349

Simple XML Rules 350

Universal File Format 350

XML as the New Universal File Format 351

The Alphabet Soup of XML 352

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

Next Steps 358

16 Automating Word 359

Early Binding 359

Compile Error: Can't Find Object or Library 361

Late Binding 362

Creating and Referencing Objects 363

Keyword New 363

Createobject Function 363

Getobject Function 363

Word's Objects 364

Document Object 365

Selection Object 367

Range Object 368

Bookmarks 371

Next Steps 376

0 0

Post a comment