Archive for the ‘Access VBA’ Category

Capture a User’s Domain\UserName

February 28th, 2011

When developing your Excel or Access application, it’s not uncommon to need to capture your user’s domain/username for one reason or another. A user’s domain/username combination is the traditional unique identifier for a user - often captured by programmers to do things in like:

  • Help manage user login and permissions
  • Create a history of who used the application
  • Capture the name of each user who made the changes to data
  • Personalize outputs and program messages for users

.
There are literally half a dozen ways to capture a user’s domain name and user name. In this post, I’ll give you a very easy method using the Environ function.

. » More: Capture a User’s Domain\UserName

Using Flash Progress Indicators

February 3rd, 2011

Besides being an Excel and Access guru, I moonlight as an Xcelsius expert. Because Xcelsius is so closely tied to Excel, I’ve always seen Xcelsius as an extension of the Excel BI stack. But that’s a conversation for another time.

.

A while ago, Ryan Goodman created and posted a few Flash progress indicators on his Xcelsius blog. These are Flash .swf files designed to be used as loaders for Xclesius dashboards.

Â

.

I recently had an opportunity to work on a project where I could implement these loaders into an Excel dashboard as progress indicators – a lazy man’s progress bar if you will.
» More: Using Flash Progress Indicators

For God Sake Please Use End If

November 15th, 2010

I think I’m turning into a curmudgeon. Yesterday, I was reminded how much I hate when programmers don’t use end if.  That is to say, they use the IF…THEN statement, but leave off END IF. Some of you beginners are thinking “How can you even do that”?

. » More: For God Sake Please Use End If

Creating Access UDFs that Accept Arguments

November 4th, 2010

This week, I showed you how to Create and Use your own Access User Defined Functions (UDFs). Today, I’d like to take that one step further and show you how to create an Access UDF that accepts arguments.

  » More: Creating Access UDFs that Accept Arguments

Creating and Using User Defined Functions in Access

November 2nd, 2010

We haven’t had an Access post in a while, so today, I’ll show Access some love.

We all know that we can create our own User Defined Functions in Excel. Well, did you know you can also create and use your own User Defined Functions in Access?

  » More: Creating and Using User Defined Functions in Access

Use Windows Authentication for your Excel and Access Applications

October 20th, 2010

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

Saving Time with Run Commands

September 27th, 2010

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)

  » More: Saving Time with Run Commands

VBA Hover ToolTip Trick

September 14th, 2010

I’m pretty busy today so here’s a quick VBA tip.

We all know if you hover over a variable in VBA (while in debug mode) you can see the value of that variable.  This allows you to see the values that are being passed in and out of variables – very useful.  But by my estimation, these tooltips can only hold 77 characters (including the variable name).  This basically means if the value in your variable is too long, it gets cut off.

 

Here’s an example.  Hovering over the variable TempDataFile allows me to see the string that represents the file name, but that string gets cut off.
» More: VBA Hover ToolTip Trick

Running an Excel Macro from Access (or Another Excel Workbook)

August 31st, 2010

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)

Integrating Xcelsius with Excel and Access

February 10th, 2010

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