Struggling with MS Query...

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I am learning about MS Query from a book. I am falling at
the first hurdle...

I have an Excel file on my desktop that I want to be able
to query. It contains list of numbers with column headers
(i.e. your standard table layout)

As I understand it, I need to specify that Excel file as
a data source. So I select <New Database Query> and get
the <Choose Data Source> dialog box. Now I get confused...

I have tried using <New Data Source>. Here I get a list
of 'drivers' to select from. I selected 'Excel Driver'as
the most obvious (as I want to query an Excel file. I
presume if I wanted to query an Access file I would
choose the access driver???).

So I name my new data source and it appears in the
<Choose Data Source> dialog box. I then run my query
using that but I get a message saying 'No Table could be
found'?????

What am I missing? Do I have to format my Excel files in
a different way so that they can be accessed by Query?
What is all this .dsn file extension business?

I am truly at an impasse.

Any clues?
 
In the Query wizard look under options and check system tables and see if
that helps.

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Alex, select the data in your Excel file and give it a name (Insert > Name >
Define). That will take care of the "no Table found" error message.

Question: Why are you using MS Query in the first place? If all you want to
do is query the Excel file that's open in front of you, just use the
AutoFilter or Advanced Filter features of Excel. No need to use MS Query.

Use MS Query to pull external data into Excel for analysis. So, for example,
if you have data stored in an Access database, you can use Query to retrieve
that data into Excel. Same thing if the data is stored in an Excel file
somewhere, and you just want to grab a piece of it for the current file.
Retrieving the data through Query means you don't have to copy and paste.
As I understand it, I need to specify that Excel file as
a data source. So I select <New Database Query> and get
the <Choose Data Source> dialog box.
Click to expand...

Yes, an Excel file is your data source, but you don't have to select New
Query. You can navigate directly to the data file. You would create a data
source if you intend to work with this external file on a regular basis.
Think of the "data source" as a shorcut to the file. Using the saved data
source means you don't have to navigate to the file.
I have tried using <New Data Source>. Here I get a list
of 'drivers' to select from. I selected 'Excel Driver'as
the most obvious (as I want to query an Excel file. I
presume if I wanted to query an Access file I would
choose the access driver???).
Click to expand...

Correct and correct, if you want to save the data source for later use, as
opposed to simply navigating to the file each time, as I said above.
So I name my new data source and it appears in the
<Choose Data Source> dialog box.
Click to expand...

Which means that from now on, you can select the data source name rather
than navigate to the date file.
What is all this .dsn file extension business?
Click to expand...

It's the extension MS Query gives to a saved data source file. Again, think
of it as a shortcut to the data source file you navigated to when you
created the data source.

Hope this helps.
 
DDM

Ok. So I got that working now. Yes, I wanted to import
small snippets of data from one Excel file into another.

One more question if that is ok...?

You suggested I define my Table with a name. I did that
and everything was fine.

I read in my Excel 2000 guide book that if I define a
Table as 'Database' then the table becomes 'dynamic' and
if I add new rows it will automatically update the range
defined by 'database'. This much is true.

However, if I now run MS Query on that Table defined
as 'database' it will not work. MS Query will not show
the values in the columns. I am guessing but am I to
assume the MS Query Wizard will not work on dynamic
tables?

Thanks again. Your help was hugely appreciated.
 
Alex, so far as I can see, MS Query won't take dynamic data ranges. What you
can do instead is take the approach that Peo Sjoblom proposed in his post.
In the Query Wizard, right after you select the file, you'll see an Options
button. Click that and check the View System tables button. Then select the
worksheet the data is on. This will allow you to pull in the data whether it
is named or not, and it will pull in all the data (unless you decide to
filter it later on).
 
...
Alex, so far as I can see, MS Query won't take dynamic data ranges.
Click to expand...

This is why IMO using a defined Name ('named range') is not great
advice (also see the current post about the 'Can't expand named range'
error). The later advice is better i.e. when the sheet name is used
then the provider will determine the number of rows and the columns
e.g.

SELECT * FROM [Sheet1$];

If you need to limit the area for the provider to look in, use a range
address e.g.

SELECT * FROM [Sheet1$A1:C65536];

means the provider will only look in the first three worksheet
columns. Although the maximum number of rows is specified, the
provider will only return the 'used' rows i.e. those that currently or
previously contained data.

Of course, in production the SELECT * will be replaced with a list of
column names.

Jamie.

--
 
Back
Top