Putexcel: Conditional Formatting

[Ingo Brooks]

Dear all,

I need to generate an Excel report containing summary statistics as well as a subset of observations from a Stata dataset.

In the first step, I make use of the

export excel

command, to write a subset of observations to Excel. In the next step, I would then like to highlight (i.e set the cell background to orange) the the largest and smallest 5 values in each numeric variable that I exported to Excel in the first step. Can I somehow achieve this with some sort of a

putexcel ..., fpattern(solid, lightgray)

type of a command, or is there another feasible way (from within Stata) to set the cell background color in an Excel worksheet conditional on the value of the cell?

Thanks,
Ingo

[Leonardo Guizzetti]

The direct question, using Excel's conditional formatting cannot be achieved using putexcel (nor with Mata's xl() class). What I have done in the past is to manually create a VBscript text file that I enter into the final Excel file using the developer tools (Alt+F11 on Windows). I suppose you can also run this script from the command line, but this wasn't an option in my case.

An alternative that is done without VBscript is to programmatically determine which cells you enter get which formatting, and apply them one by one. This is often more tedious than the above approach.

[alejoforero]

I have done something like that in the past. You have to create a loop cell by cell in stata to select the conditions for formatting. Its ugly but might work.

sort variable
g smallest5=_n<=5

forvalues obs=1/N{
    cap assert smallest5=1 if _n==`obs'
    if !_rc {
         loc row=`obs'+1
         putexcel A`row', fpattern(solid, lightgray)
    }
}

If you have several variables you would have to add another loop.

[Leonardo Guizzetti]

Bjarte Aagnes shows how to create and execute a VBS script from within Stata in this thread. Note that you must have Excel installed on the same system as your Stata installation for this to work.

[Ingo Brooks]

Thanks so much for these suggestions, Leonardo and Alejo. - I very much like the VBS approach. This eases the final formatting of the Excel report significantly and works like a charm. Thanks!