Tag Archives: VBA

Office 2010: Excel 2010 – New Buttons on Ribbon for Pivot Tables – Custom Named Sets!

Just this week I blogged about adding Named Sets in VBA. Well lo and behold, in Excel 2010, there is a button “Fields, Items, & Sets” that lets you define your own Named Sets. Either with MDX or based on rows/columns you have on your pivot table

image

Works pretty slick! There goes the need for the custom VBA solution, which is fine by me. Although I am disappointed you still can’t create your own calculated measures. The OLAP Pivot Table Extensions add-in lets you, so I wonder why the built in functionality still doesn’t let you.

Excel 2007, OLAP Cubes: Customizable, User Defined Named Sets in Excel 2007 using VBA

In a perfect world, your master data and master data management (MDM) is set up so everyone can see things how they want. Categories of Items, Regions, etc, etc are all defined in your dimension data, and you can create hierarchies, etc that make sense and everyone is happy. But, this is not a perfect world :)

Some users want to see “their” items, “their” regions, etc. And they ask and ask for you to add it to the cube, but you have to deny them. Why? Because if you add their named set, you have to add all of them that are requested, that don’t make sense to 99% of the other people using the cube.

You probably could even go about doing some crazy MDX or something in your cube to create the named sets per user, but then you still have to manage it all in your MDX script in your SSAS project.

I blogged a while ago about the OLAP Pivot Table Extensions on CodePlex , which, is an awesome toolkit, but it is geared around Calculated Measures. I downloaded the code and took a gander here and there and it would be pretty easily modified to work with Named Sets (change xlCalculatedMember to xlCalculatedSet , add a radio button on adding a new calc, etc) – but, its in VS2005, and I have VS2008 (2005 BIDS, not C#), and right now I don’t feel like mucking around with that, maybe someone with more time, and more ambition can do it :) – Or maybe when I get some free time I will take a look, but for now here is a solution.

I took the idea outlined at the bottom half of this blog http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx  about named sets and creating them in macros..

Sub AddNamedSet()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")

End Sub

That macro is pretty sweet, you can add a named set to your workbook. But, its pretty static. You have to edit the macro every time you want to add more items, it doesn’t update or remove the sets you might have, etc.

What I whipped up quick was an updated macro that lets you build customizable named sets based on data in another worksheet in your Excel file (you can imaging it coming from other sources – another Excel file, a SQL table, etc, etc). I did this with a cube we use at work, but for this example, I changed it to pull off the AdventureWorks DW SSAS DB, AdventureWorks Cube, (SQL 2005 edition)

First off, here is the macro: 

Text File of Macro: Custom_NamedSets_Macro.txt</a

Excel 2007 Workbook with Macro: Custom_NamedSets.xlsm

Sub AddNamedSet()

    ' data sheet top row must be set name [Set Name] and then item numbers after
    Dim sourceData As Worksheet
    Set sourceData = Worksheets("Data")
    
    Dim strName As String
    ' get the name of the set, including brackets []
    strName = sourceData.Range("A1").Value
    
    ' used range is the whole column, rangeVals is the item numbers
    Dim usedRange As Range
    Dim rangeVals As Range
    Dim maxRow As Integer
    
    Set usedRange = sourceData.usedRange
    
    maxRow = usedRange.Rows.Count
    
    ' get the item numbers
    Set rangeVals = sourceData.Range("A2:A" & maxRow)
    
    Dim strFormula As String
    Dim i As Integer
    
    ' loop through and build forumla
    strFormula = "'{"
    
    For i = 1 To rangeVals.Count
       strFormula = strFormula & rangeVals(i, 1) & ","
    Next i
    
    ' remove last comma and add last curly and tick
    strFormula = Left(strFormula, Len(strFormula) - 1) & "}'"

    ' get pivot table object
    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)
    
    ' Add a calculated member titled "[MySet]"
    pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
    
    ' Add a set to the CubeField object.
    Dim cbf As CubeField
    Set cbf = pvt.CubeFields.AddSet(Name:=strName, Caption:=Replace(Replace(strName, "[", ""), "]", ""))


End Sub

Sub DeleteNamedSets()

    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)
    
    Dim i As Integer
    
    For i = 1 To pvt.CalculatedMembers.Count
        pvt.CalculatedMembers.Item(i).Delete
    Next i
    pvt.RefreshTable
    
End Sub

Now, this was a 15-20 minute VBA hack. It could really use some cleanup, but works. What I did to make it work with Advendture Works was just put the whole “member” string in the data tab of the spreadsheet. In my case at work, I just was using item number and had some of the member string in the macro. But really you can see you could add a named set on each column, or something, you really could make this powerful. And since I added the delete, if they re-run the add, it will recreate the set, so they can modify the data and re-add. It should be expanded on and made a little more robust, but you get the idea. Now, think of how you could use this with your cubes, and how you could get your user’s thinking about ways to use custom named sets!

VBA – Reading a Base64 Element into XML and using as Byte Array

Ok, more VBA

Getting a response back as Base64, but when trying to convert it from XML to binary data just having issues.. coming back as ASCII which converts wacked.

What you need to do:

Dim MyInfo As MSXML2.IXMLDOMNodeList
Set MyInfo = xmlDoc.getElementsByTagName(“MyBase64Element”)
MyInfo .Item(0).DataType = “bin.base64″

Dim image() As Byte
image = MyInfo .Item(0).nodeTypedValue

then you can use it in a byte array and convert to an image or whatever datatype you need.

The key here is overriding the type (probably Variant/String by default) to “bin.base64″ and then making sure to use the “nodeTypedValue”

This one threw me for a loop for a little while :)

MSXML2.IXMLDOMNodeList – Loading XML from files or strings

Again with the VBA, working with MSXML2.IXMLDOMNodeList objects. How do you load XML? Well MSDN shows you how to do it from an XML file..

Loading from an XML File:

Dim MyIXMLDOMNodeListVar As MSXML2.IXMLDOMNodeList
Dim xmlDoc As New MSXML2.DOMDocument30

xmlDoc.Load “c:myxml.xml”

If (xmlDoc.parseError.ErrorCode <> 0) Then
   Dim myErr
   Set myErr = xmlDoc.parseError
   MsgBox (“You have error ” & myErr.reason)
Else
   Set MyIXMLDOMNodeListVar = xmlDoc.getElementsByTagName(“MyElement”)
End If

Loading from XML string:

Dim MyIXMLDOMNodeListVar As MSXML2.IXMLDOMNodeList
Dim xmlDoc As New MSXML2.DOMDocument30
Dim myXml As String

myXml = “<MyElement>Steve Rules!</MyElement>”
xmlDoc.loadXML (myXml)

If (xmlDoc.parseError.ErrorCode <> 0) Then
   Dim myErr
   Set myErr = xmlDoc.parseError
   MsgBox (“You have error ” & myErr.reason)
Else
   Set MyIXMLDOMNodeListVar = xmlDoc.getElementsByTagName(“MyElement”)
End If

You will notice there isn’t much difference, except when you want to load from a string, you use xmlDoc.loadXml instead of just xmlDoc.Load .. I wish I could have found that in the documentation somewhere (maybe it’s there and I just didn’t look hard enough) :)

VBA vs VB.NET – turn bytes into bitmaps

So, recently working on some things, I have noticed the HUGE difference between VBA and VB.NET, specifically with turning bytes into bitmaps.. (assume GiveMeBytes() returns a byte array that is a bitmap)

VB.NET:

Dim image As Byte() = GiveMeBytes()
Dim memStream As MemoryStream = New MemoryStream(image)
Dim bitImage As Bitmap = New Bitmap(System.Drawing.Image.FromStream(memStream))

bitImage.Save(“C:test.bmp”)

 

VBA:

Dim image() As Byte
image = GiveMeBytes()

Dim bitImage

bitImage = FreeFile
Open “c:test.bmp” For Binary Access Write As bitImage
Put #bitImage, , image()
Close #bitImage

Now, maybe there is an easier way in VBA/VB6 to do it, but this is the way I learned way back in the day. I am sure you can do something with the FileSystemObject (I am guessing)..

You notice that the VB.NET snippet mostly deals with converting bytes to MemoryStream to Bitmap, and the saving is 1 line, whereas the VBA is really nothing with converting but mostly deals with saving the file. Can we get the best of both worlds? I am not sure, but I still like the .NET implementation better, it just seems “cleaner” to me, and the VBA just seems “dirty”, but they both do the same thing.. (notice in the VBA I didn’t strongly type the bitImage Dim)

In conclusion…”viva .NET!” :)