Problem with putexcel

[Dario Maimone Ansaldo Patti]

Dear All,

I have the following issue. I wrote my own programme and I included putexcel to export some results in Excel. The part of the programme referring to putexcel is:

putexcel set mytable.xlsx, sheet(Sheet1) replace
putexcel (A1:D1)=("The Title"), merge border(bottom, double, black) hcenter vcenter bold
putexcel A2=("Var 1")
putexcel B2=("Var 2")
putexcel C2=("Var 3")
putexcel D2=("Var 4")

Apparently, when I run the programme I get the error message that the option merge is not allowed. If I remove it, Stata replies that the option border is not allowed and so on, until I am forced to remove all the options.

On the other hand, if I use the same lines outside the programme, they worked perfectly and I can customize my table. Hence my question is: Does putexcel behave differently if included in a programme? (If so, actually this would be quite strange in my view, but I do not have any other explanations).

Thanks

Dario

[Scott Merryman]

Having -putexcel- in program does not seem to a problem.

. capture program drop foo

. program foo
  1.         putexcel set mytable.xlsx, sheet(Sheet1) replace
  2.         putexcel (A1:D1)=("The Title"), merge border(bottom, double, black) hcenter vcenter bold
  3. end

. 
. foo
Note: file will be replaced when the first putexcel command is issued
file mytable.xlsx saved

Perhaps showing the simplest version of the program you are writing that will generate the error.

[Dario Maimone Ansaldo Patti]

Scott Merryman Thanks for your reply. This is a simplified version of my program:

capture program drop pairs

program define pairs, rclass
version 9

syntax varlist(min=2 max=100000 numeric) [if] [, SIg(numlist min=1 max=1)]

   putexcel set pairs.xlsx, sheet(Sheet1) replace
   putexcel (A1:D1)=("Prova"), border(bottom, double, black) merge hcenter vcenter bold 
   putexcel A2=("Stock 1"), hcenter border(bottom, black)  
   putexcel B2=("Causality"), hcenter border(bottom, black)
   putexcel C2=("Stock 2"), hcenter border(bottom, black)
   putexcel D2=("Result"), hcenter border(bottom, black)
   local result "Pair"
   local noresult "Not a pair"
   local rcausality "-->"
   local lcausality "<--"
   local dcausality "<-->"
   local nocausality "No causality"
   
   local k: word count `varlist'
   local km1 = `k' - 1  
   local m 2 
forval i = 1/`km1' {
   disp "i: `i'"
   local ip1 = `i' + 1   
   forval j = `ip1'/`k' {
   local ++m
   disp "j: `j'"
      local v1: word `i' of `varlist'
      local v2: word `j' of `varlist'
      di as result _newline "working with `v1' and `v2'"  
   reg L(0/2).`v1' L(1/2).`v2' `if' 
      testparm L(1/2).`v2'
      local p1=r(p)
   reg L(0/2).`v2' L(1/2).`v1' `if'
      testparm L(1/2).`v1'
      local p2=r(p)
   if `p1'<0.10 & `p2'>=0.10 {
    reg `v1' `v2'
    tempvar res_`v1'_`v2'
    predict `res_`v1'_`v2'' `if', res
    dfuller `res_`v1'_`v2'' `if', nocon
    local dfstat=r(Zt)
    local cv1=r(cv1)
    local cv5=r(cv5)
    local cv10=r(cv10)
    if `dfstat'<`cv10' {
    di as result _newline "`v1' and `v2' are cointegrated and can be treated as a pair"
    putexcel A`m'=("`v1'") 
    putexcel C`m'=("`v2'") 
    putexcel D`m'=("`result'")
    putexcel B`m'=("`rcausality'")
    }
   else if `dfstat'>=`cv10' {
    di as result _newline "`v1' and `v2' are not cointegrated and cannot be treated as a pair"
    putexcel A`m'=("`v1'") 
    putexcel C`m'=("`v2'")
    putexcel D`m'=("`noresult'")
    putexcel B`m'=("`rcausality'")
   }
   }   
   else if `p1'>=0.10 & `p2'>=0.10 {
    di as result _newline "`v1' and `v2' are not a pair"  
    putexcel A`m'=("`v1'") 
    putexcel C`m'=("`v2'") 
    putexcel D`m'=("`noresult'")
    putexcel B`m'=("`nocausality'")    
    }
    }
    }
   end

I tried once again but still I get the error message that merge is not allowed.

Thanks again for your help.

[William Lisowski]

Well, there's your problem.

version 9

The putexcel command fails with version control set to version 14 or earlier. Is there a need for you to have version control set to version 9? I suspect that other commands you show may not work with that old of a version. Why do you not set the version to the version of Stata you are developing the program with?

[Dario Maimone Ansaldo Patti]

William Lisowski Thanks William. Actually, I do not need version 9. I currently use Stata 15.1, but I am going to share this program with some friends that use Stata v. 14. If putexcel does not work with Stata 14, then I should think about something else.

[Scott Merryman]

I believe -putexcel- was introduced in Stata 13.

[William Lisowski]

When in post #4 I wrote "The putexcel command fails ..." I was referring specifically to the putexcel command in post #1 on which the merge option appeared and which fails. That option was apparently new in Stata 15.

[Dario Maimone Ansaldo Patti]

Scott Merryman and William Lisowski thanks for your help.