AET Excel Query v2

I've just uploaded a new version of the AET Excel Query add-in based on some new code provided by friend and mentor, Masaru Kaji (aka Colo) of Cell Master fame.

If you have previously downloaded the former version, or just want to try it for the first time, you can download the new version here.

This will likely be my last post of the year. In addition to taking the rest of the year off, I'll be taking a trip in early January so I probably won't post anything for a month or so.

Until then, I'd like to wish you a merry Christmas if you celebrate it. And most definitely, a happy new year in 2015.

See you next time.

Excel 2013 and the Ribbon

I've recently started using Excel 2013, and soon noticed the Ribbon doesn't act the same way as it did with Excel 2007 and Excel 2010. Addins that add menu items to "what used to be" (still is) the Top Menu (Menu Commands on the Add-Ins tab) and toolbars (Custom Toolbars also on the Add-Ins tab) are affected.

After a quick look on the Internet, I found this.

Well now, that changes things a bit...

Actually the recommended method of looping through each Window and deleting controls and toolbars does not always work. As insurance, I recommend you add them as Temporary where applicable. As far as my testing shows, this works without fail. (And it's good practice too).

Anyway, I was talking to my friend Ken Puls (of Excelguru fame) at the time, and he suggested using the Ribbon as it should be - with XML. And Ken should know, being one of the authors of RibbonX - Customizing the Office 2007 Ribbon.

I gave this some thought and decided that's just what I'd do. I've used some very basic XML for my main tools, JMT Excel Utilties back in 2007, which has it own tab, but where was I going to put everything else. Why not in the same place?

Ken kindly provided some XML to add items from different files on the same tab and I went from there. This is where I'm at now. It's been a busy two weeks, not just writing XML, but rewriting a lot of VBA.



If you have already downloaded any of the above addins and use them with Excel 2007 - Excel 2013, you can replace them from the links below. I recommend you do so as I have made some major changes to some of their code. (If any of the menu items or toolbars still show on the Add-Ins tab from the old versions in Excel 2013, you can simply right-click them and delete them)

AET Chart Tools

AET Data Checker

AET Excel Query

AET Find and Replace

AET Status Bar

Calculation Checker

Cell Highlighter (not pictured)

Cell Watch Form

Classic Excel Colors

Marlett Checkboxes

VBA Code Indenter
Also, not shown in the picture, but I have made big improvements to the code. An xlam alternative version has been introduced in case you have problems with the regular VBE version.

Download it here.

JMT Excel Utilities?
Hang on! In the above picture, I didn't see JMT Excel Utilities!

No you didn't. But I can show you this. (Look to the left)



They're making a comeback and will be better than before. They won't be free, but well worth what I'll be asking. Stay tuned.

See you next time.

The Cell Masters

In 2004 I met someone who would change my life and give me inspiration to start blogging.

That person was my friend and mentor, Masaru Kaji, more commonly known in Excel circles as Colo, of Colo's Junk Room fame.



I'm very happy to say he's back with a new site, named after his famous interviews with Excel legends, known as "The Cell Masters". I should mention that Colo is very much a Cell Master himself. In that respect, I would like to award him this.



Please welcome him back. You can find his new site here.

Clone Data Labels and Conditionally Show Data Labels

Excel 2013 has a feature which allows you to clone data labels. It's pretty handy but I decided to make my own version.

Here's a 100% Stacked Bar Chart with default data labels.



They're not easy to see, and the number format could be better shown as a percentage. So I have formatted a single data label like this.



Now I select AET Chart Tools on the AddIns tab, then Clone Selected Data Label.



My version works with the active series, active chart, all charts on the active sheet, and also the active workbook. Cloned format includes fill colour, font colour, font name, font size, bold/italic font and number formatting. It also works with Excel versions 2007, 2010 and 2013.

Active Chart will do in this case.



Here's the cloned data labels. Please forgive my yucky colour choices.



Moving right along, I'm only interested in values bigger or equal to 50%. So this time I select Conditionally Show Data Labels.



Same selection choices are available. This tools works with Excel versions 2003, 2007, 2010 and 2013. Possibly earlier versions too but I don't have them to test with.



And we're done.



Download my updated AET Chart Tools here.

See you next time.

SQL Queries Within Excel Using ADO

Recently I've taken some minor steps into the wide world of databases to give Excel a helping hand. I'll be honest and say I'm very much a beginner so don't expect much. I'm still learning the ins and outs of basic SQL.

Anyway, here's a little something I made on the weekend that allows you to perform simple queries within Excel itself. And it also allows to save your SQL queries so you can use them again later. (Forget indenting, go online or try your luck waiting for a later version!)



Assuming your data is in tables, you can select columns from sheets in your workbook, to insert the sheet (table) name and column (field) name directly into your SQL.

To be honest, it's half useful (for me at least, I can use it at work), and half experimental (as I'm still taking baby steps, I'm seeing what I can actually do with it).

Here's the download link.

See you next time. (I may or may not be be busy making Japanese versions of my add-ins for a week or two)