Creating a loop to export tables in multiple sheets in one excel file

[Yesmina Salib]

Hello,

I am trying to create a loop that will export a single excel file with separate tabs. Originally I was exporting each table separately by question, but I would like to create a loop that does some of the manual work so that I do not have to combine the excel files into one single file at the end

Here is the code I was using to create excel exports:

table (Q48_1) (StartDateYr) (cohort Q55), statistic(frequency) statistic(percent, across(Q48_1))
collect export "~/Downloads/Q48_1.xlsx", sheet(Q48_1) replace

And here is the loop:


local first = 1

foreach var of varlist Q5 Q6 Q22 Q23 Q24 {
collect clear
table (`var') (StartDateYr) (cohort Q55), statistic(frequency) statistic(percent, across(`var'))

if `first' {
collect export "YS_TEST_v2.xlsx", sheet("`var'") replace
local first = 0
} else {
collect export "YS_TEST_v2.xlsx", sheet("`var'") modify
}
}


When I use the code above, my export only has a single tab (for Q5) and I get the error message:

program error: code follows on the same line as close brace
r(198);

Is it possible to adjust the loop to create one excel doc that has multiple tabs for each of the tables that I was exporting separately?
Should I be using putexcel or another command?

Thank you!

[Clyde Schechter]

The error message is self-explanatory. Once you have } on a line of code, you cannot have anything after it (well, at least nothing that isn't commented out). You have

} else {

as one of your lines of code. Put a newline before -else- and that error message will go a way. Also, because you are getting that error, the code stops executing at that point. This error is encountered the very first time the loop iterates (i.e. the Q5 iteration) and Stata never gets to try the others. That's why you have only one worksheet in your output file. So fixing this error message will resolve that part of the problem as well.

[Simon Pfaff]

Yesmina, I also create several tables and save them in different sheets of a single excel file.
I use putexcel and created a short example to show the different steps. Maybe it helps...

clear all
collect clear

* important in my example: erase the file
capture erase "D:\Beispiele\3_exl_sheets\result.xlsx"

* data
sysuse auto.dta    


* create the tables
************************************

local j = 1

* create a bunch of tables
foreach var of varlist foreign foreign {    
    table (rep78 ) (`var'),      ///
    nototals                                             ///
    statistic(percent, across(rep78))                ///
    name(table`j')
    local j = `j'+1
}
    
* Show the created tables    
collect dir    


* insert the tables using putexcel
************************************

* you could use loops here...

* First Table
*local page = 1
putexcel set result.xlsx, sheet("1") modify
putexcel A1 = collect, name(table1)

* Second table Table
*local page = `blatt+1'       
putexcel set result.xlsx, sheet("2") modify
putexcel A1 = collect, name(table2)

* there are a lot of possibilities to format the tables with putexcel
* you can also use xl() to change the excel file


* change col width
************************************
mata: b = xl()
mata: b.load_book("result.xlsx")
mata: b.set_sheet("2")
mata: b.set_column_width(1,1,30)