Re: st: Export Excel and Cell references

[Eric Booth <eric.a.booth@gmail.com>]

<>

Using -export excel- I havent been able to get the Formulas calculate/recalculate sheet options to work, nor has changing the format of the data from ‘Text’ to ‘General’.

The only way I’ve found to do this (at least for my setup using Stata 13 and MS Excel 2011 on a Mac OSX) is to export the formulas (as string variables in Stata) into the worksheet, then:
 open the worksheet containing those formulas, highlight the entire sheet, Find = and replace it with a =  (there’s no difference here , just find/replace an equal sign) and in doing so it will calculate the sheet.  

So, you can toy with this example, go to the “test2” worksheet and find/replace to see if this works for you.



sysuse auto, clear

export excel using "test.xlsx",  ///
	sheetreplace sheet("test1")  firstrow(variables)

g formula1 = `"=test1!B1:B100"'
g formula2 = `"=AVERAGE(test1!B1:B20)"'
g formula3 = `"=test2!A5"'


export excel formula* using "test.xlsx", ///
	sheetreplace sheet("test2") 



- Eric
eric.a.booth@gmail.com



On Dec 23, 2013, at 3:08 PM, Jeph Herrin <stata@spandrel.net> wrote:

> Do you have AutoCalculation enabled in the workbook? Where you check this differs on the version of Excel, but in Excel 2010 it is under Formulas-> Calculation options.
> 
> Tangentially, you may be interested in using -putexcel- to write your table directly instead.
> 
> hth,
> Jeph
> 
> 
> On 12/23/2013 11:12 AM, Tal Elmatad wrote:
>> Hello,
>> 
>> I am having a problem with Export Excel in Stata 12. I generally
>> create a “raw data” worksheet and then create a second formatted
>> worksheet that references cells on the “raw data” worksheet. I noticed
>> that when I rerun my Stata script, if I use Export Excel the cell
>> references don’t update (i.e. while the raw data is updated, the
>> formatted worksheet is showing the old values). I suspect that since
>> Excel only updates references when a connected cell is updated, that
>> the Export function is somehow not registering as a change to Excel.
>> Is there an option I can use to force the workbook to update? Thank
>> you!
>> 
>> Tal Elmatad
>> 
>> *
>> *   For searches and help try:
>> *   http://www.stata.com/help.cgi?search
>> *   http://www.stata.com/support/faqs/resources/statalist-faq/
>> *   http://www.ats.ucla.edu/stat/stata/
>> 
>> 
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/