Export Stata results into duplicated preformatted EXCEL sheet template using putexcel

[Sebastian Wichert]

Hello,
I have many Stata results (tables, matrices) and I want to export them into several preformatted EXCEL sheets in a single EXCEL file (one table/matrix per sheet). Therefore I've created a preformatted EXCEL sheet which should be the template for all other sheets. Unfortunately putexcel only allows me to write in an already existing EXCEL sheet/template (or it creates an empty sheet/file). Is there any way how I can "convince" Stata/putexcel to duplicate the template and write every table/matrix into a newly created preformatted EXCEL sheet? Maybe there is another command? Am I missing something?

Any assistance is appreciated. If any information is missing, please don't hesitate to contact me.

Best,

Sebastian

P.S.: Creating sheets one by one with clicking in EXCEL isn't a option, because I need hundreds of sheets.

P.P.S.: My existing code either allows me to (over)write the template or to export it to a new unformatted sheet. (Each matrix should be in a separate sheet which should be formatted like the template)

*Existing code
forvalues x = 1/150 {
putexcel G3=matrix(A`x') using "sales.xlsx", sheet("template") modify keepcellf
}
* Suggestion which doesn't work as needed 
forvalues x = 1/150 {
putexcel G3=matrix(A`x') using "sales.xlsx", sheet("sales_Area`x'") modify keepcellf
}

[William Lisowski]

Welcome to Statalist!

I don't see an easy way of accomplishing what you want using just the capabilities of Stata.

My approach would be to create a "template" workbook containing the "template" sheet, and then use Stata's copy command to first make a copy of the workbook with a different (appropriate) name, and then putexcel to the template sheet using the new workbook. The end result will be hundreds of workbooks, each containing a single "template" sheet with the data from putexcel. At least that solves the problem of getting your data into the template without having to fiddle in excel creating hundreds of copies of the template sheet.

And then I would investigate Excel programming techniques for ways to automate copying and renaming the template worksheets from the hundreds of workbooks into a single workbook.

[Sebastian Wichert]

Hi William,

I just figured out that I totally forgot to answer to your post! I am really sorry! Thank you very much for for reply! Your suggestions worked quite well!

Best,

Sebastian

[LEILA BEN AOUN]

Hi,

I would like to do something similar, have you found a trick finally to have a template sheet and duplicates it ?
Thanks in advance

Leila

[Sebastian Wichert]

Hi Leila,
again I have to apologize that my reply is so late. Unfortunately I didn't find a solution to "merge" the EXCEL workbooks into one. But I have to admit that we didn't try it using VBA or something else. We were satisfied with the solution since the whole purpose of this exercise was to convince a data protection officer, who randomly and manually checked a few files, that we do not disclose single values or violate other data protection regulations. Nevertheless we had to deliver all our results in this human-readable format.

I hope you have found another solution that works for you.

Best,
Sebastian