Solved

Using Microsoft Query to access Table or Named Range in Excel 2007 macros workbook

Posted on 2010-08-22
8
2,173 Views
Last Modified: 2012-05-10
In 2003, I was able to setup a query to another spreadsheet using Microsoft Query.  Trying to do the same thing in Excel 2007 I have three problems/questions.
The sample spreadsheet (Testsource1.xslm) has a single table and a single range that is defined as the same area as the  table. Using a blank worksheet:
1. I start by using Data->From Other sources -> From Microsoft Query.  When I select the source spreadsheet using Microsoft Query I get an error message "ODBC Excel Driver Login Failed" - "External Table is not in the expected format".  If I have the source spreadsheet open when setting up the query, this doesn't happen?  This was not a constraint in 2003.  But I think it leads to other errors that occur later in this process.
2. When I did this under 2003, I got a wizard box "Choose Columns" which would list any named ranges in them to choose from.   In 2007 I don't see either the table or the named range in the available tables and columns box. If I select options and pick all four check boxes, then I see something related to each worksheet name.  I must have "system tables" and "tables" selected to see these.  
3.  If I select one of the tables related to Sheet1, I can setup the query.  But when I try to edit the query, I get another ODBC Login Failed, this time with "Unrecognized database format.   Seems to be related to number 1 above.
In summary I can't setup a query related to a named range or table without having the source file open.  I then can't seem to edit the query if I base it on the system table (sheet1 for example).  I expect that there is something that needs to be done with the way the source spreadsheet is setup.
 TestSource1.xlsm
0
Comment
Question by:StevenPMoffat
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33497007
The 2007 version should work the same as your experience in Excel 2003.  I even tried it with my 2007 in Compatibility mode and it worked fine.  I have included step-by-step screen shots that walk through the process with your example file without using the MS-Query Wizard.  Please reboot first and then give them a try and let me know exactly which step fails.

MS-Query-for-EE-26420771.pdf
0
 

Author Comment

by:StevenPMoffat
ID: 33497075
ProdOps:
I tried your steps.

1. Did you have to have the source file open in order to setup the query?  (See number 1 above)
2. I took your steps and they worked.    I can probably work with this although in Excel 2003 I had the option of selecting a Named Range to choose from.  
3. Once I set up the query, I tried editing it and got a similar error.  See attachment

See attached PDF for steps taken and error messages received. This shows number 1 above and number 3 above.
Using-Microsoft-Query-to-access-.pdf
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 250 total points
ID: 33497666
Steven,
No, I did not have the source file open when I created the query nor when I edited the query after it had been created.  
MS-Query does not recognize the new Excel 2007 Tables so that is why you are not seeing the Named Ranges like you did in Excel 2003.  If you define the range as
=Sheet1!$C$4:$U$11  instead of   =Table2[#All]  and you will see the Named Range in MS-Query.
Try defining the named range you want to work with the Row-Column syntax and then building the query.   It may not be the final solution but let's try to eliminate this problem and see what remains.
Jerry
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33499400
Jerry is quite correct. Neither tables nor dynamic named ranges (which is effectively what you are using) work with MSQuery.
0
 

Author Comment

by:StevenPMoffat
ID: 33502888
Jerry,  
Your first suggestion to change the definition of the Named Range works fine.  That problem is solved.  But I am still experiencing my open problems.  Here is what I have observed.
1. I cannot connect to a file the first time setting up the query. I get the ODBC login failure.
2. If I open the source file, then I can set up the query.  Note in this case, a read only version of the source file gets opened.
3. Once the query is defined, if I try to edit it, I get the database incorrect format error message and I am asked to pick the file again.  If I pick the same source file, then I can complete the edit and I can continue to edit in this way.
4. However, if I close the read only copy of hte source file, then I can't edit no matter how many times I try to select the source file again.  
Since you didn't have the same problem, I am wondering if there are some sort of Excel settings that are different on my machine as opposed to yours.  I need to test on a couple of other machines to see if the problem is similar.  Any other ideas?
Steve
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33503376
Steve,
I used your sample file from above when I ran my tests and was able to connect to your Sheet1$ without the file being open.  I was also able to edit the query multiple times without a failure.   Are you using this same sample file in your tests or are you using your production file and having the problem there?  On a rare occasion I have had a corrupt Excel file and once I copied the original source data to a new workbook I could connect to it without problems.  I am not saying this is the problem but start from a new workbook and create the source data and then from a second workbook, see if you can connect to the first one...  Just to eliminate the possibility of a corrupt source or receiving file.
I cannot think of any specific Excel settings that will affect this.  There could be an issue with the Excel ODBC driver on your PC but I really doubt that is the problem.  Please try testing with new workbooks on your PC and on several others and let us know what you find.  
Rory - If you are still monitoring this thread please let us know if you see anything I am missing so far.
Thanks,
Jerry
0
 

Author Comment

by:StevenPMoffat
ID: 33503983
I will try your suggestion.  I was using this actual file as it was my test file created from scratch.  It will take me a couple of days to do the testing, so standby.  I will start a fresh sample.  
Steve
0
 

Author Closing Comment

by:StevenPMoffat
ID: 33507010
I tried my problems on another machine and don't have the access problems, so we are good on this one.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question