Looping through excel worksheets using putexcel

[Daniel Zaas]

Good morning,
I am trying to create a do file that outputs descriptive statistics and comparisons in a way that can be easily adapted for other projects. Everything works perfectly except that the headings do not show up the first time I run the do file. However, they do show up when I run it a second time. I imagine it has something to do with this line but I am at a loss for why the rest of the statistics export on the first run but not the headings.

  
putexcel set "$title", sheet("`lab`sheets''", replace) modify

Here is the section of code in question:

forval sheets = 1/`sheets' {
    
    putexcel set "$title", sheet("`lab`sheets''", replace) modify 
    
    putexcel A1=("`a1'") B1=("`b1'") C1=("`c1'") D1=("`d1'") E1=("`e1'") ///
             F1=("`f1'") G1=("`g1'") H1=("`h1'") I1=("`i1'") ///
             ,vcenter hcenter txtwrap 
    
    foreach var in `sheet`sheets'' { //loops through each indicator
        forvalues x = 0/1 {      // loops through baseline then midline
            sum `var' if `compare' ==`x' , detail  //sums each variable at baseline/midline
                scalar `var'_mean`x' = r(mean)        //saves mean
                scalar `var'_sd`x' = r(sd)           //saves SD
                local `var'_label: var label `var' //saves var label
                scalar `var'_obs`x'= r(N)           //saves number of obs    
        }
        scalar `var'_ndiff = string((`var'_mean1 - `var'_mean0)/(sqrt(((`var'_sd1)^2 + (`var'_sd0)^2)/2)), "%10.3fc") //Calculates the normalized difference
    }
                    
    foreach var in `sheet`sheets'' {  //loops through each indicator    
                reg `var' `compare' , cluster(`schoolid')   
                test `compare'
                scalar `var'_p2 = r(p)                           //saves p value for each gender
                scalar `var'_p=string(r(p),"%10.5fc")          //store the p-value of the ttest of the coefficient on the consent variable
                local `var'_diff = round(_b[`compare'], .001) //stores the differnce in means and rounds it to 3 decimal points
                if `var'_p2 <= 0.1 & `var'_p2 > 0.05        local `var'_diff = "``var'_diff'*"  // makes the *** show up
                if `var'_p2 <= 0.05 & `var'_p2 > 0.01         local `var'_diff = "``var'_diff'**" 
                if `var'_p2 <= 0.01                            local `var'_diff = "``var'_diff'***"
                local `var'_sig = (r(p)) < 0.05 // dummy  = 1 if significant at 5% level 
        }
        
    *Putexcel codes to update the Excel file mentioned above
    local row = 2
    
    foreach var in `sheet`sheets'' {
            putexcel A`row' = ("`var'")         ///
                     B`row' = ("``var'_label'") ///
                     , fpat(solid, "221 235 247") vcenter  //Formating for labels
                     
            putexcel C`row' = (`var'_mean1)     ///
                     D`row' = (`var'_mean2)     ///
                      E`row' = ("``var'_diff'")  ///
                     F`row' = (`var'_ndiff)     ///
                     G`row' = (`var'_p)         ///
                     H`row' = (`var'_obs1)         ///
                     I`row' = (`var'_obs2)         ///
                     , vcenter hcenter  
            

                local row = `row'+1 //Next row
    }
}

I am using Stata 15.1.
Thank you for any help you can provide!

-Daniel

[William Lisowski]

The line you highlight sets the output workbook and sheet. It doesn't actually write anything to the worksheet, so it's not clear why it would be at fault.

Is this the code that writes the headings that are missing?

    putexcel A1=("`a1'") B1=("`b1'") C1=("`c1'") D1=("`d1'") E1=("`e1'") ///
             F1=("`f1'") G1=("`g1'") H1=("`h1'") I1=("`i1'") ///
             ,vcenter hcenter txtwrap

If so, how are the values of the local macros a1 through i1 being set? If they are not set at the time the putexcel command runs, each of the cells A1 through I1 will be blank. Preceding the putexcel command with

macro list _a1 _b1 _c1 _d1 _e1 _f1 _g1 _h1 _i1

will allow you to confirm that they have been correctly defined.

[Daniel Zaas]

Hi William,
Thanks for the response. The locals for the heading are set higher up in the do file before the loop. Adding the code you mentioned confirms that they are defined correctly. Additionally, if type in the headings manually instead of using the locals I have the same problem. The reason I highlighted that first line is that I am thinking the way I am replacing/modifying the sheets as it loops is causing my issue but I cannot figure out why or how to resolve it.

Thank you,
-Daniel