Posts Tagged ‘Integration’

« Older Entries

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:

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:

The Benefits of a Good Technical Editor

December 4th, 2009

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). 

» More: The Benefits of a Good Technical Editor

5 Comments »

Posted in Access Queries, Excel & Access Integration

Tags:

Easily Move Access Query Results to Excel

November 19th, 2009

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.

. » More: Easily Move Access Query Results to Excel

5 Comments »

Posted in Excel & Access Integration

Tags:

Documenting Access Queries in Excel

October 26th, 2009

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.

  » More: Documenting Access Queries in Excel

5 Comments »

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

Tags: