Putexcel 14.1

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.  

1. Set your Excel file that you wish to beautify
2. Put all your text/data into the spreadsheet
3. Format blocks of like things

Example:

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.