Extremely basic putexcel doesn't work, no error message

[Jeanne Sinclair]

Hello,

I'm new to posting to the forum and a Stata newbie as well, but I'm a big fan. I'm using 14.1 (student version) and am attempting to export a series of tabulate command results to an excel file to document data cleaning.

The most basic putexcel command doesn't work, however, there is no error message. Here is the DO file:

putexcel set "results", sh("result") replace
putexcel A1=("Rule")

Here is the output:

. putexcel set "results", sh("result") replace
. putexcel A1=("Rule")
file results.xlsx saved

When I say it doesn't work, I mean that nothing shows up in the excel file.

Could this have anything to do with the fact that my files are in dropbox? Or that I'm using the student version?

Thanks in advance for your help!
Jeanne

[Jeanne Sinclair]

Well, I moved my file out of dropbox and it now seems to work.

[Kristoffer Bjarkefur]

Jeanne, you have most likely moved on and might not even remember this post, but I found this post when a colleague of mine had different but somewhat similar issues using the combination of dropbox and putexcel. We have something that is a solution to our exact issues, but my educated guess is that is a general solution to issues with dropbox and putexcel. So I want to share it to anyone else finding this post when googling similar.

Before each line of code with putexcel that immediately follows another line of code that used putexcel, add sleep 1000, like in the example below:

putexcel set "results", sh("result") replace
sleep 1000
putexcel A1=("Rule")
sleep 1000
putexcel B1=("Rule2")

Sleep makes Stata temporarily stop running the do-file for the number of milliseconds specified.

Here is my educated guess explaining this issue. Each time we save a file to a dropbox folder, dropbox notice that we have made a change to a file and perform some operation using that file. I don't know what that operation is but that is irrelevant. If we manually saved a file multiple times, then dropbox have time to do that operation in between each save. However, when we use a much-faster-than-human software like Stata to save a file multiple times (the Excel file is updated and saved each time we use putexcel) then dropbox has no time to finish the operation before Stata tries to access the file again. Using the sleep command we allow dropbox to finish it's operation before we modify the file again.

It is most likely the case that 1 second is much longer than what dropbox needs to finish the operation, but unless you make a large number of putexcel calls, then you might be better of using a wide margin to be on the safe side.

If anyone has a better explanation than my educated guess, I am happy to be corrected.

Kristoffer

[Brina Seidel]

I frequently encounter this problem when I use putexcel -- sometimes the excel file saves quickly and the code will run just fine, but sometimes my system takes too long to save the file and my next putexcel statement crashes because the previous one was not yet completed.

I had been adding "sleep ###" after each putexcel command just to be on the safe side, but taking all those pauses was really slowing down my code. So I wrote a quick program that executes "sleep ###" only if needed. If the putexcel command works fine, then the code will not pause. However, if the putexcel command crashes because the system had not finished saving the previous modification to the excel file, then the code will pause and try the putexcel command again after waiting a second. I thought I would share this here in case it is of use to others.

To use Kristoffer's example, if my original code read as follows...

putexcel set "results", sh("result") replace
sleep 1000
putexcel A1=("Rule")
sleep 1000
putexcel B1=("Rule2")
sleep 1000

...then I would use this instead:

program define putexcel_wait
    *** Try the putexcel statement, capturing the results if there is an error    
    capture putexcel `1'
    *** If the error was because putexcel was unable to save the file, then wait 1000ms and try again
    if _rc == 603 {
        sleep 1000
        putexcel `1'
    }
    *** If there was a different error, then run the putexcel file again immediately and display the error message
    else if _rc != 0 {
        putexcel `1'
    }
end

*** Replace "putexcel" with "putexcel_wait", and enclose the rest of the putexcel command in compound quotes so that it is passed to the putexcel_wait program as a string
putexcel_wait  `"putexcel set "results", sh("result") replace"'
putexcel_wait  `"pputexcel A1=("Rule")"'
putexcel_wait  `"putexcel B1=("Rule2")"'

Hope this is of use.

[Clyde Schechter]

Brina, this is a nice solution to a common problem! It's a shame the Forum software limits me to liking it once. I have one thought about an improvement. Consider changing -if _rc == 603 {- to -while _rc == 603 }-. This would permit your program to try repeatedly until success is reached. While a second of sleep will probably be sufficient most of the time, I have found that when I am working remotely over a VPN connecting me to a computer that is, in turn, connected to an intranet and the data files are on yet another server, that sleep times up to 5 seconds are occasionally necessary. Perhaps few people experience delays that long, and, of course, the code risks hanging in an infinite loop if the problem opening in the file arises from something that is not helped out by waiting (like somebody else opened the Excel file on that server and has left it open and walked away--you could be looping for days on this!) So I'm not sure if, on balance, this would be an improvement or not in general use. It would be for me, I think. (I will make that change when I use it, or perhaps add a counter and limit the number of attempts.)

Another potential improvement. Your code requires the user to wrap the original putexcel command in compound double quotes, which is inconvenient and can make the user's code slightly less readable. What about this:

program define putexcel_wait
    *** Try the putexcel statement, capturing the results if there is an error    
    capture putexcel `0'
    *** If the error was because putexcel was unable to save the file, then wait 1000ms and try again
    if _rc == 603 {
        sleep 1000
        putexcel `0'
    }
    *** If there was a different error, then run the putexcel file again immediately and display the error message
    else if _rc != 0 {
        putexcel `0'
    }
end

The 0 local macro contains everything in the command line received by the program except the program name. So then this could be invoked without extra quotes and without a redundant putexcel in the argument:

putexcel_wait set "results", sh("result") replace
putexcel_wait A1=("Rule")
putexcel_wait B1=("Rule2")

That is, the putexcel_wait command would be exactly identical to the desired -putexcel- command, except for the command name.

[Brina Seidel]

Thanks for the suggestions, Clyde! I didn't know about `0' and I will definitely use it in the future. Hope others find this useful as well!

[Masoumeh Sanagou]

Brina, this is a nice solution to a common problem! It's a shame the Forum software limits me to liking it once. I have one thought about an improvement. Consider changing -if _rc == 603 {- to -while _rc == 603 }-. This would permit your program to try repeatedly until success is reached. While a second of sleep will probably be sufficient most of the time, I have found that when I am working remotely over a VPN connecting me to a computer that is, in turn, connected to an intranet and the data files are on yet another server, that sleep times up to 5 seconds are occasionally necessary. Perhaps few people experience delays that long, and, of course, the code risks hanging in an infinite loop if the problem opening in the file arises from something that is not helped out by waiting (like somebody else opened the Excel file on that server and has left it open and walked away--you could be looping for days on this!) So I'm not sure if, on balance, this would be an improvement or not in general use. It would be for me, I think. (I will make that change when I use it, or perhaps add a counter and limit the number of attempts.)

Another potential improvement. Your code requires the user to wrap the original putexcel command in compound double quotes, which is inconvenient and can make the user's code slightly less readable. What about this:

program define putexcel_wait
*** Try the putexcel statement, capturing the results if there is an error
capture putexcel `0'
*** If the error was because putexcel was unable to save the file, then wait 1000ms and try again
if _rc == 603 {
sleep 1000
putexcel `0'
}
*** If there was a different error, then run the putexcel file again immediately and display the error message
else if _rc != 0 {
putexcel `0'
}
end

The 0 local macro contains everything in the command line received by the program except the program name. So then this could be invoked without extra quotes and without a redundant putexcel in the argument:

putexcel_wait set "results", sh("result") replace
putexcel_wait A1=("Rule")
putexcel_wait B1=("Rule2")

That is, the putexcel_wait command would be exactly identical to the desired -putexcel- command, except for the command name.

Thanks for these codes. They saved me a lot of time.

[ben earnhart]

Awesome! This little bit of code is saving me huge amounts of time and hassle!

[Bill Rising (StataCorp)]

For people struggling with Dropbox or others hitting Excel files often with putexcel commands, you might want to look at

putexcel set ...., open
* many other putexcel commands
putexcel close

This allows building the workbook in memory instead of writing every change to disk, so it both is faster and avoids sync problems. The main tradeoff is that you need to remember to close the file to be sure that it gets written to disk.

This addition to putexcel was included in the original Stata 15.1 update on November 6, 2017.

[Jeanne Sinclair]

Oh wow... I hadn't logged in to Statalist in months... this is exciting! I guess I didn't receive notifications... I'll turn that on now. I still have this putexcel problem, so I'll review the messages very soon. Many many thanks!!!