Excel - Setting borders

[AlexHowarth]

Does anybody have any code which can apply borders to cells (either multiple or single) in excel? We have a process which creates spreadsheets for a 3rd party but the process owner wants them to look pretty! I can't just add borders to the template as the spreadsheet we create can have between 1 and 2000 lines. The spreadsheets need to be printed and mailed and if we set borders down to line 2000 on the template, we will print off lots of blank pages. Alex

[SamanthaShaw]

Don't know if these are of use? Looks like you can do this using VBA:- http://www.excel-user.com/2010/02/vba-put-borders-around-all-used-cells http://stackoverflow.com/questions/13121425/border-around-each-cell-in- Lots of other results as well when I searched on ""excel set cell borders vba

[SamanthaShaw]

You could try adding a new action to the MS Excel VBO to do this. If you take a look in there, and examine the code stage for the Format Cell action, you can see that it is similar to the suggestion on the StackOverflow link, which suggests something like:- Range(""C11"").Borders(xlEdgeRight).LineStyle = xlContinuous Range(""A15:D15"").Borders(xlEdgeBottom).LineStyle = xlContinuous (as an example). So you could try adding a new one called Set Border which takes inputs of the cell range start and end, and then use code similar to the above.

[AlexHowarth]

Thanks Sam, I did try this at the time but couldn't get it to work. I can't remember the exact error I was getting but when I googled it, it was something about cell borders overlapping. Was hoping someone may have had something built already! We've got a workaround now but I'll re-visit it when I've got a little more time.

[grant_warwick]

Personally I've just created templates and pushed the data into the already formatted template.

[Denis__Dennehy]

I agree with Grant - using a template is the way to go. The standard Blue Prism reports use templates with the formatting already done. It then copies the data row in the template down as many times as it needs for the data it wants to popultate - so that the report fits the data exactly.

[John__Carter]

Expanding a ready-made template would be easier but you can do it with code such as this... myBorder = myRange.Borders With myBorder(Border_Position) .LineStyle = Border_Style .Weight = Border_Weight .Color = Border_Colour End With ...where Border_Position, Border_Style, Border_Weight and Border_Colour are all numerical inputs equivalent to the corresponding VBA enumerations. For example, for Border_Position see XlBordersIndex here: http://msdn.microsoft.com/en-us/library/office/ff835915(v=office.15).as

[Anonymous]

Hi, Does anyone have a capable VBO template to set borders in Excel file? What code should I use in code properties? Thanks P.K.