Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: Export Excel and Cell references


From   Eric Booth <eric.a.booth@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Export Excel and Cell references
Date   Mon, 23 Dec 2013 15:20:56 -0600

<>

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/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index