Hi All,
I'm running a code (Stata18) to create an automated excel report that includes several formulas within several rows of a 40k row file. To do this, I'm using putexcel. It basically runs a code over 50 variables. Unfortunately, at some point putexcel stops working and tells me "count not write cell format formula()" - Error message r(691).
I have tried several options, I put a timmer in the loop as error code 691 is linked to an I/O writing problem. I have also broken down the output process in smaller pieces and so on, but I keep getting the same error message after the 3rd loop. Below, under "Original Code" I'm including the code that I'm running but right now, I'm assessing alternatives without using putexcel.
As I front runner I have export excel, however all the exported rows with formulas are exported as a string. In this option, is there a way to change the format of these rows in a sistematic manner? That is, with export excel I have to create within stata an string variable. Hence in excel that cell reported has a a text format and I want to figure out a way to automatically change that format, otherwise the formula is not identified as a formula until one has to open the file and manually change the format in excel. Since I'm reporting a big file, a manual change of format of around 2000 rows would be not scalable. As an example I include the below sample code (Plan B) of what I'm thinking on doing as an alternative, The red text is hihglighing what I would love to achieve, but I do not know how. Any suggestion is appreciate it.
Plan B:
clear
set obs 4
gen idn=_n
gen y=rnormal() if idn<=3
gen y2="=sum(A2:A4)" if idn==4
gen y3="=sum(A3:A4)" if idn==4
export excel y y2 y3 using "${NEWDATA}bor_test.xlsx",replace sheet("Test") firstrow(variables)
cd ${NEWDATA}
global FNAME "${NEWDATA}bor_test.xlsx"
global NTABC "Test"
putexcel clear
putexcel set "${FNAME}", sheet("${NTABC}") modify open
putexcel A5:C5 , overwritefmt nformat(#.##) -> but this does not achieve to convert rows to a formula.
putexcel save
Best,
Danielken
**Original Code with problem of putexcel afther third run.
use "${NEWDATA}bor_ccontable_all.dta",clear
gen idkeep=1 if idbloque<=27
drop if idbloque==.
drop idkeep
levelsof digitsoracle,local(lclin) clean
local fclin : word 1 of `lclin'
local oclin : list lclin - fclin
local smonth "01 02 03 04 05 06 07 08 09 10 11 12"
foreach x of local smonth {
local svars`x' "f${FORYEAR}m`x'_hd f${FORYEAR}m`x'_pd f${FORYEAR}m`x'_acute f${FORYEAR}m`x'_cext f${FORYEAR}m`x'_predia f${FORYEAR}m`x'_proanci f${FORYEAR}m`x'_tot"
}
local keyvars "name2servinte idbloque oraclecode codeaccounting coddesc segmento descsegmento notes"
global FNAME "for_cconta_${FTYPE}_${FORYEAR}.xlsx"
global NTABC "clinicas"
sort idnobs
cd "${TABLES}"
*REPORTING OUT
local idrow "3 6 7 10 13 16 17 23 25 27"
local varsm "01 02 03 04 05 06 07 08 09 10 11 12"
local scolumn01 "G"
local scolumn02 "N"
local scolumn03 "U"
local scolumn04 "AB"
local scolumn05 "AI"
local scolumn06 "AP"
local scolumn07 "AW"
local scolumn08 "BD"
local scolumn09 "BK"
local scolumn10 "BR"
local scolumn11 "BY"
local scolumn12 "CF"
foreach x of local varsm {
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==3 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==6 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==10 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==13 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==16 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==23 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==25 & form_`x'==""
replace form_`x'="sum(`scolumn`x''"+string(min_sum)+":`sco lumn`x''"+string(max_sum)+")" if idbloque==27 & form_`x'==""
foreach i of local lclin {
levelsof idnobs if idbloque==3 & digitsoracle=="`i'",local(idposhd) clean
levelsof idnobs if idbloque==6 & digitsoracle=="`i'",local(idpospd) clean
replace form_`x'="sum("+"`scolumn`x''`idposhd',`scolumn`x' '`idpospd')" if idbloque==7 & form_`x'=="" & digitsoracle=="`i'"
levelsof idnobs if idbloque==10 & digitsoracle=="`i'",local(idpos2hd) clean
levelsof idnobs if idbloque==13 & digitsoracle=="`i'",local(idpos2pd) clean
levelsof idnobs if idbloque==16 & digitsoracle=="`i'",local(idposac) clean
replace form_`x'="sum(`scolumn`x''`idpos2hd',`scolumn`x''` idpos2pd',`scolumn`x''`idposac')" if idbloque==17 & form_`x'=="" & digitsoracle=="`i'"
}
}
putexcel clear
putexcel set "${FNAME}", sheet("${NTABC}") modify open
foreach i of local lclin {
display "Esta en clinica `i'"
foreach x of local varsm {
foreach j of local idrow {
levelsof form_`x' if idbloque==`j' & digitsoracle=="`i'", local(for`x'_`j') clean
levelsof idnobs if idbloque==`j' & digitsoracle=="`i'", local(idobs`x'_`j') clean
putexcel `scolumn`x''`idobs`x'_`j''=formula(`for`x'_`j'')
}
}
}
putexcel save
drop form_*
**Error Message
