Format data as a table in excel

[at392]

Hi, I have an automation to create a queue report at the end of each days automation run, which generates the data into an excel format.

I want to find out how to automate formating the data in the excel file to be in a table format / have bold headers so that it looks a bit more presentable than the raw data. I can't find any instructions for this anywhere - can anyone help with this?

Thanks

[Mukeshh_k]

Hi Alex Traynor,

I will be attaching a MS Excel Customization VBO shortly - where you can Bold/Unbold the Header based on range, Colour Cell based on Range, Set Row Height based on range, Create Borders based on range n etc - it might help you to customise/design the excel report in presentable form.

Regards

Mukesh

[johan.m]

We have an action to put in the MS Excel VBO to do it

[Mukeshh_k · Accepted solution]

Hi Alex - You can additionally create a custom action in one of your extended or MS Excel VBO to Set Border, Set Bold  basis on Range:

Add Inputs and Add a code stage in one of your newly created Action in your custom MS Excel VBO:

Add below code in the code Stage (Highlighted in Red are two actions for Bold and Border, you can remove( range.Borders.LineStyle = 1 ) if you only require Bold action, additionally if you want to colour your selected range Add one Additional line or copy paste the same code in new action and just replace the highlighted in Red lines in code by ( range.Interior.Color=RGB(R,G,B) ) and you would have to provide additional 3 inputs for R,G,B for colouring  :

Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)

range.Font.Bold() = True
range.Borders.LineStyle = 1

range.Interior.Color=RGB(R,G,B)

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

Make sure you have all the Libraries and Namespaces listed in the Initialise page: