With the release of Stata 14.1, Stata made a good amount of changes to one of my favorite commands: putexcel – I have mixed feelings about this.
First, I want to take the opportunity to stress version control. Always begin your do-files with the version command. If you don’t, you’ll likely get the error r(101) “using not allowed” when trying to implement your old putexcel commands in a do-file. A quick note about version: you must run this command with the putexcel statement. Just having it at the top of your do-file and calling it doesn’t work, unless you run the do-file all the way through. Think of using version control like using a local macro.
What I like:
Putexcel has been simplified…
…but it requires changing the way you think about putexcel. Think of the command in a more modular structure.
putexcel set MyBook.xlsx, sheet("MyData") putexcel A1=("Title of Data") A2=("Subtitle") A3=formula("SUM(A4:A20)"), bold putexcel A3:A20, nformat(number_sep) hcenter
The updated options simplify your code a bit:
No more bold(on), rather just bold or nobold and there’s no need for that modify command.
Update: as of the December 21st update, modify is an option again...
Advanced number formats may be used:
No more restrictions, the advanced number format can now be used and can be found under putexcel advanced – which makes sense since it was able to be used already in the Mata xl() object.
putexcel set MyBook.xlsx, sheet("MyData") putexcel A3, nformat(#.000) nobold border(all)
What I don't like:
Using not allowed! I liked the using statement within putexcel, especially when I needed to add something as simple as a title on an excel spreadsheet. Now, I have to use two lines of code instead of one. This is a minor complaint compared to the next section.
The new syntax – it’s very difficult to work with large files, which is why I’ll be sticking with my “version 14.0” command when writing putexcel statements to large excel files. Why? I cannot find a simple way to write different format-types in one statement (say bold font in cell A1 and a border around cell B2). I have to do this in two separate commands. This is a big problem when you’re working with large excel files because Stata keeps opening the file, saving it, and closing the file for EVERY putexcel statement. We could waste minutes writing borders and adding different colors. Granted, Excel files shouldn’t be 40MB but we have them; and management wants to keep them that way, putting the Stata programmer between a rock and a hard place. Check out my previous post on “block” putexcel for working with very large excel files.
What I hope happens:
We get the best of both worlds – the putexcel set keeps the Excel file open, so that it doesn’t continuously save file after file but the new format stays. If we have a putexcel set, why not putexcel close? Oh, and using is added back as an optional/hidden feature of putexcel. That, my friends, would be my ideal putexcel command: not too hot, not too cold; simple, yet powerful.
1/4/2016 03:37:15 am
Will - I've got a question about your beef with the new, 2-line-requiring syntax vs. the old single-line syntax. If I'm understanding your statement properly, Stata opens/modifies/saves the Excel file in question each time an instance of -putexcel- is run.
Hello Tim, thank you for that question - I've been at it for a few hours now and it's quite frustrating (and is likely a Stata Support fix).
1/5/2016 01:11:48 am
With my deepest apologies for having sent you down an hours-long rabbit hole, I really appreciate the response. I would never have been able to troubleshoot that in the depth that you did - don't know enough about the inner workings of how Stata interacts with Office .***x files - but it's reassuring that you encountered the same behaviors.
1/13/2016 08:00:32 am
1/13/2016 08:18:07 am
I should add that when I exclude the names export option the putexcel command works perfectly. However I need the row and col names bc i plan to make graphs and things in excel once I have them in there.
1/13/2016 09:33:28 am
Thanks, Will! That did it. It's always the little things!
5/31/2017 08:16:09 am
6/1/2017 05:00:12 am
2/14/2018 10:07:39 am
Very helpful- Thank you!
Leave a Reply.
Will Matsuoka is the creator of W=M/Stata - he likes creativity and simplicity, taking pictures of food, competition, and anything that can be analyzed.