Visual Basic - How to Center a Column in Excel and other Formatting Options

Question

How do I center a column in Excel using Visual Basic (Visual Studio 2015)? I am using Microsoft.Office.Interop.Excel

Here is the code segment I am using to do this but not able to:

Excel3 = CreateObject(“Excel.Application”) 
Excel3.WorkBooks.Add() 

Sheet3 = Excel3.ActiveSheet

Sheet3.Columns("A:A").Select
    With Sheet3.Selection
        .HorizontalAlignment = Sheet3.Columns("A:A").xlCenter
        .VerticalAlignment = Sheet3.Columns("A:A").xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = Sheet3.Columns("A:A").xlContext
        .MergeCells = False
    End With

I am using Visual Basic (Visual Studio 2015). The error message states:

"Unhandled Exception: System.MissingMemberException: Public member 'Selection' on type 'Worksheet' not found at Microsoft.VisualBasic.CompilerService.LateBindign.LateGet(Object o, Type objType, String name, Object[] args, String[] paramames, Boolean[] CopyBack) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)

Another variation gives me error: Selection on Type Range not found.

Sheet3.Columns("A").Select
    With Sheet3.Columns.Selection
        .HorizontalAlignment = Sheet3.Columns("A").xlCenter
        .VerticalAlignment = Sheet3.Columns("A").xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = Sheet3.Columns("A").xlContext
        .MergeCells = False
    End With

Sohel Sohel · Accepted Answer · 2017-01-31T22:10:47

I found a solution of this after reading a post from this link. Here are the steps I've taken.

Changed from: Imports Microsoft.Office.Interop.Excel

to: Imports Excel = Microsoft.Office.Interop.Excel

The above change allows me accessing the Constants objects that has xlCenter I can use.

And then the relevant code changed to:

Sheet3.Columns("A:S").EntireColumn.AutoFit
Sheet3.Range("A:S").VerticalAlignment = Excel.Constants.xlCenter
Sheet3.Range("A:S").WrapText = True
Sheet3.Range("A:A").HorizontalAlignment = Excel.Constants.xlCenter

Visual Basic - How to Center a Column in Excel and other Formatting Options

1 Answers