QueryCell

Posted on by Dick Kusleika

QueryCell is an Excel add-in that let’s you use SQL statements against tables in a workbook. I downloaded the trial and tested it out. Here’s what I found.

Email on Download QueryCell requests your email when you download the trial. Sam Howley is the owner of the company and a regular at the Joel on Software discussion boards, so I know that he has considered this. But I wondered what you thought about it. Would you give your email to download a trial? I think Sam has a compelling statement on his download page

Please provide your email address and in two days time I will send you a single email asking you what you thought.

If someone goes through the trouble of convincing me there will be one email, I’m inclined to believe it. You can download QueryCell without giving an email, although it wasn’t not totally obvious to me that that was possible. So, would you give your email to download a trial if it wasn’t required?

Download 8.3MB download. That’s big, I think, for an Excel add-in.

Install It installed without a hitch.

Menus In 2007, it has it’s own Ribbon tab. In 2003, it creates a one-button toolbar that shows/hides the app. I’m very protective of my screen real estate and I would prefer a menu item, or at least a choice. The other problem I have is that no matter where I move that toolbar, it gets put on its own row when I restart Excel. One button on one row doesn’t work for me.

Trial The trial period is 30 queries. We just talked about trial periods. I like trials that are limited in the number of uses rather than the number of days. That allows me to try it at my leisure. I’m not sure if 30 queries is enough, though. I experienced some anxiety after I had run three. I thought “Oh sh*t, I’m going to be out of queries so I better be careful.” If this was my software, I would make the trail 60 queries or 60 days, whichever is longer. What can I say, I’m a trial liberal.

Trying it out

I started with a pretty easy SELECT statement. I put double quotes around the S% and it threw an error, but didn’t charge me for a trial. It was a nice error too. It told me what the problem was and how to fix it.

Error
Tip : use single quotes for literal strings in SQL not double quotes eg. WHERE CODE=’PAID’ not WHERE CODE=”PAID”

Once I fixed my syntax, it put the results on a new sheet.

Next I ran the same query except I checked the “Mark Results” box. This selects the records in place rather than generating a new recordset. This is a very nice feature.

Finally, I wanted to JOIN a couple of tables. As I was typing my SQL statement, I noted that there was a field list on the right side and it was selecting the fields as I typed so that I didn’t have to finish typing them. I almost never type table names in the SELECT part of the query but it was easier to do it in QueryCell than not. Having one letter table names helped, but the autocomplete was nice.

I won’t bore you with the results of the JOIN query, but it worked flawlessly.

Tutorial As I was playing, I thought Sam should offer a ready-made workbook with some examples. And there, under the help menu, was just such a workbook. It’s a great tutorial and it would have served me well to spend a few minutes with it before I started playing on my own.

Other I mentioned the 2003 toolbar on it’s own row problem above. I noted a few other things.

When I was typing my SQL statement and using the autocomplete, I kept pressing tab but it wants me to press enter. I don’t know why tab seems more natural to me, but it does.

There are tabs along the bottom of the main area, but it’s not totally obvious that they are there. They only have borders if you’re hovering over them. I figured out they were there when I got that error message. I thought I had lost my query, but it was on another tab and I was able to click back to it. I think I’d have to work with QueryCell a little more to see if I like the tab interface.

Don’t try to enable the Analysis Toolpak add-in while the QueryCell window is visible. It crashed my Excel every time. If I hide the window, no crash. I’ll be interested if anyone else has that problem or if it was just me.

When I was ranting about screen real estate earlier, it may have seemed strange that I didn’t mention how much screen QueryCell takes up. I should be screaming myself stupid about it, but it didn’t really bother me. I’d seen the screen shots so it wasn’t a surprise. And for a tool like this, I guess I felt like it was appropriate. This isn’t something you would have visible all the time, just when you’re using it. Having said that, I have two monitors, so why not let me use them. Let me put QueryCell on one and Excel on the other.

All of the windows withing QueryCell are resizeable. It’s really a polished, well-done app.

The cursor doesn’t change when hovering over a hyperlink, but I stand by my polished statement above.

Conclusion I’m very impressed with this add-in. It costs $40, which seems reasonable to me. My litmus test for trying a program is if I start thinking of ways I could use this in my normal work. If you have legacy monster Excel workbooks like me, this add-in would be money well spent (other than it would delay you getting that data out of Excel and into a proper database).

Free Who looks out for you like DDoE? Nobody, that’s who. Sam has graciously given me a license to give away. If you download the trial and like it, send me an email with QueryCell as the subject before the end of the day October 19th. I’ll pick an email at random and send you a full license key for your free copy of QueryCell.

Posted in Uncategorized

19 thoughts on “QueryCell”

  1. I’ve been querying Excel workbooks using ADO for ages. It’s one line of code different from using ADO to query a database on a SQL server. What advantage am I missing here?

  2. Thanks for taking the time to review QueryCell Dick.

    I will be looking in to the issues you raised.

    >money well spent (other than it would delay you getting that data out of Excel and into a proper database).

    I have had users comment that QueryCell is great for ‘training-up’ excel users, getting them used to using SQL so
    that they would be less resistant to eventually moving completely on to a database.

    Cheers
    Sam Howley

  3. I agree with zach, I failed to get an impression of the usefulness of this tool. What is it’s objective? Is it just an SQL builder? If so, I think I would soon get very tired of using an addin to build code that is usually (when querying Excel tables) relatively simple? Does it handled unnamed ranges? Does it handle sheets? Can you filter the recordset before dumping? Does it writeback? In other words, what do I get for my $40.

  4. Hello all,

    Zach and Bob are quite right, but to Zach I would reply that not all people know VBA and ADO, but SQL most often so it could be a simple alternative. Hum, Bob maybe the website can give you some answer to your questions ;)

    Dick, did you tried a similar add-in called SQL Drill (former SQL Excel) at http://www.sqldrill.com/ which nevertheless seemed less impressive.

    What’s interest me in that type of products is the fact that after it helps me to create SQL statement I then can leave embeded in the workbook the queries and have the ability to rerun them w/o the addin.

    Regards
    Thierry

  5. I’m not sure I would fork out $40 to do something I could achieve just as easily by dumping the data into an Access table, or use MS Query, or VBA/ADO… It looks good but there are already so many ways of achieving the same without dipping into the wallet.

  7. I agree with Jon…I don’t see that this does anything different than Microsoft Query. Sam, can you comment on what your tool does differently from MS Query?

  9. Sam

    I didn’t mean to be too critical. I suppose $40 is little in way of commission considering how many hours I imagine you must have spent developing this. And the interface appears very friendly indeed and the performance pretty darn quick. I suppose my point is that it isn’t worth $40 to me. But then there are plenty of users out there who don’t know ADO, MS Query or Access and this could very well be useful to them and a great introduction to a database approach of managing data in Excel.

    Regards
    Jon

  10. Hi All,

    Currently QueryCell:

    – Is very similar in appearance and operation to a standard SQL text client you might use to connect to a database
    – Automatically scans all open worksheet, assigning table names to all contiguous regions of data. You can have multiple regions (tables) on the same worksheet)
    – provides a SQL Library where you can drag your SQL queries to save them for future use
    – Allows you to define custom regions, sub-tables within larger tables
    – Has a simple test data generator, allowing you to generate test/dummy data for development/prototyping
    – Has a SQL Insert generator, that simplifies the task of converting a region of Excel data into SQL Inserts. Obviously there are many ways to get
    SQL data into a database but sometimes it’s useful to have the actual Inserts saved
    – As was mentioned in the review, makes it easy to color rows that match a specified criteria

    I agree that everything you can do in QueryCell you can do in other ways. It’s aim is to make certain types of tasks easier.

    It’s a newish product, released in June 2009. I would love to hear suggestions as to how it can become more useful for people.

    Jan : The NavigatorXL Screenshots do look similar as they also default to naming tables A,B,C etc. In QueryCell this is the default but you can change the
    name of a region of data (table) to whatever you wish.

    Jon : Jon, thanks for your words. I think you are right in identifying that different people get different value from tools. There are certainly people getting value from QueryCell and It’s really useful for me to hear about how it can provide value to more people :)

    jam : SQLDrill looks to be a great tool. I haven’t used it extensively but it is very popular. It focuses, in my understanding, with connecting Excel up to external data sources and pulling their data into Excel. Where as QueryCell focuses on querying data already in Excel.

    Cheers
    Sam Howley

  12. IMHO has its meaning.
    Advanced users who are familiar with SQL sometimes miss the syntax to query large Excel files.
    Excel filtering features are not suited for large dimensions, let’s say more than few tenths of distinct values.
    It’s not a tool for the everyday user, though.

    My 2 cents

  17. Nice tool!!

    Allows the ability for people not familiar with SQL to get a taste of it! Also, for companies that utilize excel for business solutions but then need to change over to a new system, allows for evaluation of the data in order to identify data cleansing issues and possibly allow Business Analysts to identify how data should/would or could fit into the new system.

    Users could leverage this to make relationships with their data that they were not capable of doing before. I am guessing that most comments here are from those that work exclusively in the IT dept. If that is not the case then forgive the comment but this tool could be used to bridge some gaps between IT and the business world.

    Nice use of FireBird!! Keep it up!!

  19. I found a special task pane in QueryCell, it works like a standard task pane in Excel. How do I create it? What styles to set to handle of window?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.