topic Expanding a ready-made in Product Forum

Excel - Setting borders

AlexHowarth — Wed, 22 Jan 2014 17:22:00 GMT

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

Don't know if these are of

SamanthaShaw — Thu, 30 Jan 2014 20:27:00 GMT

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

You could try adding a new

SamanthaShaw — Thu, 30 Jan 2014 20:58:00 GMT

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.

Excel - Setting borders

AlexHowarth — Wed, 12 Feb 2014 21:07:00 GMT

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.

Personally I've just created

grant_warwick — Wed, 03 Sep 2014 22:44:00 GMT

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

I agree with Grant - using a

Denis__Dennehy — Thu, 04 Sep 2014 13:16:00 GMT

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.

Expanding a ready-made

John__Carter — Thu, 04 Sep 2014 13:56:00 GMT

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

Hi,

Anonymous — Mon, 11 Jul 2016 13:52:00 GMT

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.