Set Border to excel sheets without external packages || Without Excel scope || Closed XML

Hello Learner’s,

For past few days I have seen many post asking help of how to set border to the excel sheet.
Many solutions and many suggestions are given but most of them consist either installing external packages or using shortcut keys which is not a good programming practice.

Today I am creating a tutorial/guide on how to set border on preferred excel sheet without shortcut keys and without installing any external activities.

Step 1 : Drag an assign activity and create a string variable as shown in the image below. In value pass the path of your Excel file.
image

Step 2: Drag an invoke code activity and paste the below code.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(FilePath)
excel.Visible=False
ws=CType(wb.Sheets(“SheetName”),Microsoft.Office.Interop.Excel.Worksheet)
ws.Range(“A1:Q123”).Cells.Borders.LineStyle = XlLineStyle.xlContinuous


wb.Save
wb.Close

image

Step 3 : Create an argument ‘FilePath’ and pass the variable mentioned in the step one

Step 4: Import NameScape mentioned in below image.

And thats it it will setborder to your mentioned sheet.

Apart from border you can do other customization like color cell,color border,pivot table,bold header
Eg Apply bold font to header

ws.Range(“A1:Q1”).Font.Bold = True

Hopefully this post reaches out many people who are stuck and this post helps them

6 Likes

Nice guide, I moved it to our FAQ section :slight_smile:

3 Likes