Search code examples
excelvb.netbordercell

VB.Net: All Borders Around Excel Cells

I'm attempting to put borders around every cell in a given range. I haven't been able to find a way to do this through research. The only thing i've been able to implement to create a border is the following code:

protoWorksheet.Range("A1:K1").BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)

Visual of what I want: enter image description here

Solution

  • This code will place a border around each cell in a given contiguous range, in this case for Sheet1, range is C8:D8.

    Option Strict On
Option Infer Off   
Imports System.IO
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop.Excel    
Public Module RangeBorderSample
    Public Sub OpenExcelSimple()
        Dim proceed As Boolean = False
        Dim xlApp As Application = Nothing
        Dim xlWorkBooks As Workbooks = Nothing
        Dim xlWorkBook As Workbook = Nothing
        Dim xlWorkSheet As Worksheet = Nothing
        Dim xlWorkSheets As Sheets = Nothing
        Dim xlCells As Range = Nothing

        xlApp = New Application With {.DisplayAlerts = False, .Visible = True}
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(Path.Combine(
            AppDomain.CurrentDomain.BaseDirectory, "Excel1.xlsx"))

        xlWorkSheets = xlWorkBook.Sheets
        For index As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(index), Worksheet)
            If xlWorkSheet.Name = "Sheet1" Then
                proceed = True
                Exit For
            End If
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        Next
        If proceed Then
            xlCells = xlWorkSheet.Range("C6:D8")
            PlaceBordersOnEachCell(xlCells)
        End If
    End Sub
    Private Sub PlaceBordersOnEachCell(pRange As Range)
        pRange.Cells.Borders.LineStyle = XlLineStyle.xlContinuous
    End Sub
End Module