MsQuery in Excel not working....'Database has no tables' 1

[nikkiswin]

I have made a template using the Template Wizard function and have exported some data to an associated Excel List (stored as a .xls file). Now when I try to use Data\Get External Data to retrieve the data and run a query I get a message along the lines of 'Database has no tables'. I have tried using the ODBC driver for Excel Files and Excel Lists and neither seems to work.
Any suggestions out there???

 

[acron]

Oddly enough you can Get external data no problem form a text file, but if you want to use an Excel worksheet you need to have applied a range name to the data you wish to access.

Try naming the data and try setting up your query again.

A.C.

 

[Dreamboat]

acron: It sounds like we're running FROM Excel, not from Access.

nikkiswin: If the tables really exist, then are they hidden? You can't get data from hidden tables, I don't think. Anne Troy

http://www.TheOfficeExperts.com

 

[nikkiswin]

Excel created the Database file as part of the Template Wizard process so if it 'forgot' to create a table for itself...
Is there some sort of 'make table' process I need to go through?
(When using Excel lists in the past I have never had do to anything to them.)
Nikki

 

[acron]

Dreamboat,

I understand that the problem arises in Excel, and that is the position I addressed. To get get external data from an xl workbook into xl using MsQuery, you need to have the data range named.

nikkiswin,

Could you amend the template to apply a name to the range you wish to work with.

A.C.

 

[dkriese]

Office XP/Excel 2002 Yes, you had to download the add-in from the microsoft site, it's a great little wizard, and if you follow the instructions for Template Wizard with Data Tracking, it creates an excel database file with the same name as your template which adds the word database, the tricky things are:

Make sure (so you can find it) that both files are saved in the same directory, otherwise it will drive you crazy when you need to verify data has been recorded in the database. All headings need to be named whether its a row or column heading. The wizard will ask you for the name of the range and the cell that the data goes into. the database worksnook will contain a worksheet named Table1.

Now, my question is can you alter or change the database without ruining its integrity. You will note when you open the database file that the last column has something named WT_(something) and has what appears to be a record number?

take alook

http://www.dkriese.com/exceldatabase.jpg

I hope this info helps.