How to add all border to the Excel cells

Help
1

Hi Guys.I need to add all border to the excel cells.Can anyone help me with this.Thanks in advance

2

@sandhyak9

The possible solutions(to be used based on business scenario) that i could think of are-

  1. Using Template file
  • Create an excel file as template and add all the formatting you want.
  • Add steps in BOT to create a new excel file in the required location as a copy of the template file specified above.
  • Use write range activity to enter data into the file. Write range will write only the data into the files and do not change the original format.
  1. Formatting Excel
  • Open excel file.
  • Do ctrl+a.
  • Got to Home tab and then click the Border > All Borders . See screen shot below
  • Save the file and close
    Capture

Hope this is helpful!
If this resolves your issue, please mark it as solution so that people facing similar issues can directly navigated to answer.

3

Hi @sandhyak9,

First select the range of cells using “Select Range” activity or using “Ctrl+A” hotkey. Then you can add all border by sending the hotkey “Alt+Shift+b+h+a” inside an excel application scope.
allborder

Warm regards,
Nimin

3 Likes
4

control +b is not working for bold text.Any other way is there?

5

For Border write the below code in the Invoke Code Acitivity

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(“File Path”)
excel.Visible=False
ws=CType(wb.Sheets(“SheetName”),Microsoft.Office.Interop.Excel.Worksheet)
ws.Range(“A1:C1”).Cells.Borders.LineStyle = XlLineStyle.xlContinuous

Bold

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

wb.Save
wb.Close

5 Likes
6

Hi,
This activity sets border to cell or cell range.

Regards
Balamurugan.S

3 Likes
7

Hi @Ilayaraja I am trying your code but tell me → “XlLineStyle” undeclared. How can i do that?

Thanks in advance

8

@salvatore.quimi

Try Microsoft.Office.Interop.Excel.XlLineStyle

Regards
Ilayaraja

9

This is excellent , Thanks for these short cuts , where can I find more of these?

11

@MariaJosephina - Are you good with the issue or required any assistance?

12

Hi @Ilayaraja how to make the range selection dynamic in this
Say like it will start from particular cell and cover until which the content is available

13

@u322805 - To cover the complete content, Read the excel and get the count of the
excel.

Example - If excel contains 10 rows from A1 to C10,then the start range would be A1 and end range like C+datable.rows.count(It gives you C10 dynamically)

1 Like
14

Hi @Ilayaraja
Thank you so much for the reply
Is there anyway in dot net
I used
WS.UsedRange.Borders.LineStyle = XlLineStyle.xlContinuous
and this works for the entire worksheet, but I want to know how to make it start from say “A39”

15

@u322805 - Any range if you want to provide dynamically first you should read the excel and get the already existing data count. From that only we have to specify the ranges dynamically. Hope I have understood your question correctly?

16

@u322805 - For specific range you can use like

WS.Range(“A10:C30”).Cells.Borders.LineStyle = XlLineStyle.xlContinuous

1 Like
17

@Ilayaraja Thank you so much for the reply
As you said, I used below the code in Invoke Code Activity in which I passed rows count as Argument

Rowsdt_FIltOrg = datatable.rows.count.ToString

WS.Range(“A39:V”+ Rowsdt_FiltSorg).Borders.LineStyle = XlLineStyle.xlContinuous
WS.Range(“A39:V”+ Rowsdt_FiltSorg).Rows.AutoFit

18

Hi,

I;m trying to use that line of code but still throwing error about ‘undeclared’, any thoughts?

Thank you!