I’ve decided to dedicate this week to using Recordsets in Excel. Not to connect to external data sources, but to specifically use them to simplify internal Excel tasks.
Today, I’ll show you how I typically use a Recordset to fill a ComboBox with unique values from a table.
» More: Recordset Tricks in Excel 1 – Filling a ComboBox with Unique Values
I’m fresh from the DataPig Power Analyst Bootcamp. As usual, it was a blast. I’d like to give a shout out to Earl who taught me more than I really need to know about the chicken breeding business (he works for a company called Aviagen).
Anyway, during the bootcamp, someone asked how they can work windows authentication into their Excel or Access application. It just so happens that I have a hack for that!
» More: Use Windows Authentication for your Excel and Access Applications
I’m often forced to work on other people’s PCs. Over the years, I’ve encountered all kinds of configurations that defy the notion that every Windows build has the same programs in the same location.
.
This puts me in the unsavory position of digging through their Windows menus to find the program I need. I must of have spent hours staring over cluttered desktops and incomplete Start menus. Not to mention all kinds of wallpaper pictures of red-eyed children in front of the Christmas tree. I mean really, don’t any of these people have Photoshop? But I digress.
I’ve learned to save time and my sanity by using the Run command. When you click Start, you will see the Run command. NOTE: If you have Windows Vista or Windows 7, you need to explicitly Enable the Run Command)
One of the more common questions I get revolves around running an Excel macro from Access. That is, how do you fire an existing Excel macro from Access.
I’ll share the code to do that in a moment.
I first want to point out that this code can also come in handy if you need to run an external Excel macro in another workbook. That is to say, while you are working in an Excel file, you can reach out and run a macro in another workbook.
» More: Running an Excel Macro from Access (or Another Excel Workbook)
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 active pivot data field to a number format without having to muddle through the formatting dialog boxes. I loved it and used it for a while. Then I thought:
» More: Auto Format PivotTables to Match Source Data
During one of my nerdy adventures, I came across the need to selectively hide AutoFilter Dropdowns. That is to say, apply an AutoFilter to a table, but only let the user see and interact with one or two of the dropdowns. Here’s the deal:
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, I’ll show you how to easily make Excel run a Stored Procedure to get data.
One of the hardest things to manage in Excel solutions is version control. You know, that embarrassing knowledge that there are orphaned versions of your application floating through your company. And that horrifying sense that these useless versions are actually being used. There is nothing more embarrassing than asking one of your users to try and catch the splash screen to read off the version number. That’s just sad dude…sad.
In this post, I’ll show you a couple of techniques that will help you hack your own version control for your Excel solutions (BTW: This works for Access too).
Being the complete nerd that I am, I work with lots of different technologies. But in the end, I always strive to make those technologies work with Excel. One of those is Xcelsius. Xcelsius is a dashboarding application that takes data and spices it up into slick looking flash presentations.
Those of you who have already been asked to create reporting in Xcelsius may have found it a bit difficult to use. No VBA, no easy way to get connections and no pivot tables.
Well for the upcoming Xcelsius Developer’s Workshop, I’ve come up with a technique where you can embed Xcelsius dashboards into Excel and Access applications.
This allows you to get all the flashy benefits of Xcelsius, but still give users a familiar environment where they can use PivotTables, VBA, MSQuery, etc.
It’s too complicated to show with pictures, so I cooked up this video. Enjoy!
» More: Integrating Xcelsius with Excel and Access
I recently posted about creating a Zoom Box in Excel . Alan writes in the comments:
“One of the features in Access that I really like is the ability to set a font size. Older people are sometime vision impaired, but you will learn about that in time. Is it possible to increase the font in the zoom box or set a font option?”
Anything for one of my 12 fans Alan. The steps are outlined below.
