12352

Microsoft sneaked a couple of ne As we should all know by now, PowerPivot provides Excel with a powerful way to harness data from one or more sources, and to do further analysis on that data within familiar pivot tables. Furthermore, because PowerPivot is creating an in-memory cube of the data, it is possible to build an analysis using CUBE formulae. I have blogged a couple of times about CUBE formulae, in Cycling Through The Fog and in Cracking The Code. In Excel, as with any development, you want your solution to be as flexible and dynamic as possible. This … Continue reading Cooking With CUBEs

Microsoft sneaked a couple of ne Introduction In my last blog post, I described how I thought that I could build an Excel 2007 ribbon on th fly. The technique was founded upon having a ‘worker’ addin that handled the main functionality as well as the version management and ribbon building; and a simpler Excel 2007 ribbon wrapper addin. This approach would be similar to the myriad of table driven menu solutions around, but the ‘table’ would be a configuration file in this case, to allow the user to control the menu/ribbon. The ‘worker’ addin would read the configuration file … Continue reading Ribbon On The Fly Part2

I recently needed to build an Excel addin that supported a variable number of options, with buttons to invoke the options. That in itself is not unusual, but I also wanted the numbers to be driven by the user; which always complicates matters. The definition of the buttons is simple, even allowing for the fact that it will be maintained by users. I could use a separate configuration workbook, an INI file, or even XML. As I am looking to make this a user driven facility, within an overall Excel framework, an Excel workbook seems the best option. For the … Continue reading Ribbon on the Fly, or the Zip

One of my primary interests at present is Business Intelligence (BI), using available tools to present meaningful information to the business that actually adds value. I won’t go into what BI is, the quality of BI products available at present, or the how businesses perceive or fails to perceive BI, that may be a topic for another day, but rather in developing my skills and capabilities in this area. Excel is a wonderful medium for presenting this information, and the majority users that I know would rather use Excel than other tools. Most people who are familiar with BI will … Continue reading Does Excel Do MDX?

I have been playing with PowerPivot recently, and one of the sites I have visited is Rob Collie’s PowerPivotPro blog, http://powerpivotpro.com/. One particular post that caught my eye was a guest post by Colin Banfield, on building complex DAX formulas in NotePad++. As Colin noted, the PowerPivot formula editor is severely limited, a better option is required when working with more complex DAX formulae. As Colin suggested, NotePad++ can provide this better option; with code folding, keyword highlighting, and Intellisense. In his post, Colin showed how to extend NotePad++ to provide this functionality for DAX formulas. Being primarily an Excel … Continue reading Formulas Made Easy

One of the better additions to Excel 2007 are, in my view, the functionality added to tables http://www.jkp-ads.com/articles/Excel2007tables.asp. I was recently working on a 2007 project and I was looking to add some highlighting using conditional formatting. That will be easy thought I, but as usual, I was way off the mark. Formatting The Table For the sake of clarity, I will not use the actual formatting that I was trying, but a far simpler case. Figure 1 shows an example of a typical table in Excel 2007. Figure 1 Let’s assume that I want to show those rows where … Continue reading Who Scratched My Table?

In a previous post, I talked about building stored procedures (SPs)in an Access database, and calling the same from Excel using ADO. As I mentioned in that post, I am not a fan of the Access GUI. Whilst GUIs can be okay for doing some simple testing, checking whether something works or, I find it far easier to build a script when I need to do similar things over and over (such as building all of the SPs for an application). I am an inveterate scripter (see Autogen’ed Ribbon Code and XML Is Such A Pain); rather than build the … Continue reading The Kid Grows Up

  I have written before about hand-crafting XML so as to customise an Office 2007 ribbon at XML Is Such A Pain and Autogen’ed Ribbon Code   As I have mentioned before, the CustomUI editor is far from perfect. It does allow adding of  image files and to validate the XML, but the editing experience is absolutely awful.   My favourite editor is TextPad, and one of the useful features of TextPad is the facility to incorporate syntax files that are file type dependent, for instance a Visual Basic syntax file for .bas and .vbs file types. The syntax files … Continue reading You Say CustomUI Editor, I Say TextPad