Author Archive

Vizubi Excel

March 29th, 2012 admin No comments

Just came across a promising add-in for Excel.
Its called Vizubi Excel and is part of a larger Development / Data platform
You can use it to load data from QVD’s ( Qlikview data [blob] files )
The beauty is the Add-In for Excel is free … but only available in a 32 bit version

So no I need to set-up a 32 bit installation and go back in time to test this…

Does anybody have any experience with Vizubi please let me know…



Categories: Excel, Qlikview Tags: ,

Three Jokes

March 13th, 2012 admin No comments

A man is flying in a hot air balloon and realizes he is lost. He reduces height and spots a man down below.
He lowers the balloon further and shouts, Excuse me, can you tell me where I am?
The man below says: Yes. You’re in a hot air balloon, hovering 30 feet above this field.
You must work in Information Technology says the balloonist.
I do replies the man. How did you know?
Well says the balloonist, Everything you have told me is technically correct, but it’s no use to anyone.
The man below says, You must be a manager in business.
I do replies the balloonist, but how did you know?
Well, says the man, You don’t know where you are, or where you’re going, but you expect me to be able to help.
You’re in the same position you were before we met, but now it’s my fault.

A man was driving along a freeway when he noticed a chicken running along side his car.
He was amazed to see the chicken keeping up with him because he was doing 50 MPH.
He accelerated to 60 and the chicken stayed right next to him. He speeded up to 75 MPH and the chicken passed him up.
The man noticed the chicken had three legs. So, he followed to chicken down a road and ended up at a farm.
He got out of his car and saw that all the chickens had three legs.
He asked the farmer What’s up with these chickens?
The farmer said Well, everybody likes chicken legs. I bred a three legged bird. I’m going to be a millionaire.
The man asked him how they tasted.
The farmer said Don’t know, haven’t caught one yet.

A physicist, a theoretical economist and an econometrician are asked to find a black cat (who doesn’t really exist) in a closed room with the lights off:
The physicist gets crazy trying to find a black cat that doesn’t exist inside the darkened room and ends up in a psychiatric hospital.
The theoretical economist is unable to catch the black cat that doesn’t exist inside the darkened room,
but exits the room proudly proclaiming that he can construct a model to describe all his movements with extreme accuracy.
The econometrician walks securely into the darkened room, spend one hour looking for the black cat
that doesn’t exits and shouts from inside the room that he has it catched by the neck.

Categories: Uncategorized Tags:

Random results after PivotTable refresh.

March 8th, 2012 admin No comments

A funny thing happend to me lately…

A user (manager) came to me with a PivotTable his predecessor created that produced random results after refreshing.
The PivotTable was based on a PivotCache connected to a query on a SQL Server.
When the query was run in Management Studio it was complaining about a conversion and returned a error.
This query didn’t produce a error in excel but just returned the records upuntil the error.
As the query wasn’t sorted the number of records returned to excel was random as so the results in the PivotTable.

If after the first refresh the data was almost complete nobody would have noticed the corrupt results

Hope I this helps anybody having the same issue….

p.s. Self Service BI is not for everybody …

Categories: Excel, Pivot table, SQL Tags: , ,

Microsoft SQL Server 2012 Data Mining Add-ins for Office 2010

March 8th, 2012 admin No comments
Categories: Data Mining, Excel Tags:

Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010

March 7th, 2012 admin No comments
Categories: Excel, PowerPivot Tags:

XLNS was added to the Spreadsheet Page from John Walkenbach.

February 16th, 2012 admin No comments

Just found out that my blog was added to the Spreadsheet Page from John Walkenbach.

I could not resist making a screenshot with the 10 post from my Blog …

Categories: Uncategorized Tags:

Combine csv’s into one Workbook

February 2nd, 2012 admin No comments

Lately I was looking into the issue of combining all csv files in a folder into a single workbook.
One could do this in vba but it needed to be a scheduled overnight process.
As I recently discovered PowerShell I decided to give it a fair chance.

This is the script I came up with and to be fair it works like a charm.
Some minor tweaks need to be done but I couldn’t resist the post because it has been a while.

$files = get-childitem 'D:\Data\'

$datafiles = $files | where {$_.extension -eq ".csv"}

$Excel = New-Object -Com Excel.Application

$Excel.ScreenUpdating = $false
$Excel.DisplayAlerts = $false

$WorkBook = $Excel.Workbooks.Open('D:\Data\'+$datafiles[0].name)

$mv = [System.Reflection.Missing]::Value

for ($index = 1; $index -lt $datafiles.Count; $index++)
 $WorkSheet = $WorkBook.Sheets.Add($mv,$mv,$mv,'D:\Data\'+$datafiles[$index].name)

$Excel.Visible = $True
$Excel.DisplayAlerts = $True
$Excel.ScreenUpdating = $True
Categories: CSV, Excel, PowerShell Tags: , ,

Evaluate Math functions

October 14th, 2011 admin No comments

I got a question about math functions in excel from a guy

He needed to parse a function like (3^[x1])+([x2]^2)+[x1]+[x3]+3*[x4] into a function
And needed to parse the values for the variables via a range.

I thought I would spread the wealth and share my code with the world….


 'Set reference to: Microsoft Scripting Runtime

Public Function EVAL(sFormula As String, rVarValues As Range)

    Dim tmpDict As Dictionary
    Dim Cell As Range
    Dim i As Long

    Set tmpDict = VariableDictionary(sFormula, "[", "]")

    i = 0

    For Each Cell In rVarValues

        sFormula = Replace(sFormula, tmpDict.Items(i), Cell.Value): i = i + 1

    Next tmpCell

    Set tmpDict = Nothing

    EVAL = EVALUATE(sFormula)

End Function

Private Function VariableDictionary(sString, sStart, sEnd) As Dictionary

    Dim tmpDict As Dictionary
    Dim strTemp As String
    Dim i As Long

    strTemp = ""

    Set tmpDict = New Dictionary

    On Error Resume Next

    For i = 1 To Len(sString)

        If Mid(sString, i, 1) = sStart Then strTemp = ""

        strTemp = strTemp & Mid(sString, i, 1)

        If Mid(sString, i, 1) = sEnd Then tmpDict.Add strTemp, strTemp

    Next i

    On Error GoTo 0

    Set VariableDictionary = tmpDict

    Set tmpDict = Nothing

End Function
Categories: Excel, Formulas, Math, VBA Tags: , , ,

DAX QuerieTables in Excel via VBA

September 16th, 2011 admin No comments

I came upon the this great article DAX Table Queries in Excel from Gobán Saor.
This got me thinking about automating this via VBA.
The first try immediatly led to this macro

Sub Create_QT_Test()

    Dim qtTable As QueryTable
    Dim sConn As String
    Dim sMDX As String
    Dim ws As Worksheet

    sConn = "OLEDB;Provider=MSOLAP.5;" & _
            "Persist Security Info=True;" & _
            "Initial Catalog=Microsoft_SQLServer_AnalysisServices;" & _
            "Data Source=$Embedded$;" & _
            "MDX Compatibility=1;" & _
            "Safety Options=2;" & _
            "MDX Missing Member Mode=Error;" & _
            "Optimize Response=3;" & _
            "Cell Error Mode=TextValue"

    sMDX = "DRILLTHROUGH SELECT FROM [Model] WHERE ([Measures].[Sum of trades])"

    Set ws = ActiveSheet

    Set qtTable = ws.ListObjects.Add(3, sConn, Destination:=Range("B2")).QueryTable

    With qtTable

        .CommandText = sMDX
        .CommandType = xlCmdDefault

    End With

End Sub

I’m going to work on this futher and post better and more code…



Categories: Denali, Excel, MDX, PowerPivot, VBA Tags: , , ,

Windows 8 Developer Preview downloads

September 14th, 2011 admin No comments

For everybody who is interested a link

Categories: Uncategorized Tags: