Monday, October 13, 2008

Working with Margins in WPF

Hi all,

As we know, that WPF has come up with a new Concept of XAML, the thing which irritated me most while I as learning it, is working with Margins. I, being an average guy, certainly took pretty long to understand how Margin Works.

     When I start learning WPF, I thought that previous version(Win Forms) is pretty easy and effective as compared to new WPF. But more familiar I got with WPF, I found it more and more effective.

Q. What are Margins?
A. Margins are mere numbers, which decide how far your control will be placed from the boundaries of the parent control/container.



< Grid >
      < Image Height="40" Margin="0,1,0,0" Name="imgTop" Opacity="1" Stretch="Fill" Style="{DynamicResource formHeaderImageStyle}" VerticalAlignment="Top" />

   < / Grid >

Now here, in the above line, it says vertical alignment = Top, that means, the margin will be considered from the top only. Margin 0,1,0,0 means 1 point away from top. Suppose if you want to increase and decrease the height of the control propotionate to the parent container then you can use following code.

< Grid >
      < Image Height="40" Margin="0,10,0,10" Name="imgTop" Opacity="1" Stretch="Fill" Style="{DynamicResource formHeaderImageStyle}" VerticalAlignment="Strech" />

   < / Grid >

The above code says Vertical alignment = strech. It means that the image control will be streched according to the margin set and the actual height of the container. The image control will be ten-ten points away from the top and the bottom of the container(Grid in the current case). If the Grid's height is increased, the image's height will be increased to ensure that the bottom is only 10 points aways from the Grid's lower bottom.


Monday, August 4, 2008

Office 2007 Format


Microsoft has brought a revolutionary change in office Application through Office 2007 Launch. The biggest change, for me is its XML Format. Now, Office document is not a mere document anymore. The new office documents behave like a Container too, that means, you can store any data in an office document. For example, you can store any bussiness information in an excel file which will not be visible to users at all.

I will take an example of a simple excel file, say myExcel.xlsx. If you add a zip extension(convert the name to and open it with compressed zip folder, you can see that the excel file is a container(a folder) actually. Because, the data in office 2007 is stored in a container in the form of different files. You will see a file named "[Content_Types].xml" and along with few folders(normally three folders). These three folders are the containers where in all the data of the file store in the form of different XML Files. Whatever you will store in the excel file, it will appear in the XML file, one way or another.

One very good example of how the document is structured or used, is here:

If I include a chart in a sheet, it will create few more xml files. For ex, \xl\charts\chart1.xml. This XML will contain all the data for chart and the formatting of the chart too. It will contain, that how many series does the chart have, what is the range of the chart, what type of chart is it etc.

Apart of the above features, you can also store your own XML Data in office document. The Office 2007 XML file format accommodates custom business data, and allows for programmatic access to it when files are loaded. So the data in Custom XML will not be directly visible to the user, but you can load it on the runtime and use it programmatically. A very good link from Brian Jones is

You can add the CustomXML while using the CustomXMLParts Object (given in the Office Object Model). Here is the link of MSDN

Dim cParts As CustomXMLParts
Set cParts = ThisWorkbook.CustomXMLParts

Create an xmlFile and save it on desktop. It can contain any information related to your bussiness requirements:

< ?xml version="1.0" ? >
< BhandariInfotech >
< ChartSheet Id="fChart" >Chart Collection< /ChartSheet >
< DataSheet Id="fChart" >Data Collection< /DataSheet >
< ChartSheet Id="sChart" >Charts< /ChartSheet >
< DataSheet Id="sChart" >Datas< /DataSheet >
< /BhandariInfotech >

cParts.Add "C:\A.xml"


The above data will include the above XML Tags in an XML File and store it in the excel file itself. This data will not be visible to user, but you can use it programmatically to access the data. You can also add your critical Business information, which you have to use in the document, but you don't want the users to see that information. These information can be your revenue figures, your Balance Sheet info...etc.

The XML Format is very modular and clean too. Every single thing in the Office Documents, is stored in different XML Files. Be it your Chart Data, be it your sheet data, be it your Name ranges...everything is stored in different XML files. These all files can be accessed programmatically to extend the functionalities of office applications. The idea of keeping all the files separate is to make sure that a user should be able to access a specific type of data, without touching the other files in the container. Earlier, suppose if my one sheet is corrupted, then the other sheets in the workbook will not be effected because the data is kept in separate XML files for different sheets. If I have 200+ sheets in my workbook, and I want to access only one sheet frequently, then opening a workbook with 200+ sheets can be a cumbersum task which will involve long time in opening and closing the workbook again and again. The other option is, access the Office Document Programmatically and copy only that sheet which you require in a new workbook and open that new workbook.


Monday, June 30, 2008

Getting the object of Running office Application

If you want to use the running excel object. You can use the following function:

private void getExcelApp()
objXlApp = (excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
objXlApp = new excel.Application();

Friday, June 20, 2008

Setting RGB Colors for Excel 2007 Chart Series

Sub ColorChartSeriesWithRGB()
Dim chrt As Chart
Dim iSeries As Series
Dim colors(4, 3) As Integer
Dim i As Integer
Set chrt = ActiveChart
colors(1, 1) = 100
colors(1, 2) = 200
colors(1, 3) = 250
colors(2, 1) = 200
colors(2, 2) = 256
colors(2, 3) = 50
colors(3, 1) = 60
colors(3, 2) = 180
colors(3, 3) = 126
colors(4, 1) = 111
colors(4, 2) = 123
colors(4, 3) = 205
i = 1
For Each iSeries In chrt.SeriesCollection
iSeries.Format.Fill.ForeColor.RGB = RGB(colors(i, 1), colors(i, 2), colors(i, 3))
i = i + 1
Next iSeries
End Sub

Friday, June 13, 2008

Gallery Items in Ribbon

You can include the following code to implement the Gallery in your Ribbon.

<customUI xmlns="" loadImage="LoadImage" >
<ribbon startFromScratch="false">
<tab id="tab1" label="Gallery Demo" keytip="x" >
<group id="group1" label="Demo Group">
<gallery id="gallery1"
supertip="This is the super tip."
onAction="galleryOnAction" >
<item id="item1" />
<item id="item2" />
<item id="item3" />
<item id="item4" />
<button id="button1" getLabel="GetLabel"
imageMso="HappyFace" />


Wednesday, June 11, 2008

Reading Tags in Powerpoint

Sub ShowTags()
' Show me the tag name and value for each tag on each shape
' that has a tag on the currently displayed slide

Dim x As Long
Dim oSh As Shape

For Each oSh In ActiveWindow.View.Slide.Shapes
If oSh.Tags.Count > 0 Then
With oSh.Tags
For x = 1 To .Count
MsgBox oShName & vbtab & .Name(x) & vbTab & .Value(x)

Next ' x
End With
End If
Next ' oSh

End Sub

Sub AddTag()
' Adds the tag "TAGNAME" with value "TAGVALUE"
' to the currently selected shape

With ActiveWindow.Selection.ShapeRange(1)
.Tags.Add "TagName", "TagValue"
End With

End Sub

Sub ShowTagValue()
' Displays the current value of tag "TagName" on the selected shape

With ActiveWindow.Selection.ShapeRange(1)
MsgBox .Tags("TagName")
End With
End Sub


Tuesday, June 3, 2008

Optional Parameters in


Optional parameters are not supported in C#, so we have to pass a value to every parameter while calling a method. But incase, if we don't want to pass any value to the parameter, we can use the global variable defined as Type.Missing.

Check the following link of MSDN Site:


How Add-ins Work with the 2007 Microsoft Office System

A very good explanation of Add-in architecture in the following library:


Sunday, June 1, 2008

IDTExtensilibity2 Procedure to Add Menus

Hi all,

I would describe the procedures/steps that is followed when you try to add menu through IDTExtensilibity2 Interface.

Whenever office application is opened, it checks whether it can find any DLL registered for the application or not. If it finds any DLL registered, that DLL is loaded in the memory. The DLL is a kind of ADD-In, which is loaded automatically. This process of loading the DLL File, triggers five types of events.
  • On connection
  • OnStartupComplete
  • OnDisconnection
  • OnBeginShutdown
  • OnAddInsUpdate

  • To capture these events, your DLL File must implement IDTExtensibility2 Interface. This interface contains the definition of all these events. One can write the code for adding menus in onstartupcomplete method. It will force the application to add Menus after it loads the Add In.

    Hope I was able to clear the process.


    Wednesday, May 21, 2008

    ADO Connection Pooling Fundamentals

    If you require to open multiple connections several times in your application, it is not a good practice to open the connection every time it's needed because it involves overhead. For overcoming this problem, connection pooling is used. Whenver you close the connection, you mean to destroy the connection object because your work is already done. But, @ the backend, OLEDB preserves it's state in a pool. So whenver you will need the connection again, ADO will not have to do the expensive stuff again to make a connection to the source. Rather, it will pick the connection from the pool and will return the connection object.

    Please note that the connection pooling can only be achieved when the connection information is same. If you are opening a connection with a new ID/password, ADO will open a new connection to avoid security issues.


    Friday, April 4, 2008

    Some ADO Fundamentals


    We all are using ADO quite frequently, but we are not sure if we are using it efficietnly too or not. I have given some information below, which may help you out in managing your queries.
    While getting a recordset from a connection object.
    Set objRS = objConn.Execute(CommandText, [RecordsAffected],[Your Options])
    You can speed up your records fetching process by mentioning the options in the recordset object. Following are the options which are available:

    adCmdText If you have given the Command Text as a SQL string.
    adCmdTable It is a sign that query would return the column names of the table.
    adCmdTableDirectfor a table name, whose columns are all returned.
    adCmdStoredProc for a stored procedure name.
    adCmdFile for a saved recordset.
    adCmdUnknown for an unknown command type.
    adCmdUnspecified to indicate the command type is unspecified. ADO will work out the command type itself, but this will lead to poorer performance, so you should always explicitly set the command type.

    You can also add the following ExecuteOptionEnum modifiers to Options:

    adAsyncExecute, for asynchronous execution.
    adAsyncFetch, for asynchronous fetching.
    adAsyncFetchNonBlocking, for asynchronous fetching that does not block.
    adExecuteNoRecords, for a non-row returning command.

    Vikas Bhandari

    Creating Custom Menus in Excel through VBA

    Source :

    Sub AddMenus()
    Dim cMenu1 As CommandBarControl
    Dim cbMainMenuBar As CommandBar
    Dim iHelpMenu As Integer
    Dim cbcCutomMenu As CommandBarControl

    '(1)Delete any existing one. We must use On Error Resume next _
    in case it does not exist.
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
    On Error GoTo 0

    '(2)Set a CommandBar variable to Worksheet menu bar
    Set cbMainMenuBar = _
    Application.CommandBars("Worksheet Menu Bar")

    '(3)Return the Index number of the Help menu. We can then use _
    this to place a custom menu before.
    iHelpMenu = _

    '(4)Add a Control to the "Worksheet Menu Bar" before Help.
    'Set a CommandBarControl variable to it
    Set cbcCutomMenu = _
    cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _

    '(5)Give the control a caption
    cbcCutomMenu.Caption = "&New Menu"

    '(6)Working with our new Control, add a sub control and _
    give it a Caption and tell it which macro to run (OnAction).
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
    .Caption = "Menu 1"
    .OnAction = "MyMacro1"
    End With
    '(6a)Add another sub control give it a Caption _
    and tell it which macro to run (OnAction)
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
    .Caption = "Menu 2"
    .OnAction = "MyMacro2"
    End With
    'Repeat step "6a" for each menu item you want to add.

    Creating Custom Menus in Power Point

    A very good link to learn about Powerpoint.


    Thursday, April 3, 2008

    Adding an item in Right Click Menu.

    Option Explicit

    Private Sub Workbook_Open()
        Call addButton
    End Sub

    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        Call addButton
    End Sub

    Sub addButton()
    Dim cBut
    On Error Resume Next
    On Error GoTo 0
        Set cBut = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
        With cBut
            .Caption = "RighClickMenu"
            .Style = msoButtonCaption
            .OnAction = "MacroName"
        End With
    End Sub

    Someone asked for help for creating a submenu in the right click menu. For example, An item like ColorRange in the right click pop up menu, and if you go over it, then it gives option to color the cells.

    Following is the code for that:

    Write the following code in the workbooks open method to add the item automatically once you open the workbook.

    Sub addButton()
    Dim cBut As CommandBarPopup
    Dim pic As PictureFormat

    On Error Resume Next
    On Error GoTo 0
    Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, temporary:=True)

    With cBut
    .Caption = "Color Menu"
    End With

    Dim red As CommandBarButton
    Dim blue As CommandBarButton
    Dim green As CommandBarButton
    Dim black As CommandBarButton

    Set red = cBut.Controls.Add(temporary:=True)
    Set blue = cBut.Controls.Add(temporary:=True)
    Set green = cBut.Controls.Add(temporary:=True)
    Set black = cBut.Controls.Add(temporary:=True)

    red.Caption = "Red"
    blue.Caption = "Blue"
    green.Caption = "Green"
    black.Caption = "Black"
    red.Picture = stdole.LoadPicture("c:\red.bmp")
    green.Picture = stdole.LoadPicture("c:\green.bmp")
    black.Picture = stdole.LoadPicture("c:\black.bmp")
    blue.Picture = stdole.LoadPicture("c:\blue.bmp")
    red.OnAction = "RedColor"
    blue.OnAction = "blueColor"
    green.OnAction = "greenColor"
    black.OnAction = "blackColor"
    End Sub

    Private Sub Workbook_Open()
    Call addButton
    End Sub

    Once you are done with the above code, then create a new module and paste the following code in the newly created module.

    Enum Color
    End Enum

    Sub RedColor()
    ColorCell (
    End Sub
    Sub BlueColor()
    ColorCell (
    End Sub
    Sub GreenColor()
    ColorCell (
    End Sub
    Sub BlackColor()
    ColorCell (
    End Sub

    Sub ColorCell(clr As Color)

    Dim rng As Range
    Dim sht As Worksheet
    Dim typNme As String

    typNme = TypeName(Application.Selection)

    Select Case (typNme)
    Case "Range"
    Set rng = Application.Selection

    Select Case (clr)
    rng.Interior.Color = ColorConstants.vbRed
    rng.Interior.Color = ColorConstants.vbBlack
    rng.Interior.Color = ColorConstants.vbBlue
    rng.Interior.Color = ColorConstants.vbGreen
    End Select
    End Select

    End Sub

    Wednesday, April 2, 2008

    Creating Excel Menu from Dot net 2003

    using System;
    using Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Core;

    namespace ConsoleApplication3

    class Class1
    /// Summary description for Class1.

    private Application excel= new ApplicationClass();
    private Workbook book= null;
    private Worksheet sheet = null;
    private CommandBar MainMenuBar = null;
    private CommandBarControl MenuBarItem = null;
    //private CommandBarButton MenuItem = null;
    public Class1()
    book = excel.Workbooks.Open("C:\\log.xls",0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false);
    InitMenuBarItems("My Main Menu");
    sheet = (Worksheet)book.Sheets.get_Item(1);
    Console.WriteLine("The file name is {0}",sheet.Name);

    private void InitMenuBarItems(string Caption)
    MainMenuBar =
    excel.CommandBars["Worksheet Menu Bar"];
    MenuBarItem = MainMenuBar.Controls.Add(
    Type.Missing, Type.Missing, Type.Missing, true);
    MenuBarItem.Caption = Caption;



    static void Main(string[] args)
    Class1 cls = new Class1();


    Create and register ADDin to Use in Excel

    namespace NAddIn
    public class Functions
    public Functions()

    public double Add2(double v1, double v2)
    return v1 + v2;

    public static void RegisterFunction(Type t)
    "CLSID\\{" + t.GUID.ToString().ToUpper() +

    public static void UnregisterFunction(Type t)
    "CLSID\\{" + t.GUID.ToString().ToUpper() +

    Build the NAddIn project to create bin\debug\NAddIn.dll.
    Test the Add-In in Excel:
    Open a new workbook in Excel.
    Select Tools, Add-Ins, Automation.
    NAddIn.Functions should be listed - select it. OK.
    In a cell, type =Add2(3,4)
    The cell should display 7.
    To register the .dll after moving it, run regasm with the /codebase flag (typically as c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm /codebase NAddIn.dll). You will get a warning about the assembly being unsigned - you can ignore this (or sign the assembly as documented).

    Wednesday, March 26, 2008

    Reading and writing in a TextFile from VBA

    Sub LogInformation(LogMessage As String)
    Const LogFileName As String = "C:\FOLDERNAME\TEXTFILE.LOG"
    Dim txtString as String
    Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, LogMessage ' write information at the end of the text file
    Close #FileNum ' close the file
    'For reading the file :
    While not EOF(FileNum)
    Line Input #FileNum, txtString ' txtString contains the line read by the command
    End Sub

    Tuesday, March 25, 2008

    Import data from a text file (ADO)

    The procedure below can be used to get an ADO recordset from a text file and fill in the result in a worksheet.

    Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range)
    ' example: GetTextFileData "SELECT * FROM filename.txt", _
    "C:\FolderName", Range("A3")
    ' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
    "C:\FolderName", Range("A3")
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
    If rngTargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=" & strFolder & ";" & _
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
    Set cn = Nothing
    Exit Sub
    End If
    ' the field headings
    For f = 0 To rs.Fields.Count - 1
    rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
    Next f
    rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
    'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
    Set rs = Nothing
    Set cn = Nothing
    End Sub

    The procedure can be used like this:

    Sub TestGetTextFileData()
    Application.ScreenUpdating = False
    GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3")
    ' GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
    "C:\FolderName", Range("A3")
    ActiveWorkbook.Saved = True
    End Sub

    Monday, March 24, 2008

    Code to View Pivot Data within a date range

    The following code works on the pivot with the following format :

    Items Date1Date2Date3Date4
    Item 1 DataDataDataData
    Item 2 DataDataDataData
    Item 3 DataDataDataData
    Item 4DataDataDataData

    Suppose, if you have a combobox with the date range, and you want to show those date columns which fall in the given range, following is the code which you can use. The example takes a week input from the user, where, in the AN Column, the Combobox option is given and in the AO column, the actual date value is given. For example, in AN1 the value is given as February-17 to February-23, and in AO1, the value of Feb 17 is given as 39495.

    Public Sub CMBMacro()
    Dim prevIndx As Integer
    Dim cmbValue As Long
    Dim upRange As Long
    Dim pivot As PivotTable
    Dim pvtItm As PivotItem
    Dim ifNum As Long
    Dim iCounter As Integer
    Dim strngArr() As String
    Dim strngCn As String
    Dim prvDate As Long
    Dim position As Integer
    Dim prevItm() As PivotItem
    Dim indxChckd As Integer
    Dim extDate As Integer
    Dim bool As Boolean
    indxChckd = Me.Range("k1").Value
    cmbValue = Me.Range("AO" & indxChckd).Value - 1 ' It will take the Date selected in the combo.
    upRange = cmbValue + 7 'It will add up 7 days to get the upper range of the week.
    iCounter = 0
    If Not Application.EnableEvents Then
    Exit Sub
    End If
    position = 2
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set pivot = Me.PivotTables(pvtName)
    bool = False
    For Each pvtItm In pivot.PivotFields("Date").PivotItems
    If IsDate(pvtItm.Value) And pvtItm.RecordCount > 0 Then
    ifNum = CDate(pvtItm.Value)
    If ifNum > cmbValue And ifNum < upRange Then
    If pvtItm.Visible = False Then pvtItm.Visible = True
    bool = True
    iCounter = iCounter + 1
    End If
    End If
    ReDim prevItm(iCounter)
    iCounter = 1

    For Each pvtItm In pivot.PivotFields("Date").PivotItems
    If IsDate(pvtItm.Value) And pvtItm.RecordCount > 0 Then
    ifNum = CDate(pvtItm.Value)
    If (ifNum <= cmbValue Or ifNum >= upRange) And pvtItm.Visible = True And bool Then
    pvtItm.Visible = False
    End If

    If pvtItm.Visible = True Then
    Set prevItm(iCounter) = pvtItm
    iCounter = iCounter + 1
    End If
    End If

    ************************************************The Following Code sorts the date columns. It is required if the date columns are not sorted by default********* *************************************
    For position = 1 To UBound(prevItm)
    prvDate = CDate(prevItm(position))
    For iCounter = position + 1 To UBound(prevItm)
    ifNum = CDate(prevItm(iCounter))
    If prvDate > ifNum Then
    prevItm(position).position = prevItm(iCounter).position
    End If

    If Not bool Then
    MsgBox "No Items Found for this range.", vbCritical, "Error"
    End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub

    Hope this has helped you all.