Problems using MS Query to access an Excel file

I imagine there are easier ways to work with this, but I want to use
"queries" on a set of data which is currently in an excel file.

However, when I try to do:

Data/Import External Data/NewDatabase Query/Excel Files ...
.... on the excel file. I get the following error message from Microsoft Query:

"This data source contains no visible tables"

What am I doing wrong? How do I set up the excel file so that it is clearly
seen as a table?

[It all works well when I do something similar with a very similar an Access
db file.]

Thanks in advance.

Select your data,
Do Insert=>Name=>Define

Enter Database under name and click Add.

then save your workbook and try it again.

--
Regards,
Tom Ogilvy

"alfredo" <[email protected]> wrote in message
news:[email protected]...
> I imagine there are easier ways to work with this, but I want to use
> "queries" on a set of data which is currently in an excel file.
>
> However, when I try to do:
>
> Data/Import External Data/NewDatabase Query/Excel Files ...
> ... on the excel file. I get the following error message from Microsoft
Query:
>
> "This data source contains no visible tables"
>
> What am I doing wrong? How do I set up the excel file so that it is
clearly
> seen as a table?
>
> [It all works well when I do something similar with a very similar an
Access
> db file.]
>
> Thanks in advance.
>
>

You've got to give MS Query a little help in finding the data list. It looks
for named ranges. :

In Excel. select your data list
Click in the Name Box (just above the Col_A column heading)
Enter your range name and press the [Enter] key
(That will create the range name)

Now, try using MS Query.


BTW, It's a good idea come up with a naming convention now, since range
names can be so helpful. I use prefixes: LU_ for lookup ranges (LU_MyData),
rng for data sources (rngMyData), ep_ for Essbase Pull ranges (ep_MyData),
etc. That way similar range types will group together when you display them
in Edit>Go To or with the[F3] key when putting them in formulas.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"alfredo" wrote:

> I imagine there are easier ways to work with this, but I want to use
> "queries" on a set of data which is currently in an excel file.
>
> However, when I try to do:
>
> Data/Import External Data/NewDatabase Query/Excel Files ...
> ... on the excel file. I get the following error message from Microsoft Query:
>
> "This data source contains no visible tables"
>
> What am I doing wrong? How do I set up the excel file so that it is clearly
> seen as a table?
>
> [It all works well when I do something similar with a very similar an Access
> db file.]
>
> Thanks in advance.
>
>

Thanks to both. Frighteningly simple if you know how!

Howwever, I am still getting an error message :

"Syntax error in FROM clause."

I will keep trying, as it is likely to be something I have done wrong.




"Tom Ogilvy" wrote:

> Select your data,
> Do Insert=>Name=>Define
>
> Enter Database under name and click Add.
>
> then save your workbook and try it again.
>
> --
> Regards,
> Tom Ogilvy
>
> "alfredo" <[email protected]> wrote in message
> news:[email protected]...
> > I imagine there are easier ways to work with this, but I want to use
> > "queries" on a set of data which is currently in an excel file.
> >
> > However, when I try to do:
> >
> > Data/Import External Data/NewDatabase Query/Excel Files ...
> > ... on the excel file. I get the following error message from Microsoft
> Query:
> >
> > "This data source contains no visible tables"
> >
> > What am I doing wrong? How do I set up the excel file so that it is
> clearly
> > seen as a table?
> >
> > [It all works well when I do something similar with a very similar an
> Access
> > db file.]
> >
> > Thanks in advance.
> >
> >
>
>
>