topic Format data as a table in excel in Product Forum

Format data as a table in excel

at392 — Thu, 23 Mar 2023 11:25:32 GMT

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

Re: Format data as a table in excel

Mukeshh_k — Thu, 23 Mar 2023 11:55:38 GMT

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

Re: Format data as a table in excel

johan.m — Thu, 23 Mar 2023 15:12:11 GMT

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

Re: Format data as a table in excel

Mukeshh_k — Thu, 23 Mar 2023 16:37:05 GMT

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: