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:
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!
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
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.
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
I'm in the process of writing my Pulitzer worthy book "An Excel Analyst's guide to Access". During the writing process, chapters get reviewed by a team of editors – one of which is a Technical Editor.
The Technical Editor on this book is Dick Kusleika (Daily Dose of Excel). Kusleika is one of the better technical editors I've worked with. I think his accountant's detail-oriented brain lends itself nicely to tech editing.
Quick Joke: Did you hear about extroverted accountant? He looks down at your shoes.
.
During his editing, Kusleika pointed out that that the serial dates in Access actually start at December 31, 1899 (not January 1, 1900 like in Excel).
Just the other day, I saw someone run an Access Query in Office 2003. Then he selected Office Tools ->Analyze with Excel. This essentially sends the results of the Access query to Excel.
.
This if fine and dandy, but there is a slightly easier way to get the results of an Access query to Excel.
Clients usually have this pesky need to have everything documented. As a generally lazy person, this offends my lackadaisical attitude. Nevertheless, I typically oblige, given the fact that I want them to take over the work at some point.
Over the years, I've rolled my own documentation tools to help in this endeavor. Today, I want to share a small procedure I use to document Access queries in Excel.
An Access parameter query is a kind of interactive query that prompts you for criteria before the query is run. Parameter queries are useful when you need to ask the query different questions using different criteria each time you run it.
Now we all know you can pull data from Access into Excel using MS Query. The problem is that MS Query doesn't let you pull in Parameter queries. There may be a technical reason for this, but I like to think it's just Microsoft's way of keeping things interesting. After all, technical roadblocks are the spice of life.
If you've had enough spice, I'll show you a VBA workaround that will enable you to run an Access parameter query from Excel.
Today, I 'd like to show you a nifty trick that will allow you to send Variable length tables from Access to a specific Excel Range without code.
That's right, I said without any VBA coding. I know you're as excited as I am, so here we go.
