Posts Tagged ‘Access’

« Older Entries
Newer Entries »

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

7 Comments »

Posted in Access VBA, Excel VBA

Tags:

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)

7 Comments »

Posted in Access VBA, Excel & Access Integration, Excel VBA

Tags:

Converting Text to Proper Case in Access

August 16th, 2010

Wendy writes to ask:

“In Excel, I can use the PROPER function to change my text to proper case. Why is there is no PROPER function in Access? “

Wendy has stumbled on one of the mysteries of life – there are many functions you use in Excel that are not found in Access. Well…it’s not really a mystery. I actually have an idea why this may be.

  » More: Converting Text to Proper Case in Access

8 Comments »

Posted in Access Functions, Access Queries, Excel & Access Integration

Tags:

Can’t Export More than 65,000 Rows to Excel 2007?

April 22nd, 2010

Kusleika and I are at the Excel and Access Power User Workshop this week. Between you and me, he’s not doing that well.

Anyway, one of our attendees asked why Access 2007 can’t export more than 65,000 rows to Excel 2007. I replied with my usual professorial demeanor “What? You’re on crack!”

This is when he demonstrated these steps:

» More: Can’t Export More than 65,000 Rows to Excel 2007?

8 Comments »

Posted in Access Tips and Tricks, Excel & Access Integration

Tags:

Adding Trend Icons to Access Queries

March 23rd, 2010

With the release of Office 2007, Excel introduced cool new conditional formatting rules that gave you the ability to show dashboard-esque icons inside cells. With these icons, you can represent performance using different shapes and colors.

 

As usual, Access gets the short end of the visualization stick. No such functionality exists in your cache of Access tools. But today, I’ll show you how to hack your way into showing dashboard-esque icons in Access queries.

  » More: Adding Trend Icons to Access Queries

Leave your comment »

Posted in Access Queries, Access Tips and Tricks, Visualizations

Tags:

The Case for Access

March 10th, 2010

Amazon says it’s official. My new book will be out on April 5th.

This book is written for all you Excel users who know that expanding your skill-set to include Access can make you more productive. I guide you through analytical and reporting benefits of Access, and show you how you can integrate Excel and Access to make your life easier.

 

To celebrate this new arrival, I’ve decided to post Chapter 1 of this book as today’s blog entry. Enjoy!

» More: The Case for Access

26 Comments »

Posted in Excel & Access Integration, ~ Bacon Bits

Tags:

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

8 Comments »

Posted in Access VBA, Excel VBA, Visualizations, Xcelsius

Tags:

Avoiding Data Cut-Off in Excel

February 3rd, 2010

It’s been a busy week and I’ve been working hard. I think I’ll reward myself by writing a blog post for people I’ve never met. Here we go.

Let’s talk about getting Access Memo Fields into Excel.

  » More: Avoiding Data Cut-Off in Excel

6 Comments »

Posted in Access Tips and Tricks, Excel & Access Integration, Excel Tips and Tricks

Tags:

Clearing Access ImportError Tables

January 26th, 2010

If you move data from Excel to Access on a regular basis, you’ll know that sometimes Access can’t resolve the data being imported from Excel. In these cases Access automatically creates a new table called ImportErrors..
. » More: Clearing Access ImportError Tables

2 Comments »

Posted in Access Tips and Tricks, Access VBA, Excel & Access Integration

Tags:

Using Dashboard Graphics in Access

January 7th, 2010

Let’s face it, Access isn’t typically a tool you would think of when considering a dashboarding platform. The reporting tools in Access, as slick as they are, don’t lend themselves to visualizations.

That is, Access doesn’t offer a whole lot in the way of dashboarding graphics. Sure, it has charts and conditional formatting, but nothing like Excel.

 

In this post, I’ll show you a hack to spruce up your Access reporting by using your own dashboarding graphics.

  » More: Using Dashboard Graphics in Access

15 Comments »

Posted in Access Queries, Access Tips and Tricks, Visualizations

Tags: