Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • putexcel error: "using not allowed"

    I read this forum all the time, but I'm new to posting, so please forgive any mistakes.

    I just installed the October 29th Stata update, and now all my code involving the "putexcel" command is returning the "using not allowed r(101)" error.

    Here's an example of my code that used to work fine:
    Code:
    fre c1checkweight if today_date - c1wgtdate < 8, nomissing include(0 1)
    matrix r2 = r(valid)
    if r2[1,1] == . {
    putexcel B10=("N/A") C10=("N/A") D10=("N/A") using "QC Template Weekly Progress Slide 2015-06-22", modify keepcellformat
    }
    else if r2[1,1] != . {
    putexcel B10=(r(N)) C10=matrix(r2[2,1]) D10=matrix( (r2[2,1]/r(N))*100 ) using "QC Template Weekly Progress Slide 2015-06-22", modify keepcellformat
    }
    However, even trying to recreate the basic examples in the manual ( for the command will produce this error. For example:
    Code:
     putexcel A1=(2+2) using file
    I realized that this is related to the recent updates to "putexcel", where you must now use "putexcel set" to specify the file for subsequent putexcel commands. This makes things easier in the long run (i.e., now you don't have to constantly re-specify where you want your cells to go, but it also has the (perhaps unintended) effect of making old code no longer work.

    The options ", modify" and ", keepcellformat" also seem to produce errors when using just the "putexcel" command.

    I've updated all my code so it now works, but I thought this might confuse others at first as well, so I thought I'd mention it here in case it helps!

    Thanks!

    -Evan

  • #2
    Helpful post.

    Comment


    • #3
      Hi Evan,

      I am having the exact same problem since I updated the stata package. Could you post the old and new syntax you used so I can see where I need to make the changes to mine? Thank you!

      I am using :

      putexcel A1=("Tract") D1=("Beta") E1=("LowerCI") F1=("UpperCI") G1=("Pvalue") K1=("adj_Beta") L1=("adj_LowerCI") M1=("adj_UpperCI") N1=("adj_pvalue") using "/Users/Hannah/Documents/Neuro/Thesis/New_topic/ANALYSIS/ADHDvsTD_lang_graphs_DPCovariates", sheet("Left_Hemisphere") modify

      local i=2
      foreach var in LAF_FA LSLF3_FA LTP_FA LIFOF_FA LILF_FA LUF_FA{
      regress `var' group_w3 ChildGender c3_childage
      matrix m=r(table)
      putexcel A`i'=("`var'") D`i'=matrix(m[1,1]) E`i'=matrix(m[5,1]) F`i'=matrix(m[6,1]) G`i'=matrix(m[4,1]) using "/Users/Hannah/Documents/Neuro/Thesis/New_topic/ANALYSIS/ADHDvsTD_lang_graphs_DPCovariates", sheet("Left_Hemisphere") modify

      regress `var' group_w3 ChildGender c3_childage WB_FA
      predict `var'_resid, resid

      matrix m=r(table)
      putexcel K`i'=matrix(m[1,1]) L`i'=matrix(m[5,1]) M`i'=matrix(m[6,1]) N`i'=matrix(m[4,1]) using "/Users/Hannah/Documents/Neuro/Thesis/New_topic/ANALYSIS/ADHDvsTD_lang_graphs_DPCovariates", sheet("Left_Hemisphere") modify
      local ++i
      }

      Best, Hannah

      Comment


      • #4
        Have you reviewed the output of help putexcel to familiarize yourself with the new syntax? I'm thinking you start with something like this:
        Code:
        putexcel set "/Users/Hannah/Documents/Neuro/Thesis/New_topic/ANALYSIS/ADHDvsTD_lang_graphs_DPCovariates", sheet("Left_Hemisphere") modify
        and your three putexcel commands no longer include the using clause and the sheet and modify options, and are broken into pieces, like this:
        Code:
        putexcel A1=("Tract")
        putexcel D1=("Beta")
        putexcel E1=("LowerCI")
        putexcel F1=("UpperCI")
        putexcel G1=("Pvalue")
        putexcel K1=("adj_Beta")
        putexcel L1=("adj_LowerCI")
        putexcel M1=("adj_UpperCI")
        putexcel N1=("adj_pvalue")
        Last edited by William Lisowski; 04 Jul 2016, 19:23. Reason: Corrected a misunderstanding in my second example

        Comment


        • #5
          Yep, I think William's got the right of it. Just for reference, the above code in my first post is an example of how it used to work with the old syntax, and here's how I changed the same lines of code so they work now:

          Code:
          *all putexcel commands will go to this file until new file specified (only have to specify once) 
          
          putexcel set "QC Template Weekly Progress Slide 2015-06-22", modify
          
          fre c1checkweight if today_date - c1wgtdate < 8, nomissing include(0 1)
          matrix r2 = r(valid)
          if r2[1,1] == . {
          putexcel B10=("N/A") C10=("N/A") D10=("N/A") 
          }
          else if r2[1,1] != . {
          putexcel B10=(r(N)) C10=matrix(r2[2,1]) D10=matrix( (r2[2,1]/r(N))*100 ) 
          }
          Part of what makes the new syntax nicer is that you only have to specify the file path and name once (or until you want to reference a different file), instead of having to type it over and over again.

          Good luck!

          Comment


          • #6
            That's very interesting. The output of help putexcel does not include the syntax to put multiple cells with a single command. That was why I edited my initial answer to separate the individual values on separate commands.

            Now I see that I needed to follow the link to help putexcel advanced to find out about that syntax.

            Thanks for sharing your experience!
            Last edited by William Lisowski; 05 Jul 2016, 06:04.

            Comment


            • #7
              Hi William and Evan,

              Thanks so much for taking the time to help me. I understand what needs to be done now. I have multiple excel spreadsheets which Im working with, each with contains multiple sheets. Is there anyway of reverting back to the old version of stata so I can continue using the old syntax so I dont have to change all my code?

              Thank you!
              ​Hannah

              Comment


              • #8
                Already answered in this forum by Kevin Crow: see his post at http://www.statalist.org/forums/foru...on-of-stata-14

                See also http://www.stata.com/help.cgi?version #6

                Comment


                • #9
                  This has been so helpful. I simply put 'version 14.0' at the start and the old putexcel commands will run as per usual. Thank you all for your help. I really appreciate it and hope this feed helps other novice users like myself.

                  Comment


                  • #10
                    How do you deal with the keepcellformat now? I haven´t been able to figure it out.
                    Thank you!

                    Update:

                    Well, a simple way is to set the version before the line, and keep the old formatting:

                    version 14.0
                    putexcel C5=matrix(q180) using table.xlsx, modify keepcellformat

                    other solutions would be appreciated.
                    Last edited by Triana Yentzen; 02 Feb 2017, 12:58.

                    Comment


                    • #11
                      Originally posted by Triana Yentzen View Post
                      How do you deal with the keepcellformat now? I haven´t been able to figure it out.
                      Thank you!

                      Update:

                      Well, a simple way is to set the version before the line, and keep the old formatting:

                      version 14.0
                      putexcel C5=matrix(q180) using table.xlsx, modify keepcellformat

                      other solutions would be appreciated.
                      The default behavior of putexcel since (I think) version 14.1 is to preserve cell formatting unless otherwise specified. See the overwritefmt option in help putexcel if you want to overwrite cell formatting.

                      Comment


                      • #12
                        Originally posted by Evan Sommer View Post
                        Yep, I think William's got the right of it. Just for reference, the above code in my first post is an example of how it used to work with the old syntax, and here's how I changed the same lines of code so they work now:

                        Code:
                        *all putexcel commands will go to this file until new file specified (only have to specify once)
                        
                        putexcel set "QC Template Weekly Progress Slide 2015-06-22", modify
                        Part of what makes the new syntax nicer is that you only have to specify the file path and name once (or until you want to reference a different file), instead of having to type it over and over again.
                        Thank you so much! This was immensely helpful!

                        Comment


                        • #13
                          Originally posted by Evan Sommer View Post
                          I realized that this is related to the recent updates to "putexcel", where you must now use "putexcel set" to specify the file for subsequent putexcel commands. This makes things easier in the long run (i.e., now you don't have to constantly re-specify where you want your cells to go, but it also has the (perhaps unintended) effect of making old code no longer work.

                          The options ", modify" and ", keepcellformat" also seem to produce errors when using just the "putexcel" command.

                          I've updated all my code so it now works, but I thought this might confuse others at first as well, so I thought I'd mention it here in case it helps!
                          Much appreciated! I was really worried!

                          Comment

                          Working...
                          X