Solved

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

Posted on 2010-08-22
8
2,112 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now