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: , ,

Scheduled Import a online csv file into Workbook

May 27th, 2011 admin No comments

For a client I needed a solution for a scheduled import of a online csv file into a workbook. There are many ways to download files from the internet but it needed to be in excel as there were a whole lot of macro’s allready in place to transform and process the downloaded data. They had a machine running at all time ( not a server ) to be used.
So this is the code I came up with. It runs every day after it is started by running the StartSchedule Procedure

Option Explicit

Public tTime As Date

Sub StartSchedule()

    tTime = Now()

End Sub

Sub Schedule()

    tTime = tTime + TimeSerial(23, 59, 59)
    Application.OnTime tTime, "ImportCSV"

End Sub

Sub ImportCSV()

    Dim tmpSheet As Worksheet

    Set tmpSheet = ThisWorkbook.Sheets.Add(,,, "http://www.dummy.com/temp/file.csv")

    tmpSheet.Name = tmpSheet.Name & " " & Format(Now(), "yyyy-mm-dd hh-mm-ss")


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

New Plugin to display the sourcecode

May 19th, 2011 admin No comments

Just downloaded and installed the SyntaxHighlighter Evolved Plugin on my blog to display the sourcecode here in a more readable way.


Categories: Uncategorized Tags:

Importing varbinary fields into Excel

May 18th, 2011 admin No comments

I needed to compare two large datasets and find out if they contained the same records.
So I amended the records with a HashByte at the end so I could check if they existed in the other one.
First I imported both queries in Excel with the idea to do a lookup on them.
But a varbinary field is not imported in excel and it will return no error when ommitting a field,
except when the only field is a varbinary field then Excel willl tell you:

No columns that Microsoft Excel can use were returned from this query.

So I had to go back to the Management Studio and join the two sets which was a pain because they were on differend servers.
So much pain I kept trying to find a Excel solution.
All I had to do was convert the varbinary field by making it a parameter in a function which would return a normal varchar.


SELECT [field] FROM [table]

had tot change to

SELECT master.dbo.fn_varbintohexstr([field]) AS [field] FROM [table]



Categories: Excel, SQL Tags: ,

Finally up to date….!

May 18th, 2011 admin No comments

Finally I put back all my post in chronological order. I have a bucket full of ideas for post mainly on PowerPivot, Excel, VBA, SQL Server etc. etc. Probally they will also be posted on the excelusergroup.org and maybe on some forums because they are not always finalized ideas.

I’m aiming for some feedback and comments….

glad to be back


Categories: Uncategorized Tags:

Checking if a range is sorted with a formula

May 18th, 2011 admin No comments

January 7th, 2011

To test if range B2:B8 is sorted use {=AND(AND(B2:B7<=B3:B8)<>AND(B2:B7>=B3:B8))} in Excel
To enter a array formula ( and get the brackets ) you need to put the formula in without the brackets and press Control+Alt+Enter

thnx to Chandoo.org

Categories: Excel, Formulas Tags: ,

Slicers and Filters in Excel 2010 Pivottables

May 18th, 2011 admin No comments

May 28th, 2010

I’m not sure if what I experienced is a bug or a feature….
I created a pivottable in Excel 2010 with slicers and made a selection via the slicers.
So far so good but when I wanted to see the records for a specific datapoint and double click on this point it will give me the complete recordset not taking into account the slicer settings.
But when I put in pivottablefilters for the same fields as the slicers it all works like good old pivottables doubleclicking.

Does anybody know if this is a feature or a bug….?