Re: st: Export Excel and Cell references

[Tal Elmatad <tal.elmatad@gmail.com>]

I have yet to figure this issue out. It seems as if this isn't an
uncommon issue people are experiencing.

On Mon, Dec 23, 2013 at 9:15 PM, Tal Elmatad <tal.elmatad@gmail.com> wrote:
> Ctrl+apt+shift+f9 will update the cells in excel after the export, but I am
> really hoping for something automated. Thank you for your responses so far,
> but I remain hopeful that we can find a work around.
>
> On Dec 23, 2013 4:21 PM, "Eric Booth" <eric.a.booth@gmail.com> wrote:
>>
>> <>
>>
>> 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/

*
*   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/