The Excel Team Blog : Power Tips

VBA tip: Limit the number of times a file can be opened

Excel Blog — Wed, 04 Jan 2012 19:51:00 GMT

Suppose you want to send someone a demo workbook, but you don’t want that workbook to be used more than a certain number of times, perhaps because you want to be paid for your work. In today's VBA tip, Excel MVP Bob Umlas shows you how to limit the number of times a file can be used by using simple SaveSetting and GetSetting statements. ...( read more ) Read More......(read more)

Pulling RSS data into Excel (or: Using Excel to Search Craigslist) - Part 1

Microsoft Excel 2010 — Tue, 12 Oct 2010 19:24:17 GMT

This blog post is brought to you by Dan Battagin a Lead Program Manager on the Excel team. OK, so I'm going to talk a bit about a relatively unknown feature in Excel: XML data import. It was introduced in Excel 2003, but we've done a pretty good job hiding it since Excel 2007 by putting it on the Developer tab of the Ribbon. I'm going to make the topic even a bit more geeky by using it in conjunction with VBA. In exchange, I'm going to produce, at the end, a nice little solution...(read more)

Auto Format PivotTables to Match Source Data (Power Tips Series)

Microsoft Excel 2010 — Wed, 22 Sep 2010 00:31:46 GMT

Today’s author is Mike Alexander, a Microsoft Excel MVP who shows us how to run a Stored Procedure to get data from a SQL server. For more information, visit www.datapigtechnologies.com . So summer is for practically over and I'm back to blogging topics that can actually help people. For my first post back, I'll come out big with one of the best pieces of code I've ever written. A few weeks ago Dick Kusleika posted a small but brilliant piece of code that auto formats the...(read more)

You Asked: What the heck is Scroll Lock

Microsoft Excel 2010 — Wed, 25 Aug 2010 06:52:37 GMT

This week’s post is written by Amy Miller. Amy is a writer for Office.com. She’s written and edited content for Excel, Access, OneNote, and InfoPath. Imagine you’re working in a spreadsheet and you innocently press the arrow keys on your keyboard to move to another cell, but instead of moving to another cell, the entire spreadsheet moves. You may have asked yourself, what the heck just happened, and how do I make it stop? Lucky you. You’ve encountered that pesky little problem called...(read more)

Importing Text Files in an Excel Sheet

Microsoft Excel 2010 — Fri, 11 Jun 2010 00:25:25 GMT

Today’s author is Jan Karel Pieterse, an Excel MVP, who describes the process of importing text in great detail. You can find more useful tips from Jan Karel on his website: http://www.jkp-ads.com/ . In this article, I'll explain how to ease importing of .txt, .prn and/or .csv files into an existing sheet in Excel. Introduction If you've ever tried to open a text file, you have probably used the Text import wizard. If you have ever opened a CSV file, you have probably noted you can't...(read more)

Solutions to Three Common Problems when Using VLOOKUP()

Microsoft Excel 2010 — Fri, 11 Jun 2010 00:23:48 GMT

Today’s author is Greg Truby, an Excel MVP, who addresses some common issues you may encounter when you use the VLOOKUP function. This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data. When using VLOOKUP() we frequently find ourselves facing three common problems: · We...(read more)

Running a SQL Stored Procedure from Excel (No VBA)

Microsoft Excel 2010 — Mon, 07 Jun 2010 23:04:03 GMT

Today’s author is Mike Alexander, an Excel MVP who shows us how to run a Stored Procedure to get data from a SQL server. For more useful articles and videos, visit www.datapigtechnologies.com . We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better. So today...(read more)

Creating a TOC with Hyperlinks Programmatically

Microsoft Excel 2010 — Fri, 30 Apr 2010 18:02:19 GMT

Today's author is Dennis Wallentin, XL-Dennis, of http://xldennis.wordpress.com/ A few weeks ago we posted a code example for creating a table of contents for your workbook. Dennis offers another approach with this code sample for creating a TOC with hyperlinks. Dennis’ code uses the PageSetup.Pages().Count property, introduced in Excel 2007, to calculate the number of pages on each sheet. In addition, the entries in the TOC link to their respective sheets to improve on-screen workbook navigation...(read more)

Creating Address Labels from a Spreadsheet Programmatically

Microsoft Excel 2010 — Wed, 14 Apr 2010 08:00:00 GMT

Today's author is MVP Bill Jelen of MrExcel.com . The following code example takes a list of addresses arranged as one address per row, and copies them onto another sheet, arranging them to fit on printable address labels. The workbook must contain two sheets, one named "Addresses" and one named "Labels". The addresses on the Addresses sheet must be arranged as one address per row, with the Name in Column A, Address Line 1 in Column B, Address Line 2 in Column C, and the City...(read more)

Add a Table of Contents to your Workbook Programmatically

Microsoft Excel 2010 — Wed, 07 Apr 2010 20:53:00 GMT

Today's author is MVP Bill Jelen of MrExcel.com . The following code example verifies that a sheet named "TOC" already exists. If it exists, the example updates the table of contents. Otherwise, the example creates a new TOC sheet at the beginning of the workbook. The name of each worksheet, along with the corresponding printed page numbers, is listed in the table of contents. To retrieve the page numbers the example opens the Print Preview dialog box. You must close the dialog box...(read more)

Where to Find the Form Controls

Microsoft Excel 2010 — Sat, 13 Feb 2010 00:50:20 GMT

Today’s author is Jan Karel Pieterse, an Excel MVP. In this article, he shows the different locations of the form controls in Excel. You can find additional details on how to use these controls on his web site: http://www.jkp-ads.com/articles/controls.asp . Excel 2007 and 2010 In Excel 2007 and 2010, the form controls and control toolbox controls are slightly hidden. First of all, you need to show the Developer tab in the ribbon. Here is how that's done: In Excel 2007, click the Microsoft Office...(read more)

Report statistics about a Set of Workbooks

Microsoft Excel 2010 — Thu, 07 Jan 2010 17:48:52 GMT

Today’s contributor is Sarah Hothersall, Liaison Director at Lyquidity. She’s providing information about the Workbook Statistics add-in, a free tool for Excel users that was created by Sandy Marshall, a Product Manager at Lyquidity, who is in charge of development of ComplyXL for enterprise spreadsheet management. To download this add-in, visit http://www.lyquidity.com/workbookstatistics . Sometimes it is helpful to report statistics about a set of workbooks contained in a folder. You can use this...(read more)

Preventing auto_open and Workbook_Open events from running

Microsoft Excel — Tue, 15 Dec 2009 19:20:52 GMT

Today’s author is Jan Karel Pieterse, an Excel MVP. You can find more useful tips from Jan Karel on his website: http://www.jkp-ads.com/ . This post informs you about Name Manager, a free utility that helps you manage defined names in your workbooks. As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running. This little article shows you how to achieve that...(read more)

Daniel’s Extreme Lookup Collection

Microsoft Excel — Thu, 10 Dec 2009 23:08:53 GMT

Today’s author is Daniel Wiesenfeld, an Excel and Access Power User who is sharing his Extreme Lookup Collection with us so we can use the Excel User Defined Functions (UDFs) he created to enhance the lookup functionality. His web site daanalytics.com is currently under construction and should be available soon. In the Visual Basic Editor, insert a Module and paste the following code: ' XVLOOKUP (& XHLOOKUP) ' Works just like a vlookup (and hlookup) except that the user refers to a lookup...(read more)

Create conditional drop-down lists

Microsoft Excel — Tue, 24 Nov 2009 19:46:09 GMT

Today’s author is Reinout Dorreboom from the Netherlands, a Technical Consultant at Getronics, where he has worked with Office applications for many years, and where his Microsoft Certified Training skills enabled him to help other people get up to speed with Excel. In Excel 2007 (and earlier), it’s possible to create a drop-down list. By using the INDIRECT function, you can then create additional drop-down lists that are conditional to the first drop-down list. In this example we’ll create the following...(read more)