Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using MS Query to access an Excel file
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using MS Query to access an Excel file
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 EditGo 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems using MS Query to access an Excel file
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems importing from an Access query | Excel Discussion (Misc queries) | |||
Run Access query from Excel, input value to query being value in c | Excel Programming | |||
VB Code or Excel macro to run Query/Import on Access file | Excel Programming | |||
Link Access query to refresh an EXCEL file | New Users to Excel | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |