Archive

Archive for September, 2011

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
        .Refresh

    End With

End Sub

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

regards

Jelle-jeroen

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:

Shrinking Excel 2010/2007 Files by about 25%

September 9th, 2011 admin No comments

We know the compression technology Excel uses to create and save the files is not optimum.
So after reading a post on BaconBits about it, I thought I would write a macro who creates (small) copies of selected excel files.
The files I produced were all around 25% smaller than the originals.
Worth the effort I would say….

Bare in mind if you save the created files they will grow again….

you can download a sample workbook here Shrink Excel Files

Sub ShrinkExcelFiles()

    Dim Fname
    Dim i As Long
    Dim sFileFolder As String

    Fname = Application.GetOpenFilename(filefilter:="Excel (*.xls*), *.xls*", _
            MultiSelect:=True, Title:="Select the Excel files you want to shrink")  

    If IsArray(Fname) = False Then
    Else

       For i = LBound(Fname) To UBound(Fname)

           sUnzipFolder = Left(Fname(i), InStrRev(Fname(i), "\")) & "unzip\"
           ShrinkXlsX Fname(i), sUnzipFolder

       Next i

    End If

End Sub
Sub ShrinkXlsX(sFileName, sTempFolder)

    Dim objApp As Object
    Dim vFileName As Variant
    Dim sFileExtension As String
    Dim i As Long

    sFileExtension = Right(sFileName, Len(sFileName) - InStrRev(sFileName, "."))

    Name sFileName As sFileName & ".zip"

    CreateFolder sTempFolder

    Set oApp = CreateObject("Shell.Application")

    For Each ItemInZip In oApp.Namespace(sFileName & ".zip").items

        oApp.Namespace(sTempFolder).CopyHere (ItemInZip)

    Next

    Open sFileName & "_Small.zip" For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1

    i = 1

    For Each ItemInFolder In oApp.Namespace(sTempFolder).items

        oApp.Namespace(sFileName & "_Small.zip").CopyHere (ItemInFolder)

        Do Until oApp.Namespace(sFileName & "_Small.zip").items.Count = i

            Application.Wait (Now + TimeValue("0:00:01"))

        Loop

        i = i + 1

    Next

    Name sFileName & "_Small.zip" As sFileName & "_Small." & sFileExtension
    Name sFileName & ".zip" As sFileName

    DeleteFolder sTempFolder

End Sub
Sub DeleteFolder(MyPath)

    Dim FSO As Object

    Set FSO = CreateObject("scripting.filesystemobject")

    If Right(MyPath, 1) = "\" Then MyPath = Left(MyPath, Len(MyPath) - 1)

    If FSO.FolderExists(MyPath) = False Then Exit Sub

    FSO.DeleteFolder MyPath

End Sub

Sub CreateFolder(MyPath)

    Dim FSO As Object

    Set FSO = CreateObject("scripting.filesystemobject")

    If Right(MyPath, 1) = "\" Then MyPath = Left(MyPath, Len(MyPath) - 1)

    If FSO.FolderExists(MyPath) = True Then DeleteFolder MyPath

    FSO.CreateFolder MyPath

End Sub
Categories: Excel, VBA Tags: ,

Average and Standard Deviation without outliers in Excel

September 2nd, 2011 admin No comments

Sometimes you need the average and standard deviation of a dataset without a certain amount of outliers.

For example you want the average or standard deviation without the top 2.5% datapoints

{=AVERAGE(IF((DataRng<LARGE(DataRng,ROUNDUP((COUNT(DataRng)*0.025),0))),DataRng))}

{=STDEV.P(IF((DataRng<LARGE(DataRng,ROUNDUP((COUNT(DataRng)*0.025),0))),DataRng))}

If you want the average without the top 10 and bottom 10 datapoints this would be your solution

{=AVERAGE(IF((DataRng<LARGE(DataRng,10))*(DataRng>SMALL(DataRng,10)),DataRng))}

happy averaging or standard deviating

Categories: Excel, Formulas Tags: ,