If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Struggling with MS Query...
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? |
#2
|
|||
|
|||
Struggling with MS Query...
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" wrote in message ... 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? |
#3
|
|||
|
|||
Struggling with MS Query...
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. 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???). 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. 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? 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 "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "Alex" wrote in message ... 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? |
#4
|
|||
|
|||
Struggling with MS Query...
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. -----Original Message----- 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. 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???). 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. 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? 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 "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "Alex" wrote in message ... 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? . |
#5
|
|||
|
|||
Struggling with MS Query...
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). -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com wrote in message ... 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. -----Original Message----- 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. 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???). 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. 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? 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 "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "Alex" wrote in message ... 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? . |
#6
|
|||
|
|||
Struggling with MS Query...
"DDM" wrote ...
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. am I to assume the MS Query Wizard will not work on dynamic tables? Alex, so far as I can see, MS Query won't take dynamic data ranges. 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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
query wizard error | Christen | General Discussion | 12 | August 13th, 2004 08:37 PM |
SELECT function in Query | alexparks | Running & Setting Up Queries | 9 | July 5th, 2004 11:31 AM |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |
Struggling with append query | KJ | Running & Setting Up Queries | 2 | June 2nd, 2004 05:15 PM |