In my life-changing post, Running a SQL-Stored Procedure from Excel, I showed you how to make Excel interact with a SQL server Stored Procedure.
.
Since that post, I’ve had several requests to cover the topic of passing dynamic parameters to a Stored Procedure. So today, I will FINALLY show you.
» More: Running a SQL Stored Procedure from Excel with Dynamic Parameters
Over the Thanksgiving holiday, I was ignoring the family and puttering around on the internet. There, I found a list of all the cool API calls you can do through VBA. An API call is essentially a piece of code you can use to make the Windows operating system do things – like open a directory, freeze the system, open a dialog box, etc.
.
Well, there is an API Call that essentially opens your CD Rom drive. That’s right – you can implement code so you don’t have to go through the painstaking task of pressing the Open button for your CD Rom drive. Wow …. AUTOMATION IS THE BEST!
.
OK…this gets my vote for the most useless API call. Second place is the API call to close the CD Rom Drive.
.
That being said, you could use this useless API to prank your coworkers.
» More: Open CD Rom – The Most Useless API Call
Last week, Debra over at Contextures posted an article that showed us lots of nifty ways to navigate a workbook that has many worksheets. Today, I’d like to add my two cents by sharing a small macro that will give you a fairly easy way to jump from sheet to sheet.
.
We all have them – those workbooks that have 10, 15, 20 or more tabs.
I personally have lots of training workbooks that have 15+ tabs.
When you’ve got a workbook that has this many sheets, it can be a pain to find the sheet you need.
» More: Easily Navigate Many Worksheets
Here’s an easy tip from Excel MVP Bob Umlas – author of the new book, Excel Outside the Box.
.
Let’s say you want to fill in some rows with the 1st and the 15th of every month.
» More: Use Fill Handle to Fill in the 1st and 15th of Each Month
Last week, I was working with some data when I encountered the need to merge two staggered columns.
That is to say, I needed to merge two columns where the data was blank in either one column or the other.
.
Here is an example.
» More: Merging Two Staggered Columns
One of my 12 fans (Lester) emailed to remind me that I have a blog. I almost forgot. So with a good kick in the pants, I’m back to blogging.
.
While I was away, Microsoft awarded me with the coveted Microsoft Excel MVP award for the fourth time. So I guess I better start earning that distinction here. Here we go.
.
Today, I’ll show you a nifty trick that allows you to actually see the AutoFilter criteria you have applied on an Excel table.
.
This is actually a pretty common request. When you’ve got an AutoFilter applied, it would often be nice to see how the table is actually AutoFiltered. Alas, Excel’s only accommodation is to give you a generic count of records on the status bar.
.
.
I’ve come up with a piece of code that will give you more details in the status bar. Like this:
.
.
Here is how you can implement this:
I’m back from the Power Analyst Bootcamp in Washington DC – and of course, it was a success. 37 of the finest analysts attended the 2-day event, where we shared our passion for Excel, deli meats, and spicy tricks and tips. In the end, everyone left with a bag full of new techniques that will make them better analysts.
.
While I was gone, there seems to have been a flurry of activity and questions in reference to hacking into a protected workbook. A couple of years ago, I posted a slick technique you can use to hack into a protected 2007 worksheet. Apparently, hacking in a protected workbook is also a highly desired talent. So today, I’ll walk through the steps to hack into a protected workbook.
.
Excel 2007 and 2010 files are essentially zipped packages that contain XML files. This means that if you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file. Not only that – you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.
» More: Hack into a protected Excel 2007 or 2010 Workbook
About a week ago, Bill Jelen sent me an email with a challenge to figure out the nifty trick he shows in this YouTube Video. Inspired by the video, I decided to use the trick in a simple dashboard model.
.
What I ended up with is this cool technique that allows me to replace my old Form Controls with Pivot Slicers. As I select each slicer, my dashboard responds as it would would if I were using a set of Form Controls. Keep in mind that the charts and other components in my dashboard are in no way connected to a pivot table.
» More: Use Pivot Slicers as Form Controls
» More: How to Compress xlsx Files to the Smallest Possible Size
One of the best practices of dashboard building is to avoid overwhelming your customers with too much data at one time – especially in a chart, where they can lose sight of the primary message if focusing on inconsequential data. One of the common ways dashboard designers help focus the message of a chart is to limit the data labels to only the key points.
.
Once I have only two data labels, my eyes immediately take in the message of this chart (that we went from 11 to 53).
» More: Automatically Label First and Last Chart Points
