Hi Guys.I need to add all border to the excel cells.Can anyone help me with this.Thanks in advance
The possible solutions(to be used based on business scenario) that i could think of are-
- 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.
- 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
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.
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.
control +b is not working for bold text.Any other way is there?
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”)
ws.Range(“A1:C1”).Cells.Borders.LineStyle = XlLineStyle.xlContinuous
ws.Range(“A1:C1”).Font.Bold = True
This activity sets border to cell or cell range.
Hi @Ilayaraja I am trying your code but tell me → “XlLineStyle” undeclared. How can i do that?
Thanks in advance
This is excellent , Thanks for these short cuts , where can I find more of these?
@MariaJosephina - Are you good with the issue or required any assistance?
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
@u322805 - To cover the complete content, Read the excel and get the count of the
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)
Thank you so much for the reply
Is there anyway in dot net
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”
@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?
@u322805 - For specific range you can use like
WS.Range(“A10:C30”).Cells.Borders.LineStyle = XlLineStyle.xlContinuous
@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
I;m trying to use that line of code but still throwing error about ‘undeclared’, any thoughts?