Importing SQL tables dynamically into Excel

[Mike Brown - Process Manager]

Hi All,

As you will be able to tell from my post, I'm very much new at this. I'm an
operations manager for a small company, and I have enough IT experience that
I've been thrown in to the roles of Network Admin and DB Admin. Enough
small talk

We have recently implemented MSDE 2000 Rel A (as an upgrade from our old Jet
database) on Windows 2000 Server, and I have been tinkering with reporting.
The entire idea of switching was to make things more efficient, so I'm
trying to stop using Access for all of our reporting, and switch it to Excel
2002. My question is sort of long, and I'll phrase it as best I can:

In our audit, we enter information about computer equipment, such as Source,
Date Acquired, Equipment Type, Brand, Model, Test Status, etc. What I need
to design is an Excel "template" (per se) wherein our customer-oriented
folks can choose data, most likely a Source and Date Acquired, and generate
a report of the equipment that came in from that source on that day. I've
been tinkering a bit with VBA and pivot tables for this, and the pivots seem
to be what I need to use, but I just haven't quite gotten it right. I've
been all over Google Groups, MSDN, and every other web/print resource I have
access to.

Using MS Query is simple enough to generate sales reports, as they are
pretty much static data and can be created and printed one time. Keeping
these other reports updated dynamically has been my bane, however. If
anyone has suggestions or links to good information, or knows of a better
place to post this question, TIA.

 

[Dick Kusleika]

Mike

If I were doing this, I would create a parameter query that got its values
from an Excel range. I wouldn't use a pivot table based on what you said.
Rather, I would only return those records to the External Data Table that
matched criteria.

Here's the steps I might take to do that. Let me know if you need more
details.

Go to Data - Get External Data - New Database Query. Select your database,
table, fields, etc..

In the Criteria dialog of the External Data Wizard, limit the rows returned
on Source and Date Acquired. It won't matter what values you use to limit
as we will be changing this later.

On the last dialog of the Wizard, choose to Edit in MSQuery.

In MSQuery, go to the Criteria pane - it will show the criteria that you've
set up. Change the values for those criteria to prompt strings, that is,
string enclosed in brackets[]. For Source, you may put something like

[Enter Source]

It won't matter what you put, because we will be changing it later.

Return the data to Excel (File menu). When you do, you will be prompted to
enter your parameter values. Enter something, but it doesn't matter what.
When asked where to put it in Excel, start a couple of rows down so you can
use the first few rows to enter the parameter. Start at say A5.

Back in Excel, the External Data Toolbar should be visible. Click the
Parameters button. You should have two parameter in the listbox on the
left. You can now change these parameters to get their values from ranges,
for example, B1 and B2.

Now the user can enter a Source in B1 and an Acquired Data in B2 and the
table will refresh to show records that meet those criteria.

Does that sound like what you want?

 

[Mike Brown - Process Manager]

Mike

If I were doing this, I would create a parameter query that got its values
from an Excel range. I wouldn't use a pivot table based on what you said.
Rather, I would only return those records to the External Data Table that
matched criteria.

Here's the steps I might take to do that. Let me know if you need more
details.

Go to Data - Get External Data - New Database Query. Select your database,
table, fields, etc..

In the Criteria dialog of the External Data Wizard, limit the rows returned
on Source and Date Acquired. It won't matter what values you use to limit
as we will be changing this later.

On the last dialog of the Wizard, choose to Edit in MSQuery.

In MSQuery, go to the Criteria pane - it will show the criteria that you've
set up. Change the values for those criteria to prompt strings, that is,
string enclosed in brackets[]. For Source, you may put something like

[Enter Source]

It won't matter what you put, because we will be changing it later.

Return the data to Excel (File menu). When you do, you will be prompted to
enter your parameter values. Enter something, but it doesn't matter what.
When asked where to put it in Excel, start a couple of rows down so you can
use the first few rows to enter the parameter. Start at say A5.

Back in Excel, the External Data Toolbar should be visible. Click the
Parameters button. You should have two parameter in the listbox on the
left. You can now change these parameters to get their values from ranges,
for example, B1 and B2.

Now the user can enter a Source in B1 and an Acquired Data in B2 and the
table will refresh to show records that meet those criteria.

Does that sound like what you want?

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Mike Brown - Process Manager"

wrote in message news:[email protected]... I'm

Dick,

This was exactly what was required. With some small tweaks this worked like
a charm. Thanks a ton.

Mike

 

[Dick Kusleika]

Dick,

This was exactly what was required. With some small tweaks this worked like
a charm. Thanks a ton.

Mike

You're welcome.