Exporting collection to excel without formatting, or overwrite formatting?

[Thomas Masterson]

Hi All,
Long time reader, first time poster. I've been banging my head against a wall trying to get the new

table

command to do what I want: export multiple large tables (ten variables, for which I calculate the mean and median for two different datasets by combinations of over a dozen categorical variables) into an excel file so that I can further manipulate the contents (bar graphs of ratios of original to matched variable means and medians, by over a dozen categories). For years, I did this using

tabstat

,

tabstatmat

and

mat2txt

, combined with

forval

loops. I would then copy and paste the csv files into my excel file. The new table command, combined with collect export looks like it should be able to automate the process. However, when I run my program, I eventually hit an error because each collect export command spawns multiple putexcel format commands. Example, below:

collect export qc_ENIGH20_ENUT19_v$version.xlsx, sheet(x) cell(A1)  ///
                                         modify noopen noisily

. version 17

. putexcel set `"qc_ENIGH20_ENUT19_v4.xlsx"' , modify sheet(x) 

. putexcel A1 , border(right,thin,)
could not write border() format to file

If I delete the spreadsheet and start fresh, I get halfway through the program before I hit an error. After that, and attempt to write to the file kicks out that error. Based on my research, I am hitting the excel limit on number of formats within a file.

My question is how do I get

collect export

(or

putexcel

) to paste table contents without adding formatting (which I don't want or need for my purpose)? Or is it back to

tabstat

, etc?

Best
Tom

[Jeff Pitblado (StataCorp)]

Try collect style use empty, replace.

[Thomas Masterson]

Thanks for the tip, Jeff!
That seems to have done the trick. Is this command fine for the preamble, or do I need it between every table and collect export command pair?

[Jeff Pitblado (StataCorp)]

Not exactly sure what you mean by preamble. Each call to table will start off with its default style, so you will need it between each call to table and collect export.

However, you can also specify style(empty) in your calls to table.

On the other hand, if don't want to add option style(empty) to all the table commands in your do-files, you can also put

set table_style empty

at the top of your do-files. When you start a new Stata session, the original table style will be restored.