Click here to Skip to main content
Click here to Skip to main content

Tagged as

Tip: Perform a Merge and Center With Excel Automation

, 19 Dec 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
You know that little "Merge and Center" button in Microsoft Excel? Here's how to 'click' it in C# with Excel Interop (and this carries over to VB etc, too).
Say you have an Excel.Range variable called, e.g., MergeRange that represents the range of cells you would've otherwise highlighted had you been working directly with an open instance of Excel.
 
Now suppose we want to Merge and Center the text that is in the range. How do we do this? By the below:
 
public void MergeAndCenter(Excel.Range MergeRange) {
            MergeRange.Select();
 
            MergeRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            MergeRange.VerticalAlignment = XlVAlign.xlVAlignBottom;
            MergeRange.WrapText = false;
            MergeRange.Orientation = 0;
            MergeRange.AddIndent = false;
            MergeRange.IndentLevel = 0;
            MergeRange.ShrinkToFit = false;
            MergeRange.ReadingOrder = (int)(Constants.xlContext);
            MergeRange.MergeCells = false;
 
            MergeRange.Merge(System.Type.Missing);
}
 
Ta-da! Works with the Microsoft Excel v12.0 and above object libraries.
 
You know how I did this? I opened up an instance of Excel on my own, typed in some text and performed a merge and center -- all while recording a Macro.
 
Then I just did a 'View Code' (on the Developer tab -- you can show it using the Ribbon Orb > Excel Options and then somewhere there's a check box that says "Show the Developer toolbar" -- and there's also Record Macro/Stop Macro buttons there) and then opened up the VBA module corresponding to the Macro.
 
I then translated the VBA code to C# equivalents.
 
Bonus tip: Now you don't have to Google search for how to do something with Excel Interop -- just record a macro, do it by hand, stop the macro, view its code, translate to your interop language and voila!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Brian C Hart
Software Developer (Senior) Corrugated Technologies, Inc.
United States United States
From Fridley, Minnesota and I like computer programming! When I got started, I was working mostly with Windows GUI programming in C/C++. Then later on I worked with COM/DCOM for a school internship. I used COM/DCOM to write an ad hoc cluster server and job-running environment for a cluster of 24 Windows-based high-end visualization workstations. I moved on to C# and have been working in C# and Windows Forms ever since. I have yet to embrace Silverlight Smile | :)
Follow on   Twitter

Comments and Discussions

 
GeneralReason for my vote of 5 Thank you. Someone was asking how to... PinsubeditorWalt Fair, Jr.13-Dec-10 14:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 19 Dec 2010
Article Copyright 2010 by Brian C Hart
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid