Integration » Bacon Bits:

Posts Tagged ‘Integration’

Running an Access Parameter Query from Excel

October 19th, 2009

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.

» More: Running an Access Parameter Query from Excel

70 Comments »

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

Sending Variable Access Data To An Excel Range

September 28th, 2009

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.

» More: Sending Variable Access Data To An Excel Range

6 Comments »

Posted in Excel & Access Integration

Tags: Access Excel Integration

Passing Multiple Values to One Parameter

August 5th, 2009

I often use MS Query to pull external data into Excel. I find it to be an extremely powerful way to quickly pull together interactive reporting without a lot of effort. If you’re new to MS Query, you may want to check out Kusleika’s intro to MS Query to get the gist.

Anyway, one of the more common requests I get is to allow a user to dynamically pass multiple criteria to a parameter. For example, a manager may want to pull in data for Tulsa, and Dallas, and Seattle at the same time.
» More: Passing Multiple Values to One Parameter

13 Comments »

Posted in Excel & Access Integration, MS Query

Tags: Excel Integration MS Query Power Tips

Using Access to Combine Multiple Excel Files: Method 2

July 27th, 2009

In my Pulitzer worthy post, Using Access to Combine Multiple Excel Files: Method 1, I showed you a simple way to leverage linked objects in Access to combine multiple Excel files.

This weekend, while checking out some of the newsgroups, I ran across a question from Ashish who asks:

“How can I specify the row number from where the data consolidation should start? I.e. I want to somehow exclude the rows (at the top of the 3 sheets) which I do not want to consolidate.”

In other words, Ashish needs to specify a particular range from which to pull and consolidate the data.

» More: Using Access to Combine Multiple Excel Files: Method 2

13 Comments »

Posted in Excel & Access Integration

Tags: Access Excel Integration Power Tips

Sending Data to Excel using Access SQL

July 22nd, 2009

There has to be a dozen ways to send data to Excel from Access. Today, I’d like to show you one of my favorite methods.

This method uses a simple Access SQL command that leverages the IN clause to point to an external destination database – in this case Excel.

» More: Sending Data to Excel using Access SQL

6 Comments »

Posted in Access VBA, Excel & Access Integration

Tags: Access Integration Power Tips VBA

Using Access to Combine Multiple Excel Files: Method 1

June 12th, 2009

Ahh, the classic story. Boy gets job. Boy becomes the department data collector. Boy meets 25 Admins who send him a spreadsheet every week. Boy copies and pastes into one workbook. Boy lights himself on fire.

Combining multiple Excel worksheets into one data table is such a common task, I would bet a pound of bacon that most of us have been through that ordeal once or twice.

The good news is that you can just scour the internet, and you’ll probably find dozens of techniques for combining multiple Excel files together.

Here’s one simple way you can leverage Access to combine multiple Excel files (without VBA).