Archive for June, 2011

Using MDX for ranking in a PowerPivot solution

June 29th, 2011 admin No comments


I found this great article about using a combination of MDX and DAX in a pivottable based on a PowerPivot model. It explains a real world Ranking problem using this approach. It is definitely worth reading…

PowerPivot is ‘Rank’


64 bit Windows with 32 bit Qlikview conflict

June 28th, 2011 admin No comments


If one has installed a 64 bit Windows OS and he tries to use the 32 bit Qlikview 9 application
He can encounter the following error message:

SQL Error:[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

This means you can’t use the 64 bit driver with the 32 bit application.

The Solution:

One should run odbcad32.exe from the SysWOW64 system folder and create an ODBC (32 bit) link.
This ODBC link can have the same name as a 64 bit one and can only be accessed / edited / viewed via this executable.


Categories: ODBC, Qlikview Tags: ,

Using a computer with multiple versions of office installed in combination with Qlikview.

June 27th, 2011 admin No comments

We were experiencing problems with a qlikview report containing a script which mailed parts of a certain tab as embeded pictures.

The line of code qlikview errored on was: Set OutApp = CreateObject(“Outlook.Application”)

The error was: ActiveX component can’t create object: ‘Outlook.Application’

The user with this problem also had problems with sending a excel workbook as an attachment from excel and got the error message:

This led me to believe that the additional installation of some Microsoft Office 2003 products were the reason for this strange behavior. In Qlikview you don’t reference a version of the ActiveX component like in vba so I assume the latest installation is leading.

To prevent these kinds of errors one should do a repair of the office installation considered to be leading.

Categories: Excel, Qlikview Tags: ,

Entire London 2012 Olympics’ cultural events database held on Excel

June 8th, 2011 admin No comments

I have read this great article on the site of the Register written by Kelly Fiveash about a job vacancy currently advertised on the London Organising Committee of the Olympic Games (LOCOG) website.

If you’re in for a laugh you have to read this…


Categories: Excel Tags:

Bottom left part of the UsedRange

June 6th, 2011 admin No comments

The csv file I import in my previous post contains two rows of informational data followed by a blank row followed by the real data. I needed to determine this range to loop though.
I decided to make a function and this is what I cam up with.

Feel free to shoot at it….


Function DataRange(tmpWorkSheet As Worksheet) As Range

    Dim rTemp As Range

    Set rTemp = tmpWorkSheet.UsedRange.Columns(1)
    Set rTemp = rTemp.Cells(rTemp.Rows.Count, 1)
    Set rTemp = rTemp.CurrentRegion

    Set DataRange = rTemp

End Function
Categories: CSV, Excel, VBA Tags: , ,