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