Possible to define Excel's print area when exporting Stata output via putexcel or export excel - otherwise, workarounds?

[Timothy Slade]

I'm batch exporting output (roughly 12,000 teacher names and sexes, broken up by District [in 11 different spreadsheets] and Zone [a sub-district geographical grouping, in 134 tabs, grouped within a District spreadsheet as appropriate]) from Stata into pre-formatted Excel spreadsheets. Our admin staff will print these pre-populated Excel spreadsheets out and use them to track attendance and other things during our mass training cascades.

I am looking to automate this process as much as possible. Because the maximum number of teachers within a given zone varies widely, and I don't want to be manually adding/removing the shells/templates, I've created far more spaces to be filled than are strictly necessary.

What I would like is for my staff to be able to simply print all of the sheets without needing to go in and manually re-set the sheet's "print area" to exclude blank/unused shells.

I know how I would calculate the parameters of the print area, should this be possible. But I don't know how to define it, if indeed it is possible.

Can anyone provide guidance as to whether it's possible to do, and if so how one might approach it?

[Steve Samuels]

As 22 names will go on one sheet, you can create "extended zones" with 22 names each and export each to different tab. To shorten tab names, II suggest that you start with a "zone" variable with string values like "102" or "z102" . The code (not tested) will be something like.

bys zone: gen ct22 = ceil(_n/22)
bys zone:  gen str10 zoneX = zone + "_" + string(ct22)
compress

That will give values for zoneX like "z102_1" "z102_2". Then export excel, with the cell() option to place the names and sheet(zoneX). You still have to get headers and footers onto all the sheets

Afterthought: This is probably unworkable, because with 12,000 names you would have 12,000/22 = 545 tabs. However you might be able to use the same idea to print out the minimum number of pages (header, names, footer, page break) per zone sheet.

[Timothy Slade]

Thanks for weighing in, Steve - I eventually found a (pretty kludgy) workaround that depended on taking advantage of Excel's treatment of ranges named "Print_Area". I defined "Print_Area" for each zone (worksheet) as [CODE]=INDIRECT($B$1)[CODE], and then just used Stata to dump the boundaries of the range into B1. (I've included the code below in case anyone else may find it useful.)

foreach d of local districts {
    foreach z of local district_`d'_zones {
    use "$trainingprep_workspace\Simplified Tchr Roster - District `d'.dta" if zone==`z', clear
            count
            local tchrs = r(N)
            local page_ct = int(`tchrs'/22) + 1
            local obs_first = 1
            local obs_last = 22
            local page = 1
            local rows "12 52 92 132 172 212 252 292 332 372 412 452 492 532 572"
            local district_name : label lbl_district `d'
                di "The current District is `district_name'"
            local zone_name : label lbl_zone `z'
                di "The current Zone is `zone_name'"
                
            putexcel set "$trainingprep_roster\EGRA Training Allowance Forms - District `d'.xlsx", modify keepcellformat
            putexcel K2=("District: `district_name'"), sheet(zone_`z')
            putexcel K3=("Zone: `zone_name'"), sheet(zone_`z')
            
            sort school_name /*std_taught*/ tchr_name_last tchr_name_first
            keep tchr_name_first tchr_name_last female
            foreach r of local rows {
                    preserve
                    keep in `obs_first'/`obs_last'
                    local target = `"B`r'"'
                    di "Placing teachers # `obs_first' through `obs_last' in the sheet for zone `z', starting with cell `target'."
                    export excel using "$trainingprep_roster\EGRA Training Allowance Forms - District `d'.xlsx", cell(`"`target'"') sheet(zone_`z', modify)
                    restore
                    local obs_first = `obs_last' + 1
                    local page_cell = 39 + ((`page'- 1) * 40)
                    putexcel K`page_cell'=("D: `district_name' / Z: `zone_name' - p. `page' of `page_ct'")
                    if `obs_last' + 22 <= `tchrs' {
                        local obs_last = `obs_last' + 22
                        }
                        else local obs_last = `tchrs'
                    di "Now moving to teachers `obs_first' through `obs_last'."
                    local page = `page' + 1
                    if `obs_last' <= `obs_first' {
                        continue, break
                    }
            }
                    local bottom_right = 39 + ((`page_ct'- 1) * 40)
                    local district_cell = 2 + ((`page_ct'- 1) * 40)
                    local zone_cell = 3 + ((`page_ct'- 1) * 40)
                    di as error "The print area will be A1:K`bottom_right'"
                    
                    putexcel B1=("A1:K`bottom_right'"), sheet(zone_`z')
                    putexcel K`district_cell'=("District: `district_name'"), sheet(zone_`z')
                    putexcel K`zone_cell'=("Zone: `zone_name'"), sheet(zone_`z')
    }    
}

The only problem with the approach was that it required me to manually define Print_Area for each of the 134 zones, and that was a pain. (Probably an easier way to do it if you speak VBA, but I don't.)

[Steve Samuels]

Great! Thanks for sharing the code. I'm sure that others will find it useful.