Excel Power

Loop through all the files in C# 2010

2012-02-06T23:56:00.000-08:00

Hi All,

I migrated from VBA to C# a while back. Earlier, with VBA, I used to create Re-Cursive function to loop through all inner folders in a given folder. For example, if I have to search whole files in C: Drive, I will probably need to loop through Drive, then folders and subfolders in VBA. But in C#, you can simply give a parameter to search through the subdirectories as well.

Code:

DirectoryInfo parent = new DirectoryInfo(@"C:\Vikas");
FileInfo[] files = parent.GetFiles("*.*", SearchOption.AllDirectories);

The files array will contain all the files within the Folder "Vikas" and it's subfolder.

Thanks,
Vikas B

Org Charts using Google docs

2012-02-06T23:53:00.001-08:00

Hi All,

I was reading a thread in LinkedIn. Someone had asked if it is possible to create a ORG chart in Excel or not. I tried to google it and found a very interesting way to do it using Google Docs.

Create Org Chart using Google Docs/

Cheers!
Vikas B

ORACLE DAL in C#

2011-12-28T01:38:00.000-08:00

Hi All,

We can easily find the Data Access Layer for SQL, but here is one which we created for Oracle. Hope it would help you all.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using Demo.Common;

namespace Demo.DataAccessLayer
{

    ////////////////////////////////////////////////////////////////////////////
    /// <summary>
    ///   Defines common DataService routines for transaction management, 
    ///   stored procedure execution, parameter creation, and null value 
    ///   checking
    /// </summary>    
    ////////////////////////////////////////////////////////////////////////////
    public class DataServiceBase
    {

        ////////////////////////////////////////////////////////////////////////
        // Fields
        ////////////////////////////////////////////////////////////////////////
        private bool _isOwner = false;   //True if service owns the transaction        
        private SqlTransaction _txn;     //Reference to the current transaction

        ////////////////////////////////////////////////////////////////////////
        // Properties 
        ////////////////////////////////////////////////////////////////////////
        public IDbTransaction Txn
        {
            get { return (IDbTransaction)_txn; }
            set { _txn = (SqlTransaction)value; }
        }


        ////////////////////////////////////////////////////////////////////////
        // Constructors
        ////////////////////////////////////////////////////////////////////////

        public DataServiceBase() : this(null) { }


        public DataServiceBase(IDbTransaction txn)
        {
            if (txn == null)
                _isOwner = true;
            else
            {
                _txn = (SqlTransaction)txn;
                _isOwner = false;
            }
        }


        ////////////////////////////////////////////////////////////////////////
        // Connection and Transaction Methods
        ////////////////////////////////////////////////////////////////////////
        protected static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        }


        public static IDbTransaction BeginTransaction()
        {
            SqlConnection txnConnection =
                new SqlConnection(GetConnectionString());
            txnConnection.Open();
            return txnConnection.BeginTransaction();            
        }


        ////////////////////////////////////////////////////////////////////////
        // ExecuteDataSet Methods
        ////////////////////////////////////////////////////////////////////////
        protected DataSet ExecuteDataSet(string procName,
            params IDataParameter[] procParams)
        {
            SqlCommand cmd;
            return ExecuteDataSet(out cmd, procName, procParams);
        }


        protected DataSet ExecuteDataSet(out SqlCommand cmd, string procName,
            params IDataParameter[] procParams)
        {
            SqlConnection cnx = null;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            cmd = null;

            try
            {
                //Setup command object
                cmd = new SqlCommand(procName);
                cmd.CommandType = CommandType.StoredProcedure;
                if (procParams != null)
                    for (int index = 0; index < procParams.Length; index++)
                        cmd.Parameters.Add(procParams[index]);

                da.SelectCommand = (SqlCommand)cmd;

                //Determine the transaction owner and process accordingly
                if (_isOwner)
                {
                    cnx = new SqlConnection(GetConnectionString());
                    cmd.Connection = cnx;
                    cnx.Open();
                }
                else
                {
                    cmd.Connection = _txn.Connection;
                    cmd.Transaction = _txn;
                }

                //Fill the dataset
                da.Fill(ds);
            }
            catch
            {
                throw;
            }
            finally
            {
                if(da!=null) da.Dispose();
                if(cmd!=null) cmd.Dispose();
                if (_isOwner)
                    cnx.Dispose(); //Implicitly calls cnx.Close()
            }
            return ds;
        }


        ////////////////////////////////////////////////////////////////////////
        // ExecuteNonQuery Methods
        ////////////////////////////////////////////////////////////////////////
        protected void ExecuteNonQuery(string procName,
            params IDataParameter[] procParams)
        {
            SqlCommand cmd;
            ExecuteNonQuery(out cmd, procName, procParams);
        }


        protected void ExecuteNonQuery(out SqlCommand cmd, string procName,
            params IDataParameter[] procParams)
        {
            //Method variables
            SqlConnection cnx = null;
            cmd = null;  //Avoids "Use of unassigned variable" compiler error

            try
            {
                //Setup command object
                cmd = new SqlCommand(procName);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int index = 0; index < procParams.Length; index++)
                    cmd.Parameters.Add(procParams[index]);

                //Determine the transaction owner and process accordingly
                if (_isOwner)
                {
                    cnx = new SqlConnection(GetConnectionString());
                    cmd.Connection = cnx;
                    cnx.Open();
                }
                else
                {
                    cmd.Connection = _txn.Connection;
                    cmd.Transaction = _txn;
                }

                //Execute the command
                cmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (_isOwner)
                    cnx.Dispose(); //Implicitly calls cnx.Close()

                if (cmd != null) cmd.Dispose();
            }
        }


        ////////////////////////////////////////////////////////////////////////
        // CreateParameter Methods
        ////////////////////////////////////////////////////////////////////////
        protected SqlParameter CreateParameter(string paramName,
            SqlDbType paramType, object paramValue)
        {
            SqlParameter param = new SqlParameter(paramName, paramType);
            
            if (paramValue != DBNull.Value)
            {
                switch (paramType)
                {
                    case SqlDbType.VarChar:
                    case SqlDbType.NVarChar:
                    case SqlDbType.Char:
                    case SqlDbType.NChar:
                    case SqlDbType.Text:
                        paramValue = CheckParamValue((string)paramValue);
                        break;
                    case SqlDbType.DateTime:
                        paramValue = CheckParamValue((DateTime)paramValue);
                        break;
                    case SqlDbType.Int:
                        paramValue = CheckParamValue((int)paramValue);
                        break;
                    case SqlDbType.UniqueIdentifier:
                        paramValue = CheckParamValue(GetGuid(paramValue));
                        break;
                    case SqlDbType.Bit:
                        if (paramValue is bool) paramValue = (int)((bool)paramValue ? 1 : 0);
                        if ((int)paramValue < 0 || (int)paramValue > 1) paramValue = Constants.NullInt;
                        paramValue = CheckParamValue((int)paramValue);
                        break;
                    case SqlDbType.Float:
                        paramValue = CheckParamValue(Convert.ToSingle(paramValue));
                        break;
                    case SqlDbType.Decimal:
                        paramValue = CheckParamValue((decimal)paramValue);
                        break;
                }
            }
            param.Value = paramValue;
            return param;
        }

        protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, ParameterDirection direction)
        {
            SqlParameter returnVal = CreateParameter(paramName, paramType, DBNull.Value);
            returnVal.Direction = direction;
            return returnVal;
        }

        protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, ParameterDirection direction)
        {
            SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
            returnVal.Direction = direction;
            return returnVal;
        }

        protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size)
        {
            SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
            returnVal.Size = size;
            return returnVal;
        }

        protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size, ParameterDirection direction)
        {
            SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
            returnVal.Direction = direction;
            returnVal.Size = size;
            return returnVal;
        }

        protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size, byte precision)
        {
            SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
            returnVal.Size = size;
            ((SqlParameter)returnVal).Precision = precision;
            return returnVal;
        }

        protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size, byte precision, ParameterDirection direction)
        {
            SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
            returnVal.Direction = direction;
            returnVal.Size = size;
            returnVal.Precision = precision;
            return returnVal;
        }


        ////////////////////////////////////////////////////////////////////////
        // CheckParamValue Methods
        ////////////////////////////////////////////////////////////////////////
        protected Guid GetGuid(object value)
        {
            Guid returnVal = Constants.NullGuid;
            if (value is string)
                returnVal = new Guid((string)value);
            else if (value is Guid)
                returnVal = (Guid)value;

            return returnVal;
        }

        protected object CheckParamValue(string paramValue)
        {
            if (string.IsNullOrEmpty(paramValue))
                return DBNull.Value;
            else
                return paramValue;
        }

        protected object CheckParamValue(Guid paramValue)
        {
            if (paramValue.Equals(Constants.NullGuid))
                return DBNull.Value;
            else
                return paramValue;
        }

        protected object CheckParamValue(DateTime paramValue)
        {
            if (paramValue.Equals(Constants.NullDateTime))
                return DBNull.Value;
            else
                return paramValue;
        }

        protected object CheckParamValue(double paramValue)
        {
            if (paramValue.Equals(Constants.NullDouble))
                return DBNull.Value;
            else
                return paramValue;
        }

        protected object CheckParamValue(float paramValue)
        {
            if (paramValue.Equals(Constants.NullFloat))
                return DBNull.Value;
            else
                return paramValue;
        }

        protected object CheckParamValue(Decimal paramValue)
        {
            if (paramValue.Equals(Constants.NullDecimal))
                return DBNull.Value;
            else
                return paramValue;
        }

        protected object CheckParamValue(int paramValue)
        {
            if (paramValue.Equals(Constants.NullInt))
                return DBNull.Value;                
            else
                return paramValue;
        }
    }
}

Reading File Tags in VB.Net

2011-12-28T01:30:00.000-08:00

Hi All,

There are times when we add the tags to our files. We can use the Shell32 folder to read the tags. I found a little problematic for the first timer, so adding the code to read the tags here :

Sub TestTags()
Dim fInfo As New FileInfo("Path of your File goes here")
Dim tags As String = ReadFileAttributes(fInfo)
End Sub

Public Function ReadFileAttributes(fInfo As FileInfo) As String

Dim shell As New Shell32.Shell
Dim objFolder As Shell32.Folder
Dim items As Shell32.FolderItems
Try
objFolder = shell.NameSpace(fInfo.Directory.FullName)
items = objFolder.Items
Return objFolder.GetDetailsOf(items.Item(fInfo.Name), 18)
Catch ex As Exception
Return "Caught Exception in Tags" 'exception..cannot find the tags
End Try

End Function

.

You have to add a reference to Microsoft Shell Controls and Automation from the COM tab of the Refences dialog.

HTH,
Vikas

How to Add a Reference in VBA Editor

2011-09-02T08:40:00.000-07:00

Hi All!

Although VBA edit does provide loads of libraries inherently, still, we may need to use few libraries which are not included by default. For example, For using Scripting.Dictionary object, or FileSystem object to search the fileSystem in an organized way, we need to add a reference to Microsoft.Scripting.Runtime.

Adding a reference purely means to instruct the VBA Compiler/Editor to include the functionality of a particular library so that we can use the functions available in the added library.

If you try to create a FileSystemObject, and write the following code:

Dim dictionary_ as Scripting.Dictionary

If we try to compile or run the code with out adding the reference it will show us the following error :

Add the reference to Microsoft.Scripting.Runtime as shown in the picture below. Go to Tools --> Reference --> Look for Microsoft.Scripting.Runtime. If you are unable to find it in the options, then you will need to browse for scrrun.dll.

Thanks,
Vikas

Ever Wondered for Obfuscating VSTO .net Solutions? Here is the solution by Michael Zlatkovsky

2011-08-30T03:33:00.000-07:00

Hi All,

I tried to obfuscate my VSTO COM code lots of time, but it never used to register the DLLs and Excel was never able to call my DLL successfully after Obfuscating. Although I have not tried it yet, but it seems we have got the solution :)

This is posted from a MSDN Forum thread. It was posted by Michael Zlatkovsky and is very very useful. Please see what he had posted :

Just to report back: after a few weeks of trying out various obfuscators, I finally did manage to obfuscate my VSTO project, both cheaply (only $179) and (now that I've got everything configured properly) painlessly.

The obfuscator that I settled on is called .NET Reactor (http://www.eziriz.com/dotnet_reactor.htm). It is VERY well-engineered piece of software, and costs remarkably little, $179 (compared to, say, Preemptive's Dotfuscator, which even after I emailed them and explained that I'm a freelance developer with little money, the best they could do is drop the price from ~$2000 to a "mere" $995). Unlike some of the other obfuscators, .NET Reactor works with VSTO solutions, and, also very importantly, it integrates with Visual Studio to make the obfuscation process just part of the build/publish one-click process. The importance of this integration is something you'll only discover when you need to obfuscate and sign assemblies manually -- which is a very tedious, 10-minute process of copying a file, obfuscating it, copying it back, re-signing the .dll.deploy file with MAGE, re-signing the .vsto file with MAGE, etc.

.NET Reactor's chief developer, Denis Mierzwiak, was VERY helpful and available when I needed some help in configuring .NET Reactor for VSTO. Initially my project was not lending itself to obfuscation, but Denis spent several hours with me on Skype to get things working, and kept sending me new releases by email as we continued to work out the kinks in the system. The Obfuscation now works 100%, integrates beautifully into Visual Studio, and is completely painless -- I actually can't recall a time when I was more pleased with a software product or with the level of technical support!

For those who'll be using .NET Reactor for VSTO, just a few hints:
1) It might be that the latest changes to the .NET Reactor (introduced for better VSTO support and integration) are still not in the "official" release -- you might want to email support@eziriz.com to ask about obfuscating an Office solution and to get the latest version.

2) It IS possible to fully obfuscate a VSTO solution (including renaming public types); since you are obfuscating a .dll file, however, that option is disabled by default. To enable it, go to the Settings tab, option #2 (Protection Settings), Obfuscation, and select "obfuscate public types".

3) Once you obfuscate public types, you will need to add a regular expression to EXCLUDE the several classes that VSTO expects (under .NET Reactor's settings -> Protections Settings -> Obfuscation -> Exclusions -> Regular Expressions). In the case of an Excel spreadsheet, those would be ExcelSampleWorkbook\.Sheet1;ExcelSampleWorkbook\.Sheet2;ExcelSampleWorkbook\.Sheet3;ExcelSampleWorkbook\.ThisWorkbook

4) Finally, if you obfuscate public types but exclude the required VSTO objects from obfuscation, be aware that code in those files (Sheet1, ThisWorkbook, etc) will obviously NOT be obfuscated. In my case, I created a "HelperDelegate" for each of those classes (i.e., ThisWorkbookDelegate) to hide the code behind the delegate. All that my Excel objects carry are event handlers (things like BeforeDoubleClick) and a reference to the delegate (so I can call Globals.ThisWorkbook.helper.myFunctionName())

Hope my experience is useful!

- Michael Zlatkovsky

HTH,
Vikas

Removing Duplicates with in the worksheet

2011-08-26T07:02:00.000-07:00

Hello All,

I had asked by someone to create a code for removing a duplicate from worksheets.

The requirement was such that one keyword should only appears once in Column B in all the worksheets. So if a value (for example "Data-1234") appears in sheet 1 in column B, then it cannot appear again in Column B, in any of the worksheets. If it appears, then move it to Duplicate sheet.

So I used the following code. Thought it might be a help so posting it here. To make it work, you will need to add a reference to Microsoft Scripting Runtime.



Const ColumnToSearch As String = "B"

Const ColumnToSearch_i As Integer = 2

Private duplicates As Scripting.Dictionary



Sub MoveDuplicates()



    Dim blankCounter As Integer

    Dim rowCounter As Long

    Dim duplicate As Worksheet

    Dim sht As Worksheet

    Dim runLoop As Boolean

    Dim value As String

    Dim added As Boolean

    Dim lastRow As Long

    

    runLoop = True

    

    

    Set duplicates = New Scripting.Dictionary

    

    

    Set duplicate = GetWorksheet(ActiveWorkbook, "DuplicateEntries")

    

    If (duplicate Is Nothing) Then

        Set duplicate = ActiveWorkbook.Worksheets.Add

        duplicate.name = "DuplicateEntries"

    End If

    

    

    'get duplicate sheet

    

    For Each sht In ActiveWorkbook.Worksheets

        

        blankCounter = 1

        rowCounter = 2

        runLoop = True

        

        

        

        

        If (sht.name <> "DuplicateEntries") Then

            

            lastRow = GetLastRow(duplicate, 1)

            

            If (lastRow > 1) Then

                lastRow = lastRow + 2

            End If

            

            'enter the log

            

            duplicate.Range("A" & lastRow).value = "Duplicate Entries Entered on : " & Now & " from Worksheet " & sht.name

            duplicate.Range("A" & lastRow + 1).value = "'-"

            duplicate.Range("A" & lastRow + 2).value = "'-"

            

            

            

            

            While runLoop

                If (blankCounter > 10) Then

                    runLoop = False

                Else

                    'check blank

                    

                    value = Trim(sht.Cells(rowCounter, ColumnToSearch_i).value)

                    

                    

                    If (value = "") Then

                        blankCounter = blankCounter + 1

                    Else

                        blankCounter = 1

                        added = AddSuccess(value)

                        lastRow = GetLastRow(duplicate, 1) + 1

                        If (added) Then

                             'dont do anything. not a duplicate

                        Else

                            'Duplicate item, Move

                            MoveRow sht.Cells(rowCounter, ColumnToSearch_i), duplicate.Cells(lastRow, 1)

                            rowCounter = rowCounter - 1

                        End If

                        

                    End If

                    

                    

                End If

                

                rowCounter = rowCounter + 1

                

            Wend

        End If

    Next sht

    

    

End Sub



Private Function GetLastRow(sht As Worksheet, Optional Col As Integer = 1) As Long

    Dim rng As Range

    

    GetLastRow = sht.Cells(sht.Rows.Count - 1, Col).End(xlUp).Row

    

End Function



Private Function GetWorksheet(wkb As Workbook, shtName As String) As Worksheet

    Dim sht As Worksheet

    

    On Error Resume Next

        Set GetWorksheet = wkb.Worksheets(shtName)

    Err.Clear

    

End Function



Private Sub MoveRow(rngToMove As Range, moveAt As Range)

    rngToMove.EntireRow.Copy moveAt

    rngToMove.EntireRow.Delete

End Sub



Private Function AddSuccess(name As String) As Boolean

    Err.Clear

    On Error Resume Next

        duplicates.Add name, name

            

        If (Err.Description <> "") Then

            AddSuccess = False

        Else

            AddSuccess = True

        End If

            

    Err.Clear

End Function

HTH,
Vikas

Add-in Express discounts and special offers

2011-07-24T11:29:00.000-07:00

Add-in Express discounts and special offers

Excel VBA : Find data using Control+F and not looping

2010-09-06T02:41:00.000-07:00

Hi All,

I was sitting and reading few posts and saw that lots of people tries to loop and find the data in different ranges. But it really becomes very very slow if the data you are looping at is huge. Also, what if probability of the occurrences of the desired string/value is very low.

Lets take an example, if I am using approx 1 Million cells in a worksheet, and I am trying to find if a particular text "xysqhqhsyqhsn" is available in how many cells. Obviously, the probability is quite low, and even equal to nil that I will find this string at all in the whole worksheet. But to find, I will need to search first to ensure that the value doesn't exist in the worksheet or not. Now, which option which one would like to have, either to loop to all the 1 million cells or just to jump on the cells which contains the value?

Obviously, in out case, we would like for the second option. I always do that. I always go for the second option to search the string in a worksheet, rather than looping through all the cells, a slow movement.

I have shown an example here. In this example, first I take an input from the user, and tries to give a counter which signifies that the input string exists in how many cells. I have used a function "GetValidRanges" which would return a collection of cells, as shown below

Sub FindInstances()
    Dim inputStr As String ' the variable to accept the input from user
    Dim rngToFind As Range ' the range where we have to find
    Dim activeSht As Worksheet
    Dim cell As Range
    Dim firstAddress As String
    Dim txtCounter As String
    Dim ranges As Collection
    
    'take the input of the string which we want to search.
    
    inputStr = InputBox("Please enter the string which you want to search.")
    Set activeSht = ActiveSheet
    Set rngToFind = activeSht.Range("A1:A300")
    If (inputStr = "") Then
        MsgBox "Invalid Input"
    Else
        'there is a string to search
        Set ranges = GetValidRanges(inputStr, rngToFind)
        
        MsgBox "Total number of occurrences are " & ranges.Count
        
    End If
    
    
    
    
End Sub


'''this function loops through the cells in the range passed, and returns a collection of those cells (eventually range objects) which contains the passed values.

Public Function GetValidRanges(inputStr As String, rngToSearch As Range) As Collection
    Dim cell As Range
    Dim firstAddress As String
    Dim ranges As Collection
    
    Set ranges = New Collection
    
    With rngToSearch
        Set cell = .Find(inputStr, LookIn:=xlValues)
        
        If Not cell Is Nothing Then
            firstAddress = cell.Address
            
            Do
                ranges.Add cell
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> firstAddress
        End If
    End With
    
    Set GetValidRanges = ranges
    
End Function

User can easily use this collection in many ways. We can get the count of the total cells returned, or loop through all the items in the collection to get the actual address.

Hope this helps :)

Thanks,
Vikas

Dynamic Named Ranges

2010-08-03T07:53:00.000-07:00

Hi All,

I see a lot of time people ask that they cannot find the last row of the data. One of the most complicated scenario would be, to assign the dynamic source range to a chart. Suppose I have a chart and the number of rows in my chart would keep on changing time to time, so how would I manage this? One option is to use VBA code and assign a new source data whenever it is refreshed. But do we really need code behind to resolve this problem? I would say no :)

Before going further, I would like to tell you about Offset function, which i will be using to explain the Dynamic named Ranges.

Syntax of Offset function is :

OFFSET(reference,rows,cols,height,width)

Offset, in itself is a powerful function to design a dynamic range.

Parameter 1, Reference : This would be the starting point of your range. Offset function requires you to provide it a starting point, on which you will finally decide what range to be consider.

Parameter 2, Rows : This number is used to shift the starting point. Suppose if the Starting Point is A1, and the number of Rows (second parameter) is 2, then the starting point will be shifted by 2 rows and will point to A3.

Parameter 3, Cols : This number is used to shift the starting point by columns. Suppose if the Starting Point is A1, and the number of columns (third parameter) is 5, then the starting point will be shifted by 5 Columns and will point to F1.

Parameter 4, Height : This would represent the number of cells in a column, aka, the height of range. This can be assumed as the height dimension of a matrix.

Parameter 5, Width : This would represent the width of the range.

Examples :

Offset($A$1,1,1,5,5) : The range is $B$2:$F$6.

Explanation : Starting point is $A$1. Second Parameter, shift row by 1 so It will point to $A$2. As per third Parameter, shift col by 1 so it will point for $B$2. Forth parameter says that the Height of the required range should be 5 cells. So it will be : $B$2:$B$6. The fifth and last parameter says that the width of the range should be 5 columns. So the final range would turn out to be $B$2:$F$6.

Dynamic ranges can be very handy and useful in creating charts when the source range has to be dynamic and is of changing nature. For example, suppose if we have the following chart :

You can see that we have a very simple chart, with the source data as $A$3:$B$11. However, the data can be of a changing nature. For example, see the following screenshot...the data is increased by 5 rows and the new source should be $A$3:$B$16.

Rather than assigning the source data to chart again and again, we can create a Dynamic Named Range which can be flexible if the rows of our data are increased. Dynamic Named Ranges are nothing but a Name Range, with Offset Formula in its target range.

The chart earlier had the series as $A$3:$B$11, if you see and edit the source data of chart, you will see two ranges. One is the Range for Series :- $B$4:$B$11 and X Series which is dates :- $A$4:$A$11.

To create this, I would first create a X Range, which will point to the Date Column of our chart. It will certainly involve the Offset function. To get the dynamic range for Dates, I will start my Offset from $A$4, which is the start point. Setting up next two parameters as 0, 0 so that the starting point should not be changed. Since we have to calculate the rows on run time, so I will use CountA formula so that I can get the exact height of the data. CountA function returns the number of non blank cells in the specified range. Suppose I try CountA($A$4:$A$410) function on the data (as shown in the second image), it would return 13. Because in the entire range (A4:A410) there are only 13 non blank cells. Rest are Blank. Width would be one to ensure that we are talking about a single column(or we can say series or x series values). See the following screenshot :

Similarly, I would create a SharePriceRange

After creating the dynamic named ranges, I will need to assign the name ranges to our chart. In our current chart, we have one series, and one x Series. That is why I have only used two dynamic named ranges. If we will have more series, then we will need to add as many named ranges equal to the number of series present in the chart.

For assigning a named range, I will need to edit the source data. Following is the screenshot of two buttons which we may need to click in order to edit the appropriate series.

The sheet which I am operating is Sheet2, so I will need to ensure that I have included the worksheet name along with the name range. See the two screenshots :

I have created a sample spreadsheet to demonstrate it properly. To download the spreadsheet, please Click here.

Thanks,
Vikas Bhandari

Move the Excel VBA Code from One Workbook to Another.

2010-07-09T08:32:00.000-07:00

Hi All,

Recently, I wanted to move VBA Code from one workbook to another. I didn't have any idea. So my teammate did me a favor and got me the following code. So I thought that it is good to share it with the outside world. Moreover, it also moves the code with the Forms as well.

The function expects two parameters,

sourceBook: the workbook where we have to copy the code from
destBook : the workbook where the code has to be copied to

Sub MoveVBACode(sourceBook As Workbook, destBook As Workbook)

    Dim sourceVbComp As VBIDE.VBComponents
    Dim destVbComp As VBIDE.VBComponents


    Dim vbComp As VBComponent


    'To check the code is protected or not
    If sourceBook.VBProject.Protection = vbext_pp_locked Then
        MsgBox "Can't copy code because Source project code is password protected", vbInformation
        Exit Sub
    End If

    Set sourceVbComp = sourceBook.VBProject.VBComponents
    Set destVbComp = destBook.VBProject.VBComponents

    'To copy the modules from source to destination
    For Each vbComp In sourceVbComp
        If vbComp.Type = vbext_ct_Document Then
            CopyModule vbComp, destVbComp, ""
        ElseIf vbComp.Type = vbext_ct_StdModule Then
            CopyModule vbComp, destVbComp, ".bas"
        ElseIf vbComp.Type = vbext_ct_MSForm Then
            CopyModule vbComp, destVbComp, ".frm"
        ElseIf vbComp.Type = vbext_ct_ClassModule Then
            CopyModule vbComp, destVbComp, ".cls"
        End If
    Next

End Sub

Function CopyModule(sourceVbComp As VBIDE.VBComponent, _
                    destVbComp As VBIDE.VBComponents, _
                    extn As String)
    
Dim fName As String
Dim vbComp As VBIDE.VBComponent
Dim destCodeMod As VBIDE.CodeModule
Dim codeText As String
Dim destCodeModule As Variant
Dim TempVBComp As Variant

Const filePath As String = "C:\"
Const xportPath As String = "C:\Temp\"
    
    fName = xportPath & sourceVbComp.Name & extn
    
    sourceVbComp.Export fName
    
    If sourceVbComp.Type = vbext_ct_Document Then
    'To copy the code form ThisWorkbook and sheets
        
        'To get the code module in the destination file
        For Each vbComp In destVbComp
            If vbComp.Type = vbext_ct_Document And vbComp.Name = sourceVbComp.Name Then
                Set destCodeModule = vbComp.CodeModule
                Exit For
            End If
        Next
        
        'To update the code from source to destination
        If Not destCodeModule = "" Then
            Set TempVBComp = destVbComp.Import(fName)
            With destCodeModule
                If TempVBComp.CodeModule.CountOfLines > 0 Then
                     codeText = TempVBComp.CodeModule.Lines(1, TempVBComp.CodeModule.CountOfLines)
                    .InsertLines 1, codeText
                End If
            End With
              destVbComp.Remove TempVBComp
        End If
        
    Else ' For modules ,forms and classes
        destVbComp.Import fName
    End If
        
    'To kill the exported file
    Kill fName

End Function

Thanks,
Vikas

Move all worksheets from a workbook to another

2010-07-09T08:24:00.000-07:00

Hi,

Ever moved all the worksheets from a workbook to another. I did it previously, so thought to share the code with you all.

Public Sub CopyWorksheets(sourceBook As Workbook, destBook As Workbook)
   
    Dim shtCounter As Integer
    Dim wkSheet As Worksheet
    
    'remove all the sheets from Destination workbook except one.
    'the last worksheet will be deleted once we have copied the worksheets
    
    For shtCounter = destBook.Worksheets.Count To 2 Step -1
        Set wkSheet = destBook.Worksheets(shtCounter)
        Application.DisplayAlerts = False
        wkSheet.Delete
        Application.DisplayAlerts = True
    Next shtCounter
    
    'rename the sheet1 so that we can assure there is no duplicity
    
    Set wkSheet = Worksheets(1)
    
    wkSheet.Name = "123111xSheet111789" 'giving some arbit name so that we can
                                        ' assure that the sheets we are trying
                                        'copy doesn't have the same name as the worksheet's name.
    
    'copy sheets to destWKB
    
    sourceBook.Worksheets.Copy after:=destBook.Worksheets(1)
    
    
    
    Application.DisplayAlerts = False
    wkSheet.Delete
    Application.DisplayAlerts = True
End Sub

HTH,
Vikas Bhandari

Excel - Break External Links

2010-07-09T08:18:00.000-07:00

Hi All,

Please find below the code for Breaking the external links from a Workbook.

Public Sub BreakLinks()
    Dim aLinks As Variant
    Dim i As Integer
    Dim sht As Worksheet
    
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            ActiveWorkbook.BreakLink Name:=aLinks(i), Type:=xlExcelLinks
        Next i
    End If
End Sub

Hope this helps.

Thanks,
Vikas

Allow User to Enter Data in a Protected Sheet

2010-07-09T07:30:00.000-07:00

Hi All,

We know that we can protect a worksheet so that user should not able to touch any of values in cell. You can protect the sheet by going to Review Tab --> Protect Sheet option. The option is shown in the following screenshot:

This is easy. But what if we want to allow user to enter data in some of the cells. Or if we want to open few of the cells to the user where he/she can enter his/her inputs.

Protection on a worksheet works only for those cells which are Locked. By default, every cell in a worksheet is locked so when a user protects a worksheet, all of the cells are locked by default. Vice versa, if a cell is not locked then user can edit the data even if the worksheet is protected. So to allow few of the cells to be unlocked(so that these can remain unprotected), we first need to remove the locked option from the cells. Following are the steps given to achieve this :

--> Select the cell which you want to unlock
--> Right click on that cell and click on Format Cells. It is displayed in the following screenshot :

--> Go to the protection Tab, and uncheck the "Locked" option. See the following screenshot:

If you keep this option unchecked for a cell/range, the cell/range cannot be protected. So if you want to keep few of cells open for user in a protected sheet, you can use the above mentioned procedure.

Hope this helps :)

Thanks,
Vikas

Find Last Cell, Column And Row in Excel Worksheet

2010-07-09T06:54:00.000-07:00

Hi All,

Please find below a small code snippet, which you can use to find the last Used Cell, Row or Column.

Public Function LastUsedCell() As Range
    Dim rng As Range
    Dim activeSht As Worksheet
    On Error GoTo ErrHandler
    
    Set activeSht = activeSheet

    Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    
    'return value
    Set LastUsedCell = rng
    Exit Function
ErrHandler:
    Set LastUsedCell = Nothing
End Function

Public Function LastUsedRow() As Long
    Dim rng As Range
    Dim activeSht As Worksheet
    On Error GoTo ErrHandler
    
    Set activeSht = activeSheet

    Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    
    'return value
    LastUsedRow = rng.Row
    Exit Function
ErrHandler:
    LastUsedRow = 0

End Function


Public Function LastUsedColumn() As Integer
    Dim rng As Range
    Dim activeSht As Worksheet
    On Error GoTo ErrHandler
    
    Set activeSht = activeSheet

    Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    
    'return value
    LastUsedColumn = rng.Column
    Exit Function
ErrHandler:
    LastUsedColumn = 0

End Function

Hope this helps :)

Thanks,
Vikas

Excel VBA : Loop through all the files and subfolders in a Folder

2010-07-01T04:55:00.000-07:00

Hi All,

I write this function many many times in my projects. When we have to create a temporary folder which are specific to our application, then I normally have to loop through all the files in a folder and put some validations so that my code should not replace any previous temp or log files. So I thought to share a small snippet of Excel VBA code where I am trying to search all the files in a Folder ( and its subfolders too) and printing in an Excel Worksheet.

Private fileCounter As Integer
Private activeSht As Worksheet
'Display all the files in a folder. Searches all the sub folders.

'Prints Folder Names in Column A and and the file Names in Column B

Sub SearchFiles()
    Dim pth As String
    Dim fso As FileSystemObject
    Dim baseFolder As Folder
                    
    pth = "C:\Projects\" 'the base path which has to be searched for Files
    Set fso = New FileSystemObject
                    
    ''check if the folder actually exists or not
                    
    If (Not (fso.FolderExists(pth))) Then
        'the folder path is invalid. Exiting.
        MsgBox "Invalid Path"
        Exit Sub
    End If
    
    Set baseFolder = fso.GetFolder(pth)
    
    fileCounter = 1
    Set activeSht = ActiveSheet
    
    activeSht.Range("A1").Value = "Folder Name"
    activeSht.Range("B1").Value = "File Name"
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    PrintFileNames baseFolder
    
ErrHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
    
    
Sub PrintFileNames(baseFolder As Folder)
    Dim folder_ As Folder
    Dim file_ As File
    
    For Each folder_ In baseFolder.SubFolders
        'call recursive function.
        PrintFileNames folder_
    Next folder_
    
    For Each file_ In baseFolder.Files
        'print files here
        activeSht.Range("A1").Offset(fileCounter, 0).Value = baseFolder.Path
        activeSht.Range("B1").Offset(fileCounter, 0).Value = file_.Name
        fileCounter = fileCounter + 1
    Next file_
End Sub

The above code loops through all the subfolders and the files in a given folder and prints the folder names in Column A and the file Names in Column B.

HTH,
Vikas

Write Custom Functions in Excel.

2010-07-01T04:01:00.000-07:00

Hi,

We all know that we can write Formulas in Excel. for example, =Sum(A1:A10) to sum the values present in range from A1 to A10. The summation is easy. Right??? But what if we want to tweak our requirements.

Let's assume we have a requirement, that we need to sum only those values which are divisible by 2. I have assumed a very basic example, although we can write an Excel Built in for this requirement, but I want to show to achieve this through Custom Function.

For example, I want to sum range A1 to A10. I will write a custom Function in Excel VBA. Open VBA editor, add Module 1 and add a function like following :

Public Function SumEvenValues(rng As Range) As Double
    Dim sum As Double
    Dim cell_ As Range
    
    sum = 0
    
    For Each cell_ In rng.Cells
        If (IsNumeric(cell_.Value)) Then
            'the value is numeric. Check the mod
            If (cell_.Value Mod 2 = 0) Then
                sum = sum + cell_.Value
            End If
        End If
    Next cell_
    
    SumEvenValues = sum
    
End Function

In your excel workbook, in Cell b1, type this : =SumEvenValues(A1:A10)

You will see that all the values which are divisible by 2 are summed up and the result is displayed in Cell B1. I have taken a very basic example to show it's use, which can be easily achived in Excel, but we may encounter to include those requirements which we may not be able to achieve within Excel only. So in such a case, VBA is used to handle all those requirements which Excel cannot handle.

HTH,
Vikas

Linking Two Pivot Tables

2010-04-14T02:11:00.001-07:00

Hi all,

Recently, I was making a dashboard and I had to create two Pivot Tables. My conditional formatting was set in such a manner, that I needed to have both the pivot tables updated @ same time, and the items too, to be shown or hid @ the same time in both the pivot table.

To achieve this, I first noted down the fields which I had to filter. I had three fields to filter and these items were Name, Date, and Month. So I had to make the following code :

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'ExpectedHours

If (Target.Name = "MainPivot") Then
Macro1
End If

End Sub

Sub Macro1()
'

'
'On Error Resume Next
'
      Dim pivotSource As PivotTable
      Dim pivotTarget As PivotTable
      Dim sourceItem As PivotItem
      Dim sourceField As PivotField
      Dim targetItem As PivotItem
      Dim targetField As PivotField
      
      Set pivotSource = Sheet7.PivotTables("MainPivot")
      Set pivotTarget = Sheet7.PivotTables("ExpectedHours")
      
      
    Set targetField = pivotTarget.PivotFields("department")
   
    targetField.ClearAllFilters
    targetField.CurrentPage = Sheet7.Range("Department").Value
   
   
    Set sourceField = pivotSource.PivotFields("Name")
    Set targetField = pivotTarget.PivotFields("Name")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
            
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
            End If
    Next sourceItem
      
      
    Set sourceField = pivotSource.PivotFields("Date")
    Set targetField = pivotTarget.PivotFields("Date")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
            
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
            End If
    Next sourceItem
      
            
    Set sourceField = pivotSource.PivotFields("Month")
    Set targetField = pivotTarget.PivotFields("Month")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
                  
                  If (targetItem.ShowDetail <> sourceItem.ShowDetail) Then
                        targetItem.ShowDetail = sourceItem.ShowDetail
                  End If
            End If
    Next sourceItem
End Sub


Public Function GetPivotItemByName(field As PivotField, item As String) As PivotItem
      On Error Resume Next
      Dim pvItem As PivotItem
      
      Set pvItem = Nothing
      
      Set pvItem = field.PivotItems(item)
      
      Set GetPivotItemByName = pvItem
End Function

Hope this helps :)

Thanks,
Vikas

Back to XL VBA : A Way to create Utility functions to increase reusability

2010-02-22T06:41:00.000-08:00

Hi there!

I normally create a Util Module every time I work on an Office Automation Project. I thought, people may be benefitted with it. Moreover, I will not have to create it again. I will copy it from here only ;)


Public Function SheetExists(wkb As Workbook, shtName As String) As Boolean
    Dim sht As Worksheet
    
    Set sht = Nothing
    On Error GoTo errHandler
    Set sht = wkb.Worksheets(shtName)
    SheetExists = True
    Exit Function

errHandler:
    SheetExists = False
End Function

Public Function GetSheet(wkb As Workbook, shtName As String) As Worksheet
    Dim sht As Worksheet
    
    Set sht = Nothing
    If (SheetExists(wkb, shtName)) Then
        Set sht = wkb.Worksheets(shtName)
    End If
    
    Set GetSheet = sht
End Function


Public Function RangeExists(wkSht As Worksheet, rngName As String) As Boolean
    Dim rng As Range
    
    Set rng = Nothing
    On Error GoTo errHandler
    Set rng = wkSht.Range(rngName)
    RangeExists = True
    Exit Function

errHandler:
    RangeExists = False
End Function

Public Function GetRange(wkSht As Worksheet, rngName As String) As Range
    Dim rng As Range
    
    Set rng = Nothing

    If (RangeExists(wkSht, rngName)) Then
        Set rng = wkSht.Range(rngName)
    End If
    
    Set GetRange = rng
End Function


Public Function GetRows(wkSht As Worksheet, Optional columnName As String = "A") As Long
    Dim rowCounter As Long
    Dim runLoop As Boolean
    Dim startRange As Range
    
    rowCounter = 0
    runLoop = True
    
    Set startRange = wkSht.Range(columnName & "1")
    
    While runLoop
        If (Len(startRange.Offset(rowCounter, 0).Value) > 0) Then
            rowCounter = rowCounter + 1
        Else
            runLoop = False
        End If
    Wend
    
    GetRows = rowCounter
    
End Function

Public Function GetColumns(wkSht As Worksheet, Optional rowNum As String = "1") As Long
    Dim colCounter As Long
    Dim runLoop As Boolean
    Dim startRange As Range
    
    colCounter = 0
    runLoop = True
    
    Set startRange = wkSht.Range("A" & rowNum)
    
    While runLoop
        If (Len(startRange.Offset(0, colCounter).Value) > 0) Then
            colCounter = colCounter + 1
        Else
            runLoop = False
        End If
    Wend
    
    GetColumns = colCounter
    
End Function

Thanks,
Vikas

Designing a Button in WPF - "Simplest yet cool Button"

2010-01-18T04:36:00.000-08:00

Hi All,

I have seen a lot of button templates which offer a cool looking styles by overriding the complete button template. Just to give a small style to a button can be complicated as far as overriding the template is concerned. I tried to build a button something like this :

An option could be, to include a new style for my button and override the template. But do we really need to do that???? I don't think so. The button which you are seeing in the Image is actually not a button, it is actually a border object filled with Alice Blue color, and with the corner radius set to 5. There is a textblock object inside the border so that it can contain some text. I didn't use label for that because textblock is lighter than a label.

Now, this control is not yet completed because it doesn't give any notification whenever it is clicked. So I had to include some animation to this button.

Here, I have opted to use code behind to run my animation. Like shown in the figure below :

After including the above code lines, you can see that the border looks like a button and animates as well once you click the control.

HTH,
Vikas

WPF : Changing Listview Selected Item color

2009-10-08T00:13:00.000-07:00

Hi All,

I am really loving WPF these days, all of the things which looked really tough to me earlier, are not simplified using WPF XAML.

Here is the code for changing the background color of the selected item in a listview :
Code :

< Style TargetType="ListViewItem">
< Style.Resources>
< SolidColorBrush x:Key="{x:Static SystemColors.HighlightBrushKey}" Color="WhiteSmoke"/>
< SolidColorBrush x:Key="{x:Static SystemColors.ControlBrushKey}" Color="Transparent"/>

Thanks,
Vikas

Binding Checkbox with the Listview Item Selection

2009-08-29T07:17:00.000-07:00

Hi All,

I know, lot of people juggle around for this. I also did ;)

Have you ever wondered to have a checkbox in a listview item which should be checked/unchecked as per the listviewitem selection and vice versa?

Suppose if you have a checkbox as a listview item, and you want to check/uncheck the checkbox as soon as user selects the listview item. And vice versa, the listview item should be selected as soon as user selects the checkbox. The following line solves this purpose :

HTH,
Vikas Bhandari

Formatting the GridView Column Header in Listview

2009-08-29T03:21:00.000-07:00

Hi All,

Please find below the code to set the background color of a GridviewColumnHeader inside a Listview. I will add more to it ;)

Code to set the font size of a GridviewColumnHeader :

Thanks,
Vikas

WPF : Styling a Combobox, Button and Tabcontrol

2009-08-10T08:13:00.000-07:00

Hi All,

I am still very much like a novice in my WPF skills. Still, I thought to share something useful with the world. I was looking to create a good looking Combobox style for my project. And (obviously after too much efforts), I created one. I know, it is simple but sometimes a novice like me tries to find an already build style so I thought I will post it here :

I created something like this :

You can download the complete project from here : Click here to download.

Please let me know if you have any questions or suggestions.

Thanks,
Vikas

WPF : Hide Headers from a GridviewColumn

2009-06-30T07:25:00.000-07:00

Hi,

I know, this is simple but I wasted a bunch of my valuable time in finding a solution while developing my first application in WPF. Include the following line in your < listview> block to get rid of headers in your gridview column.

< ListView.Resources>
< Style x:Key="CustomHeaderStyle" TargetType="{x:Type GridViewColumnHeader}">
< Setter Property="Visibility" Value="Collapsed" />
< /Style>
< /ListView.Resources>

Thanks,
Vikas

Reading Image Metadata Tags with Dot Net

2009-06-26T03:20:00.000-07:00

Hi All,

I had to find a way to tag images and then use the tagged images to be searched with the help of tagged keywords. Example, if some images are tagged with the keywords "xxx", then searching for xxx should produce the image with xxx tag in the search results.

I tried with various document management software and all of these are actually very handy tools but they don't add metadata tags to images. Since we are spread all over the word, I thought it will be very often that the images will flow from one corner to other and the tags should flow along with the images. So the document management software approach ended with no benefit. Then I thought to develop a sample on my own.

There was one more reason for developing it. I wanted to search the images and provide a custom task ( eg, Inserting the image in powerpoint) which no software gives.

I download a free tagging software called ITag (Click here for Download). And tag all of images with various keywords using this software. It is really a good tool to add metadata tags.

Now when I added the tags, the following xml was added in the image (you can easily read the tags by editing the image files.)

< rdf:Description rdf:about="" xmlns:xmpMM="http://ns.adobe.com/xap/1.0/mm/" xmlns:tiff="http://ns.adobe.com/tiff/1.0/" xmlns:xmp="http://ns.adobe.com/xap/1.0/" xmlns:exif="http://ns.adobe.com/exif/1.0/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmpMM:InstanceID="uuid:02ab02d9-02d1-11da-9510-ec7226ba1889" xmpMM:DocumentID="adobe:docid:photoshop:02ab02d2-02d1-11da-9510-ec7226ba1889" tiff:ResolutionUnit="2" tiff:YResolution="72/1" tiff:XResolution="72/1" tiff:Orientation="1" tiff:NativeDigest="256,257,258,259,262,274,277,284,530,531,282,283,296,301,318,319,529,532,306,270,271,272,305,315,33432;F68680A2E9A53199A34ACB3FE0102FCA" xmp:ModifyDate="2005-08-01T17:16:48+05:30" xmp:CreatorTool="Adobe Photoshop 7.0" exif:PixelYDimension="227" exif:PixelXDimension="892" exif:ColorSpace="65535" exif:NativeDigest="36864,40960,40961,37121,37122,40962,40963,37510,40964,36867,36868,33434,33437,34850,34852,34855,34856,37377,37378,37379,37380,37381,37382,37383,37384,37385,37386,37396,41483,41484,41486,41487,41488,41492,41493,41495,41728,41729,41730,41985,41986,41987,41988,41989,41990,41991,41992,41993,41994,41995,41996,42016,0,2,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,20,22,23,24,25,26,27,28,30;1064FAD10D9AAF6E380C481B30061C1D">

< dc:subject>< rdf:Bag xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">< rdf:li>Banking< /rdf:li>< rdf:li>Blinx< /rdf:li>< rdf:li>Burton< /rdf:li>< rdf:li>Wake up< /rdf:li>< /rdf:Bag>

< /dc:subject>< /rdf:Description>

Now the above XML suggests that the image is tagged with 4 tags (Wake up,Blinx,Banking,Burton).

After tagging, I run an indexing code and create a list of files with the tags. The produced output was like following :

< ?xml version="1.0" encoding="UTF-8"?>

< Files>

< File Name="imageName3.png">

< Keyword>Banking< /Keyword>
< Keyword>Sports< /Keyword>

< /File>

< File Name="imageName2.png">

< Keyword>Banking< /Keyword>
< Keyword>AssetManagement< /Keyword>
< Keyword>Sports< /Keyword>

< /File>

< File Name="imageName1.png">

< Keyword>Banking< /Keyword>
< Keyword>AssetManagement< /Keyword>
< Keyword>Sports< /Keyword>

< /File>
< /Files>

Now whenever user will enter a keyword in my search panel, I will just load the items from XML in the Panel and will display the images in a different thread.

HTH,
Vikas Bhandari

Use WPF User control to display in a Task Pane

2009-06-24T06:30:00.000-07:00

Hi,

Use the following code in your Ribbon OnAction method to display a WPF User control in an office task pane:

Dim pane As WPFUserControlName= Nothing

If pane Is Nothing Then
Dim uControl As New UserControl()
Dim host As ElementHost = New ElementHost()

pane = New WPFUserControlName()

host.Child = pane
host.Dock = DockStyle.Left
host.Width = 200

uControl.Controls.Add(host)

taskPane = Globals.ThisAddIn.CustomTaskPanes.Add(uControl, "Biographies")
taskPane.Visible = True
Else
taskPane.Visible = True
End If

Dot Net Color Codes

2009-06-23T07:38:00.000-07:00

Hi All,

Please findbelow the dot net color codes. Source :Click here to go to Source Site

AliceBlue	Hex Code:#F0F8FF
AntiqueWhite	Hex Code:#FAEBD7
Aqua	Hex Code: #00FFFF
Aquamarine	Hex Code: #7FFFD4
Azure	Hex Code: #F0FFFF
Beige	Hex Code: #F5F5DC
Bisque	Hex Code: #FFE4C4
Black	Hex Code: #000000
BlanchedAlmond	Hex Code: #FFEBCD
Blue	Hex Code: #0000FF
BlueViolet	Hex Code: #8A2BE2
Brown	Hex Code: #A52A2A
BurlyWood	Hex Code: #DEB887
CadetBlue	Hex Code: #5F9EA0
Chartreuse	Hex Code: #7FFF00
Chocolate	Hex Code: #D2691E
Coral	Hex Code: #FF7F50
CornflowerBlue	Hex Code: #6495ED
Cornsilk	Hex Code: #FFF8DC
Crimson	Hex Code: #DC143C
Cyan	Hex Code: #00FFFF
DarkBlue	Hex Code:#00008B
DarkCyan	Hex Code: #008B8B
DarkGoldenrod	Hex Code: #B8860B
DarkGray	Hex Code: #A9A9A9
DarkGreen	Hex Code: #006400
DarkKhaki	Hex Code: #BDB76B
DarkMagenta	Hex Code: #8B008B
DarkOliveGreen	Hex Code: #556B2F
DarkOrange	Hex Code: #FF8C00
DarkOrchid	Hex Code: #9932CC
DarkRed	Hex Code: #8B0000
DarkSalmon	Hex Code: #E9967A
DarkSeaGreen	Hex Code: #8FBC8B
DarkSlateBlue	Hex Code: #483D8B
DarkSlateGray	Hex Code: #2F4F4F
DarkTurquoise	Hex Code: #00CED1
DarkViolet	Hex Code: #9400D3
DeepPink	Hex Code: #FF1493
DeepSkyBlue	Hex Code: #00BFFF
DimGray	Hex Code: #696969
DodgerBlue	Hex Code: #1E90FF
Firebrick	Hex Code: #B22222
FloralWhite	Hex Code: #FFFAF0
ForestGreen	Hex Code: #228B22
Fuchsia	Hex Code: #FF00FF
Gainsboro	Hex Code: #DCDCDC
GhostWhite	Hex Code: #F8F8FF
Gold	Hex Code: #FFD700
Goldenrod	Hex Code: #DAA520
Gray	Hex Code: #808080
Green	Hex Code: #008000
GreenYellow	Hex Code: #ADFF2F
Honeydew	Hex Code: #F0FFF0
HotPink	Hex Code: #FF69B4
IndianRed	Hex Code: #CD5C5C
Indigo	Hex Code: #4B0082
Ivory	Hex Code: #FFFFF0
Khaki	Hex Code: #F0E68C
Lavender	Hex Code: #E6E6FA
LavenderBlush	Hex Code: #FFF0F5
LawnGreen	Hex Code: #7CFC00
LemonChiffon	Hex Code: #FFFACD
LightBlue	Hex Code: #ADD8E6
LightCoral	Hex Code: #F08080
LightCyan	Hex Code: #E0FFFF
LightGoldenrodYellow	Hex Code: #FAFAD2
LightGray	Hex Code: #D3D3D3
LightGreen	Hex Code: #90EE90
LightPink	Hex Code: #FFB6C1
LightSalmon	Hex Code: #FFA07A
LightSeaGreen	Hex Code: #20B2AA
LightSkyBlue	Hex Code: #87CEFA
LightSlateGray	Hex Code: #778899
LightSteelBlue	Hex Code: #B0C4DE
LightYellow	Hex Code: #FFFFE0
Lime	Hex Code: #00FF00
LimeGreen	Hex Code: #32CD32
Linen	Hex Code:#FAF0E6
Magenta	Hex Code: #FF00FF
Maroon	Hex Code: #800000
MediumAquamarine	Hex Code: #66CDAA
MediumBlue	Hex Code: #0000CD
MediumOrchid	Hex Code: #BA55D3
MediumPurple	Hex Code: #9370DB
MediumSeaGreen	Hex Code: #3CB371
MediumSlateBlue	Hex Code: #7B68EE
MediumSpringGreen	Hex Code: #00FA9A
MediumTurquoise	Hex Code: #48D1CC
MediumVioletRed	Hex Code: #C71585
MidnightBlue	Hex Code: #191970
MintCream	Hex Code: #F5FFFA
MistyRose	Hex Code: #FFE4E1
Moccasin	Hex Code: #FFE4B5
NavajoWhite	Hex Code: #FFDEAD
Navy	Hex Code: #000080
OldLace	Hex Code: #FDF5E6
Olive	Hex Code: #808000
OliveDrab	Hex Code: #6B8E23
Orange	Hex Code:#FFA500
OrangeRed	Hex Code: #FF4500
Orchid	Hex Code: #DA70D6
PaleGoldenrod	Hex Code: #EEE8AA
PaleGreen	Hex Code: #98FB98
PaleTurquoise	Hex Code: #AFEEEE
PaleVioletRed	Hex Code: #DB7093
PapayaWhip	Hex Code: #FFEFD5
PeachPuff	Hex Code: #FFDAB9
Peru	Hex Code: #CD853F
Pink	Hex Code: #FFC0CB
Plum	Hex Code: #DDA0DD
PowderBlue	Hex Code: #B0E0E6
Purple	Hex Code: #800080
Red	Hex Code: #FF0000
RosyBrown	Hex Code: #BC8F8F
RoyalBlue	Hex Code: #4169E1
SaddleBrown	Hex Code: #8B4513
Salmon	Hex Code:#FA8072
SandyBrown	Hex Code: #F4A460
SeaGreen	Hex Code: #2E8B57
SeaShell	Hex Code: #FFF5EE
Sienna	Hex Code: #A0522D
Silver	Hex Code: #C0C0C0
SkyBlue	Hex Code: #87CEEB
SlateBlue	Hex Code: #6A5ACD
SlateGray	Hex Code: #708090
Snow	Hex Code:#FFFAFA
SpringGreen	Hex Code: #00FF7F
SteelBlue	Hex Code: #4682B4
Tan	Hex Code: #D2B48C
Teal	Hex Code: #008080
Thistle	Hex Code: #D8BFD8
Tomato	Hex Code: #FF6347
Transparent	Hex Code:#FFFFFF
Turquoise	Hex Code: #40E0D0
Violet	Hex Code: #EE82EE
Wheat	Hex Code: #F5DEB3
White	Hex Code: #FFFFFF
WhiteSmoke	Hex Code:#F5F5F5
Yellow	Hex Code: #FFFF00
YellowGreen	Hex Code: #9ACD32

Thanks,
Vikas Bhandari

WPF Calendar Control

2009-05-22T04:28:00.000-07:00

Hi All,

By default, the WPF doesn't have the calendar control. And the toolkit release, it seemeed a little way techy for me. I wanted a simple calendar control and I really couldn't work out how I can use only a single control in my project. I thought, it would be easier for me to create a control of my own. hehe, well said by some one :

"Less Knowledge, less confusion, and No knowledge, no confusion. "

Haha...So I designed something like following image:

I have used Listview to display the dates and the items, like "Sun", "Mon" and the days are nothing but listview items. As soon as user changes the year or month, the method to reload the dates is fired. Very simple approach and very very basic functionality I have tried to put into. So if you want to download the project, then please click Here

Thanks,
Vikas

Want to Apply a style to Listview/Combobox Groups? Here is the code:

2009-05-19T06:45:00.000-07:00

Hi all,

Have you ever grouped Item in a Combobox? Suppose if I have a list of names, and I want to publish the names grouped with the Initial letters. For example, All names with starting letter as "A" will fall under Group "A" and so on. Like given in the following screenshot :

This is simple. Please download the code from Here

Or check the code which is given below(in images) :

Add the following code to Xaml Window

Using Linq for Filtering File Types etc

2009-05-15T07:13:00.000-07:00

Hi,

If you have a List of Type IO.File and you want to filter this list to contain only valid images. Then follow the following approach:

Define your array of Valid extensions :

Private IMAGES() As String = {".JPG", ".PNG", ".GIF", ".JPEG"}

The following function returns a list with Valid Images extensions as mentioned in the above array :

Public Function GetValidImage(ByVal files As List(Of File)) As List(Of File)
Dim tmpList As List(Of File) = New List(Of File)
Dim result = From s In files Group Join p In IMAGES On s.Extension.ToUpper() Equals p.ToUpper() Into Group From p In Group Select s

For Each file As File In result
tmpList.Add(file)
Next file

Return tmpList

End Function

Let me know if you have any other questions.

HTH,
Vikas

Office 2007 SP2 Release

2009-05-14T03:10:00.000-07:00

Hi All,

Though the release has been done almost a month ago...I missed to include it here in my blog.

They(Microsoft) have fixed a bunch of bugs found in SP1...and I am sure, 2007 is going to be stable very very soon. This is the reason that most of big organisations, who work on Office rigorously, for example Investment banks like GS or Morgan stanley, they have been shifting their documents to 2007 versions. The office 2007 is certainly going to be much much more popular than it is right now. Lots of cool features. I love themes by the way.

So lets get back to SP2. Microsoft found so many bugs in the previous versions, which forced them to issue a very heavy service pack because the number of bugs they have fixed is huge. Around 600 they have posted here in the link :

Microsoft Office 2007 SP2 Bug Fixes.

For me, the best thing is the chart object for PowerPoint and Word is unlocked. Now you can format/edit the chart source data etc from PowerPoint only. Earlier, before SP2, for formatting a chart programmatically, one had to take the chart to excel and paste it back to PowerPoint. A big pain for me. Using OpenXML was also a big big Pain.

So now, if you have to delete a Series, you can just create the chart object and then add/delete a series. Bingo :)

I love SP2, and I am sure, it will definitely help the Office developers like me, who have to work hard with Charts in PP/Word.

HTH :)

Thanks,
Vikas

Automatic Updating chart

2009-05-14T02:54:00.000-07:00

Hi All,

I was reading a forum : Powerpoint Forum where I found a query. And since I have not tried working rigorously with Charts, I thought it will be a worth trying. A guy in the forum asked a question see the question here :

Click me for the Question

Basically he wants to something like following :

He has a Chart in a PowerPoint Slide, and the series will be added/ removed through click of checkbox buttons as given in the following image

And if user unchecks a checkbox, then the series will be removed from the Chart. For example, if I uncheck the 2nd checkbox, the output will be like following:

The 2nd option is deleted.

I did it...though through a tweak, but yes I did it. I am sure that someone may have a better solution and if someone has then please do forward me @ vikasbhandari2@gmail.com

Would like to add again, that since I don't access a file hosting site so I am adding the code here only. Send me a mail if you want a copy of it. :P

Private Sub CheckBox1_Click()
UpdateChart

End Sub

Private Sub CheckBox2_Click()
UpdateChart
End Sub

Private Sub CheckBox3_Click()
UpdateChart
End Sub

Private Sub CheckBox4_Click()
UpdateChart
End Sub

Private Sub CheckBox5_Click()
UpdateChart
End Sub

Sub UpdateChart()
Dim chrt As Chart
Dim chrtShape As Shape
Dim seriesList As seriesCollection
Dim iSeries As Series
Dim chrtWorkbook As Excel.Workbook
Dim seriesSelected(5) As Boolean
Dim wrkbook As Object

seriesSelected(1) = CheckBox1.Value
seriesSelected(2) = CheckBox2.Value
seriesSelected(3) = CheckBox3.Value
seriesSelected(4) = CheckBox4.Value
seriesSelected(5) = CheckBox5.Value

If seriesSelected(1) = False And seriesSelected(2) = False And _
seriesSelected(2) = False And seriesSelected(2) = False And _
seriesSelected(2) = False Then

MsgBox "No checkbox checked. Exiting sub"
Exit Sub
End If

On Error Resume Next
Set chrtShape = ActivePresentation.Slides(1).Shapes("Chart 3")
Set chrt = chrtShape.Chart
chrt.ChartData.Activate
Err.Clear

Dim xValue As String
Dim rowCounter As Integer
Dim addedOnce As Boolean
Dim frstTime As Boolean
Dim brCounter As Integer
Dim prefixY As String
frstTime = True
addedOnce = False

rowCounter = 1
xValue = "$A$1:$A$6"
brCounter = 0
rowCounter = 1
On Error GoTo 0
Set iSeries = chrt.seriesCollection(1)

For i = 1 To 5
rowCounter = rowCounter + 1

If seriesSelected(i) = False Then
brCounter = rowCounter
If addedOnce Then
If frstTime Then
prefixX = xValue
xValue = ""
frstTime = False
Else
prefixX = prefixX & "," & xValue
xValue = ""
End If
Else

End If
Else
addedOnce = True
xValue = "Sheet1!$A$" & brCounter + 1 & ":$B$" & rowCounter
End If

Next i

Dim allRemoved As Boolean

allRemoved = False

While Not allRemoved
If InStr(1, prefixX, ",,") > 0 Then
prefixX = Replace(prefixX, ",,", ",")
Else
allRemoved = True
End If
Wend

prefixX = prefixX & "," & xValue
xValue = Replace(prefixX, ",,", ",")

If Right(xValue, 1) = "," Then
xValue = Mid(xValue, 1, Len(xValue) - 1)
End If

If Left(xValue, 1) = "," Then
xValue = Mid(xValue, 2, Len(xValue) - 1)
End If

chrt.SetSourceData xValue
chrt.ChartData.Workbook.Close
Err.Clear
End Sub

HTH.

Thanks,
Vikas

Auto Complete Textbox for Vb.Net Desktop Applications

2009-05-05T03:21:00.000-07:00

Hi all,

Ever tried the google toolbar? Whenever you type something, the results are displayed related to the type text just below the textbox. Have you ever wondered if you can include the same thing in your project.

Edited on < 10 AM EST, 19th May 2009: >
Click here for the complete project

I tried to achieve the following :

If I enter a file name, then all the file names which has the text entered by me should appear in a pop up just below my textbox. If somebody cannot access the code at the codeplex, or if somebody cannot download, I have added the code right here :

I applied the following trick :

I loaded few paths to a collection. Created a textbox, and a combobox just behind the textbox so that it should not be visible, but its drop down should be. If I enter some name, and if the text is present in the combobbox items, then the combobox dropdown should display all the items which has that text. Ofcourse, the items should be grouped accordingly.

First step, add a class "AutoCompleteEntry". This will be the class which will be bound to the collection as Combobox Items.

Imports System.IO

Public Class AutoCompleteEntry
Public type As String

Public information As FileInfo

Public Sub New(ByVal type As String, ByVal fileInformation As FileInfo)
Me.type = type
information = fileInformation
End Sub

Public Property FileType() As String
Get
Return type
End Get
Set(ByVal value As String)
type = value
End Set
End Property

Public Property FileInformation() As FileInfo
Get
Return information
End Get
Set(ByVal value As FileInfo)
information = value
End Set
End Property

Public ReadOnly Property ParentFolder() As String
Get
Return information.Name
End Get
End Property

Public ReadOnly Property Name() As String
Get
Return information.Name
End Get
End Property

Public ReadOnly Property Size() As String
Get
Return information.Length & " bytes"
End Get
End Property

End Class

2nd Step, create a Xaml File with the Name of SearchControl.Xaml and add the following code there.

< UserControl x:Class="SearchControl"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
>
< UserControl.Resources>
< DataTemplate x:Key="cmbTemplate">
< StackPanel Orientation="Horizontal" Width="250">
< TextBlock Text="{Binding Path=Name}" Width="100" />
< TextBlock Text="{Binding Path=ParentFolder}" Width="70" HorizontalAlignment="Left" Margin="5,0,0,0"/>
< TextBlock Text="{Binding Path=Size}" Width="80" HorizontalAlignment="Left" Margin="5,0,0,0"/>
< /StackPanel>
< /DataTemplate>

< /UserControl.Resources>
< Grid>

< ComboBox Margin="2,28,0,0" Name="cmbAutoComplete"
ItemsSource="{Binding}" ItemTemplate="{StaticResource cmbTemplate}" MaxDropDownHeight="200" Height="24" VerticalAlignment="Top">
< ComboBox.GroupStyle>
< GroupStyle>
< GroupStyle.HeaderTemplate>
< DataTemplate>
< StackPanel>
< Border BorderThickness="1" BorderBrush="Black">
< TextBlock Text="{Binding Items[0].FileType}" Background="AliceBlue"/>
< /Border>
< /StackPanel>
< /DataTemplate>
< /GroupStyle.HeaderTemplate>

< /GroupStyle>
< /ComboBox.GroupStyle>
< /ComboBox>
< TextBox Margin="2,28,0,0" Name="TextBox1" TextChanged="TextBox1_TextChanged" Height="24" VerticalAlignment="Top" />
< Label Margin="22,2,0,0" Name="Label1" Height="23" VerticalAlignment="Top" FontWeight="Bold" Foreground="DarkBlue" HorizontalAlignment="Left" Width="119">Enter Text Criteria< /Label>
< /Grid>
< /UserControl>

3rd Step, copy the following code and paste in the SearchControl.Xaml file.

Imports System.Collections.ObjectModel
Imports System.ComponentModel

''' < summary >
''' This class takes the path of the file which user can search and sees how big file it is.
''' < /summary >
''' < remarks>< /remarks>

Partial Public Class SearchControl

Private _threshold As Integer = 2

Private path As String = "C:\Vikas-Data\" ' set the path here which you want to search

Private items As ObservableCollection(Of AutoCompleteEntry)
Private view As IcollectionView

Public Sub New()

' This call is required by the Windows Form Designer.
InitializeComponent()

' Add any initialization after the InitializeComponent() call.

items = New ObservableCollection(Of AutoCompleteEntry)

LoadPaths(New IO.DirectoryInfo(path))

view = CollectionViewSource.GetDefaultView(items)
view.GroupDescriptions.Add(New PropertyGroupDescription("FileType"))

cmbAutoComplete.DataContext = items

End Sub

Private Sub LoadPaths(ByVal dInfo As IO.DirectoryInfo)
Dim info As IO.DirectoryInfo

For Each info In dInfo.GetDirectories()
LoadPaths(info)
Next info

For Each fInfo As IO.FileInfo In dInfo.GetFiles()
items.Add(New AutoCompleteEntry(dInfo.FullName, fInfo))
Next fInfo

End Sub

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.TextChangedEventArgs)
Dim text As String

text = TextBox1.Text

If (text.Length < _threshold) Then

Else
view.Filter = New Predicate(Of Object)(AddressOf MyPredicate)

If (cmbAutoComplete.HasItems) Then
cmbAutoComplete.IsDropDownOpen = True
End If
End If
End Sub

Private Function MyPredicate(ByVal item As Object) As Boolean
Dim text As String
Dim itemEntry As AutoCompleteEntry

text = TextBox1.Text
itemEntry = TryCast(item, AutoCompleteEntry)

If (itemEntry.Name.IndexOf(text) > -1) Then
Return True
End If

Return False

End Function

End Class

The output is like following screenshot :

I entered "VB" and I had various file names in the collection which contain vb so it displayed file names grouped by the folder names.

Hope this article will help you!

Thanks,
Vikas

Use WPF Themes and styles

2009-04-24T06:57:00.000-07:00

WPF, the future of Window Forms and Programming, has some fantastic flexibility to create excellent styles and apply with an immense ease. Here is an example, we can apply the themes to our WPF UIs, and the theme will take care of styles of the components used in the window. See the following link:

WPF Themes Home. Samantha is great :)

Hope this helps!

Thanks,
Vikas

Ghost Instance Problem in office application

2009-04-06T01:48:00.050-07:00

Hi All,

A number of office programmers face the Ghost Instance issue while developing office applications. The ghost instance is an instance of office application which is not closed properly. In this scenario, you will be able to see the application entry in the Porcess list of task manager but it will not be visible for the user. I will explain the context by using an example of powerpoint Addin(Obviously in dot net).

Q. How does it occur?

Ans. I create a PowerPoint(aka, PP)Addin, and in that PP Addin I try to create an excel(aka, XL) Application object. Now if I do extensive programming using XL objects(like Range, Shapes, Worksheet etc), these InterOP variables stay in Memory and not handled by the CLR. You will need to explicitly release every COM Object by using System.Runtime.InteropServices.Marshal.ReleaseCOMObject(theObjectToBeReleased) method. But, even after releasing the objects few of the instances keep on running in memory. So if the variables are running in the memory, and I try to close the excel, the excel gets closed but its process keeps on running in the task Manager List. When we hit close button(the cross button in TopLeftHand corner) in Excel, the CLR first closes all the AddIns opened, and then closes the excel window. If there is any COM running in the memory the AddIns get removed but the CLR is unable to close the Excel properly. The result is Ghost Instance. In case of Ghost Instance running in the task manager, if we open any Excel File by double clicking the file, it will open the file but it will Not load any Addins. Reason is barely simple, the Addins were already closed by CLR but not the Excel Application Instance. So the file is actually opened in the previous Excel Ghost Instance.

Q. What is the solution?
Ans. One solution to the problem is: Go to task Manager, Process Tab, and kill the Excel.EXE instance.
The other solution is to include the following approach in AddInShutDown event of your code. This work around uses the Process Class.

1. Try to get the list of Processes by GetProcessByName method :
Process[] processes = Process.GetProcessesByName("EXCEL")
2. Run through Each Process in the processes Array and check if process.MainWindowHandle.ToInt64() is equal to 0. If this is 0 then you will need to kill the process right away.
3. The only thing you will need to take care is, to create a time Lag so that all the AddIns should shut down first then you will need to call the above steps.
4. Creating a Time lag means, you can use a separate EXE, which will run the above steps say 5 seconds after the Excel Closing event was fired.

Hope this helps :)

Thanks,
Vikas

Problem in using Information.TypeName Property

2009-04-03T01:22:00.033-07:00

Hi All,

Whenever we debug the code in Dot Net while using InterOP, it is very frequent to notice a proxy object rather than the actual InterOP object. When you try to get the properties of an InterOP Variable, it shows you some proxy object, and not the actual InterOP object. For example, if I declare a variable like following :

Dim xlApp as XL.Application (The excel Application object)
set xlApp = ME.Globals.ThisAddin.Application

Now, in the debug mode, after executing the above line, if I want to see what values are stored in xlApp variable, the dot net editor will show the variable as a Runtime.Proxy object most of the times. The reason is, that normally, the InterOP layer creates a wrapper object to avoid any problems running in non EN-US locales.

Due to this behavior, I faced one problem. I was not able to obtain the Type Name of application' selection object because the selection object was coming as a proxy wrapper. I tried something like this :

Dim selectionType as string

selectionType = Information.TypeName(xlApp.Selection)

The above line was giving a System.Reflection.TargetInvocationException. I had to change my project's setting to ensure that the InterOp variables are not proxy objects. For that, I went to MyProject folder in my solution, opened the AssemblyInfo.vb file, change the attribute < Assembly: ExcelLocale1033(True) > set to < Assembly: ExcelLocale1033(False) > and saved the file. And see the difference, it didn't give me a proxy object :)

See the following thread For the example:
MSDN VSTO Thread Link.

Using WithEvents in Vb.Net

2009-03-30T22:34:00.036-07:00

Hi,

Using withEvents in VB.net is very easy. I faced lot of problem while accessing the events. For example, activating a chart does not fire a selection change event. I had to do something like whenever I select my chart, my custom menu should appear. I couldn't find doing it in C#(please send a mail if you find it), but in VB.Net it is very easy.

So to take a small example, I have taken a scenario, if I select any of the chart in my workbook, it should display a message. So first of all I created a Class ProxyCharts. The purpose of the class is to serve a Wrapper for Charts Object. This is the class where we will bind our chart objects to WithEvent keyword. So as a wrapper, we will have as many Wrappers as the number of charts available in the workbook. The proxy chart is as follows:

Public Class ProxyCharts
    Private WithEvents _Charts As Chart

    Public WriteOnly Property _Chart() As Chart
        Set(ByVal value As Chart)
            _Charts = value
        End Set
    End Property

    Private Sub _Charts_Activate() Handles _Charts.Activate
        MsgBox(_Charts.Name, MsgBoxStyle.Exclamation, "ActivatedChart")
    End Sub
End Class

Now we have to bind every chart in the workbook with the ProxyChart. So I added a WorkbookOpen method in my AddIn, which loops to all the charts in the opened workbook.

Private Sub _App_WorkbookOpen(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook) Handles _App.WorkbookOpen

        Dim chrtCol As Object
        Dim chtCollection As Collection
        Dim chrtObj As ChartObjects

        For Each wkSht As Worksheet In Wb.Worksheets
            chrtCol = wkSht.ChartObjects(Type.Missing)
            chrtObj = TryCast(chrtCol, ChartObjects)

            If Not chrtObj Is Nothing Then
                For Each chrt As ChartObject In chrtObj
                    Dim chh As New ProxyCharts()
                    chh._Chart = chrt.Chart
                Next chrt
            End If
        Next wkSht
    End Sub

As soon as a workbook is opened, the WorkbookOpen method is called by default and binds all the charts in the opened workbook with ProxyChartType. After binding, the above code will display a message as soon as you select any of the pre existing charts in workbook.

Hope this helps.

Thanks,
Vikas

Linq Example for Group Join

2009-03-26T05:23:00.021-07:00

Hi All,

I have just started bringing LINQs to my work, and learning some new things. So I thought I will store it here before I forget it ;)

I had a simple problem, where in I had to loop through a list of files and to get out few Extensions. For example, If I have a list of around 500 files in the drive, I need to get the files with some valid extension, for eg, Images. So for that, I created a files list with the name of fileList and created an array with the valid extensions. I named that array by IMAGES.

Now to get the list of files with valid extensions I used the following code:

Dim result = From s In fileList Group Join p In IMAGES On s.Extension.ToUpper() Equals p.ToUpper() _ Into Group From p In Group Select s

WPF Fish Eye Effect

2009-03-26T00:28:00.006-07:00

Hi,

I had a fantasy for Fish Eye effect, and I always have wondered how I will be able to do it one day. But guess what, it is barely simple. I didnt have to do anything much to do it. I am going to explain here in detail about the concept I followed:

Edited on : < May 20th 5.15 PM > Finally, I have got the way to store my projects with in my office. So please download it from
Here
First of all, in Xaml, I have a listview with the Following Code:

< ListView Name="lvwCharts" ItemsSource="{ Binding }" Margin="0,49,0,0">
< ListView.ItemTemplate>
< DataTemplate>
< Grid Width="250">
< StackPanel>
< Label Background="AliceBlue" HorizontalAlignment="Stretch" BorderBrush="Black" BorderThickness="1"
Content="{Binding Path = Name}" />

< Image Name="imgChart"
Height="{Binding Path = Height}"
Width="{Binding Path = Width}"
Source ="{Binding Path = Path, Converter={StaticResource converter}}"
MouseEnter="imgChart_MouseEnter"
MouseLeave="imgChart_MouseLeave"
MouseLeftButtonDown="imgChart_MouseLeftButtonDown"
HorizontalAlignment="Center"
VerticalAlignment="Center"
MouseWheel="imgChart_MouseWheel"
Margin="0"
/>
< /StackPanel>
< /Grid>
< /DataTemplate>
< /ListView.ItemTemplate>

< /ListView>

For Binding the object with the listview, create a class with the Chart Proxy

Public Class ChartsProxy
Private _name As String
Private _group As String
Private _height As Double = 100
Private _path As String
Private _width As Double = 100

Public Sub New(ByVal name As String, ByVal path As String, ByVal group As String, ByVal height As Double)
_name = name
_group = group
_path = path
End Sub

Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property

Public Property Group() As String
Get
Return _group
End Get
Set(ByVal value As String)
_group = value
End Set
End Property

Public Property Path() As String
Get
Return _path
End Get
Set(ByVal value As String)
_path = value
End Set
End Property

Public Property Height() As Double
Get
Return _height
End Get
Set(ByVal value As Double)
_height = value
End Set
End Property

Public Property Width() As Double
Get
Return _width
End Get
Set(ByVal value As Double)
_width = value
End Set
End Property

End Class

And in your Xaml.vb class add the following two Event handlers

Private Sub imgChart_MouseLeftButtonDown(ByVal sender As System.Object, ByVal e As System.Windows.Input.MouseButtonEventArgs)
Dim img As Image
Dim proxy As ChartsProxy
Dim index As Integer = -1
img = TryCast(sender, Image)

If Not img Is Nothing Then
proxy = GetParent(img)

If Not proxy Is Nothing Then
index = lvwCharts.Items.IndexOf(proxy)
lvwCharts.SelectedIndex = index
End If

End If
lvwCharts.Focus()
lvwCharts.Items.Refresh()
End Sub

Private Sub imgChart_MouseLeave(ByVal sender As System.Object, ByVal e As System.Windows.Input.MouseEventArgs)
Dim img As Image
Dim proxy As ChartsProxy

img = TryCast(sender, Image)

If Not img Is Nothing Then
proxy = GetParent(img)

If Not proxy Is Nothing Then
Dim widthAnimation As DoubleAnimation = New DoubleAnimation()
widthAnimation.From = proxy.Width
widthAnimation.To = proxy.Width - 70
proxy.Width = proxy.Width - 70
widthAnimation.Duration = TimeSpan.FromSeconds(0.3)

Dim heightAnimation As DoubleAnimation = New DoubleAnimation()
heightAnimation.From = proxy.Height
heightAnimation.To = proxy.Height - 50
heightAnimation.Duration = TimeSpan.FromSeconds(0.3)
proxy.Height = proxy.Height - 50
img.BeginAnimation(Image.HeightProperty, heightAnimation)
img.BeginAnimation(Image.WidthProperty, widthAnimation)

End If

End If

'lvwCharts.Items.Refresh()

End Sub

Public Function GetParent(ByVal img As Image) As ChartsProxy
Dim proxy As ChartsProxy = Nothing
Dim prop As DependencyObject
Dim presenter As ContentPresenter
Dim bool As Boolean = True

prop = VisualTreeHelper.GetParent(img)

While bool
presenter = TryCast(prop, ContentPresenter)

If (Not presenter Is Nothing) Then

proxy = TryCast(presenter.Content, ChartsProxy)

If Not proxy Is Nothing Then
bool = False
Else
prop = VisualTreeHelper.GetParent(prop)
End If

Else
prop = VisualTreeHelper.GetParent(prop)
End If

End While

Return proxy
End Function

Edited on : < May 15th 2.13 PM > : As I have posted a very basic version here...if you want a complete code then send a mail at vikasbhandari2@gmail.com. I will send you a complete project.

Word 2003 Style Tracking Issue

2009-03-24T01:13:00.017-07:00

In word 2003 or ealier versions, whenever we format a text on which a style is already applied, a new style is added in the Styles list and it keeps on increasing if we dont delete it. However, we can stop this automatic tracking and the automatic addition of styles will be disabled. For disabling the automatic addition of styles, go to Tools-->Options --> Edit tab and uncheck "Keep Track of Formatting" option. It is shown in the figure below

Thanks,
Vikas

Filter using Predicate (WPF)

2009-03-19T20:44:00.047-07:00

In WPF, XAML has changed the traditional way of creating and validation the form fields. For example, what if we want to display few of the items in a listview? And what if we want to give user an option to view the items with in the listview?

Without WPF, we will create the loop through all the items and will hide/show the items accordingly. But with WPF, we can do it very easily. I will explain in the following example.

Create a class SampleClass with Two Properties, Name(of string type) and Visible(of boolean type)

Create an ObservableCollection of type SampleClass in your Xaml.cs.

Code : ObservableCollection< SampleClass > collection = new ObservableCollection ()

Add few Items :

collection.Add(new SampleClass("Vikas", true));
collection.Add(new SampleClass("Sonia", true));
collection.Add(new SampleClass("Viman", False));
collection.Add(new SampleClass("Krishna", False));

Add the collection to our listview

myListView.DataContext = collection.

Now, for having a smooth access on filtering, sorting etc features, we will need to get the ICollectionView(Click to know more about ICollectionView) object out of out observable collection.

ICollectionView view = CollectionViewSource.GetDefaultView(collection);

now, to apply the filter we can use the following line anywhere in the code where in we want to put a filter on:

view.Filter = delegate(object item)
{
SampleClass sItem = item as SampleClass;
bool retValue = false;

if(sItem!=null)
{
retValue = sItem.Visible;
}

return retValue;
}

We can do the above step by using a new method with the predicate as well.

private bool GetVisibleState(object item)
{
SampleClass sItem = item as SampleClass;
bool retValue = false;

if(sItem!=null)
{
retValue = sItem.Visible;
}

return retValue;
}

And add the filter like following :

view.Filter = new Predicate< object >(GetVisibleState);

The above code will show the items in which the Visible property is set to true.

The code in VB.net is as follows :

Dim view As ICollectionView
Dim collection As ObservableCollection(Of SampleClass) = New ObservableCollection(Of SampleClass)

collection.Add(New SampleClass("Vikas", True))
collection.Add(New SampleClass("Sonia", True))
collection.Add(New SampleClass("Viman", False))
collection.Add(New SampleClass("Krishna", False))

myListView.DataContext = collection

view = CollectionViewSource.GetDefaultView(collection)
view.Filter = New Predicate(Of Object)(AddressOf FilterItems)

Private Function FilterItems(ByVal param As Object) As Boolean

Dim proxy As SampleClass
Dim Name As String
Dim retValue As Boolean

Name = combovalue ' (some arbit value, which you want to use as a filter. Example, the if you want to filter the value if the name is vikas, then say name = "Vikas")

proxy = TryCast(param, SampleClass)
retValue = proxy.Show

Return retValue

End Function

Public Class SampleClass
Private _name As String
Private _show As Boolean

Public Sub New(ByVal name As String, ByVal show As Boolean)
_name = name
_show = show
End Sub

Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property

Public Property Show() As Boolean
Get
Return _show
End Get
Set(ByVal value As Boolean)
_show = value
End Set
End Property
End Class

How to Use Linqs in VB.Net

2009-03-18T22:25:00.002-07:00

With the new versions of Visual Studios, we have got the Power of LINQ(Language INtegrated Query). With the help of LINQ, we can make query on any collection like we do in SQL. What if we want to search if a string is present in an array or not. Let me explain this in an example :

I have a string Array :

Dim validXLExtns() As String = {"XLS", "XLSX", "XLA", "XLAM", "XLSM", "XLTX", "XLT", ""}

Now, I have to check programmatically if "XLS" exists in the array or not. Ideally, you will like to loop through the items in the array. But, now, with the help of LINQs you can do it with the following command :

Dim product = From s In validXLExtns _
Where s = "XLS"

And if product.count is greater than 0, the value actually exists in the array.

Some very good examples of LINQs:

Developer.com
MSDN LINQ Examples

Working with Margins in WPF

2008-10-13T04:11:00.000-07:00

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.

Example:




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

Thanks,
Vikas

Office 2007 Format

2008-08-04T20:17:00.000-07:00

Hi,

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 myExcel.xlsx.zip) 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:
http://blogs.msdn.com/brian_jones/archive/2005/06/20/430892.aspx

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 http://blogs.msdn.com/brian_jones/archive/2005/11/04/integrating-with-business-data-store-custom-xml-in-the-office-xml-formats.aspx.

You can add the CustomXML while using the CustomXMLParts Object (given in the Office Object Model). Here is the link of MSDN http://msdn.microsoft.com/en-us/library/aa433523.aspx

Example:
[Code]
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"

[/Code]

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.

Thanks,
Vikas

Getting the object of Running office Application

2008-06-30T23:21:00.001-07:00

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

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

Setting RGB Colors for Excel 2007 Chart Series

2008-06-20T00:35:00.000-07:00

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

Gallery Items in Ribbon

2008-06-13T07:15:00.000-07:00

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

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" loadImage="LoadImage" >
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="tab1" label="Gallery Demo" keytip="x" >
            <group id="group1" label="Demo Group">
               <gallery id="gallery1" 
                  columns="2" 
                  rows="2" 
                  getEnabled="GetEnabled" 
                  getScreentip="GetScreenTip" 
                  supertip="This is the super tip." 
                  getKeytip="GetKeyTip" 
                  getShowImage="GetShowImage" 
                  getShowLabel="GetShowLabel"
                  getLabel="GetLabel" 
                  getSize="GetSize" 
                  image="internetconnection.bmp" 
                  getItemCount="GetItemCount" 
                  getItemHeight="GetItemHeight" 
                  getItemWidth="GetItemWidth" 
                  getItemImage="GetItemImage" 
                  getItemLabel="GetItemLabel" 
                  getItemScreentip="GetItemScreenTip" 
                  getItemSupertip="GetItemSuperTip"  
                  onAction="galleryOnAction" >
                     <item id="item1" />
                     <item id="item2" />
                     <item id="item3" />
                     <item id="item4" />
                     <button id="button1" getLabel="GetLabel" 
                        onAction="buttonOnAction" 
                        imageMso="HappyFace" />
               </gallery>
            </group>
         </tab>
      </tabs>
   </ribbon>
</customUI>

URL:

http://msdn.microsoft.com/en-us/library/bb736142.aspx

Reading Tags in Powerpoint

2008-06-11T01:10:00.000-07:00

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

Source:

Optional Parameters in C#.net

2008-06-03T02:00:00.000-07:00

Hi,

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:
http://msdn.microsoft.com/hi-in/library/ms178843(en-us).aspx#ValueTypes

Thanks,
Vikas

How Add-ins Work with the 2007 Microsoft Office System

2008-06-03T01:50:00.000-07:00

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

http://msdn.microsoft.com/hi-in/library/bb386298(en-us).aspx

Thanks,
Vikas

IDTExtensilibity2 Procedure to Add Menus

2008-06-01T21:42:00.000-07:00

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.

Thanks,
Vikas

ADO Connection Pooling Fundamentals

2008-05-21T03:26:00.000-07:00

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.

Thanks,
Vikas

Some ADO Fundamentals

2008-04-04T07:39:00.000-07:00

Hi,

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.
adCmdTableDirect	for 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.

Thanks,
Vikas Bhandari

Creating Custom Menus in Excel through VBA

2008-04-04T06:26:00.000-07:00

Source :
http://www.ozgrid.com/VBA/custom-menus.htm



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 = _
     cbMainMenuBar.Controls("Help").Index
     
 '(4)Add a Control to the "Worksheet Menu Bar" before Help.
 'Set a CommandBarControl variable to it
 Set cbcCutomMenu = _
     cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
                  Before:=iHelpMenu)
                  
     '(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

2008-04-04T06:24:00.000-07:00

A very good link to learn about Powerpoint.

http://www.bettersolutions.com/powerpoint/PBZ143/LE824411712.htm

Thanks,
Vikas

Adding an item in Right Click Menu.

2008-04-03T06:34:00.001-07:00


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
    Application.CommandBars("Cell").Controls("RightClickMenu").Delete
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
Application.CommandBars("Cell").Controls("RightClickMenu").Delete
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
red
blue
green
black
End Enum

Sub RedColor()
ColorCell (Color.red)
End Sub
Sub BlueColor()
ColorCell (Color.blue)
End Sub
Sub GreenColor()
ColorCell (Color.green)
End Sub
Sub BlackColor()
ColorCell (Color.black)
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)
Case Color.red
rng.Interior.Color = ColorConstants.vbRed
Case Color.black
rng.Interior.Color = ColorConstants.vbBlack
Case Color.blue
rng.Interior.Color = ColorConstants.vbBlue
Case Color.green
rng.Interior.Color = ColorConstants.vbGreen
End Select
End Select

End Sub

Creating Excel Menu from Dot net 2003

2008-04-02T04:57:00.000-07:00



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");
  excel.Visible=true;
  sheet = (Worksheet)book.Sheets.get_Item(1);
  Console.WriteLine("The file name is {0}",sheet.Name);
  Console.ReadLine();
 }

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

   }
   catch 
   {
       
   }
  }

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

  }
 }
}

Create and register ADDin to Use in Excel

2008-04-02T04:31:00.001-07:00

namespace NAddIn
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public Functions()
{
}

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

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}
}
}

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

Reading and writing in a TextFile from VBA

2008-03-26T05:41:00.000-07:00

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
Wend
End Sub

Import data from a text file (ADO)

2008-03-25T03:49:00.001-07:00

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 & ";" & _
"Extensions=asc,csv,tab,txt;"
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
cn.Close
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
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

The procedure can be used like this:

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

Code to View Pivot Data within a date range

2008-03-24T06:45:00.000-07:00

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

Items	Date1	Date2	Date3	Date4
Item 1	Data	Data	Data	Data
Item 2	Data	Data	Data	Data
Item 3	Data	Data	Data	Data
Item 4	Data	Data	Data	Data

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
    Next
    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
    Next
    
    
************************************************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
            Next
        Next
        
        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.

Thanks,
Vikas

Programming with Charts In Excel

2007-11-05T00:23:00.000-08:00

Chart programming is a little difficult in Excel because Excel itself has some procedures to update charts automatically after any event is fired. For example, After deleting a series, the axes values are automatically changed. Like this, there are lots of cases when you need to trace the Excel Behavior in order to write an efficient Chart Code. I have got some tips, which I learnt which doing the Chart Programming in my recent project:

1. If you dont want to plot 0's in your chart, then use NA() for Blank cells. It will remove the 0's in the chart. If you use "" or blank cells, it is considered as 0 in the charts.

2. If you have to assign xRange at run time, you will always have to assign it to a series. Assigning xRange goes like this:

chartobject.seriescollection(1).XValues = Some Range

SeriesCollection(1) refers to the First series of the chart. You need to assign the xaxis range to any of the active series in the chart. By Active series, I mean, that the assigned Series should have atleast a non error value. Incase if series1 doesnt have any value(either numeric/blank) in any of its ranged cells, then VBA will not be able to find where it needs to plot the xrange and will throw an error.

3. If you are assigning the values to a series on RunTime, and the first cell contains Error, VBA throws back an error. So while assigning some values to a chart on RunTime, u have to make sure that the First Element in the series is not any Error Refrences.

Hope, that was helpful!

Thanks,
Vikas

Sort by Colors

2007-08-03T23:25:00.001-07:00

In excel, sorting by colors is not a difficult situation. Anyone with a little knowledge of VBA can write this simple code. Just rush to the following link to read more:

http://www.cpearson.com/excel/SortByColor.htm

Thanks,
Vikas

Getting values from Databases quickly.

2007-08-03T23:14:00.000-07:00

When you request some values from databases in excel, it probably takes few secs. For example, when you get values from Microsoft query section, it takes lesser time as compared to the procedure when you create a connection and enters the values record by record. Its always a better option to fetch the complete table and insert the whole table in excel. If your database is huge, this technique works really well. See the following code:


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
         "ODBC;DSN=Your Oracle DSN;DBQ=Your database Service;uid=your account;pwd=your password;DriverId=25;FIL=Oracle;MaxBufferSize=2048;PageTimeout=5;" _
       )), Destination:=Range("$A$8")).QueryTable
        .CommandText = qur <-----(write your query like select * from something)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

The above code fetches the data from oracle and enters in the active excel sheet. It will return the table object based on the query you will give in the code, and insert the results in tabular format in no time. If you compare the process with the procedure of connecting and reading the rows one by one through recordset....its going to take 20 times longer than it should usually take!

Thanks,
Vikas

Macro for restricting users to overwrite the value in a sheet

2007-06-29T23:56:00.001-07:00

Hi,

Please download the required macro from here.. Here, if someone will overwrite the value in Sheet1, the macro will ask for password(which is set to vikas now).If you dont enter a correct password, you will not be able to change the cells value.

URL for File : Click Here

Thanks,
Vikas

Implementing Arrays in Excel

2006-12-22T03:13:00.000-08:00

One of the key things in the excel is to compare/find the values in an array....when we specify a range in any function, the excel needs to be intimated that whether we are pointing at a single cell or an array. For example, I am checking that if a single cell is having 0 or not :

If(B1:b10=0,True,False)...Suppose if I have entered the farmulae in C5....it will check if the value in B5 is 0 or not...but if we want this farmulae to check if every cell in the range b1:b10 is 0 or not....we will have to tell excel to use the range as array..for that after putting the farmulae, press ctrl+Shift+Enter rather than the traditional Enter...

Want to know more about Excel. Here are some links given by my frnd OldChippy from Excelforum.com(The Maximum Knowledge Forum)

http://www.ozgrid.com/Excel/arrays.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html
http://www.personal-computer-tutor.c...ayformulas.htm
http://www.excel-vba.com/excel-27-array-formulas.htm

Thanks,
Vikas B

Very useful information for Sum Product Function here

2006-12-21T05:58:00.000-08:00

Do you think you are an excel expert??? May be, but if you are then please try to go to the following URL and check if you really know about this function:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Wonderful Examples of SumProducts here too :

http://homepage.ntlworld.com/ken.wright/ExSumProduct1.html

Vikas B

Free Excel Ebook Link

2006-12-21T05:56:00.000-08:00

Please goto the following URL to get your free copy of excel. You will need to create an account to be eligible for downloading this book:

http://www.exceltip.com/LearnExcel.pdf

Regards,
Vikas B