"we found a problem with some content" using putexcel

[ben earnhart]

I'm using putexcel to generate a set of workbooks, each with multiple worksheets. Each workbook generates the same errors when I open them; the contents are visually fine, But I get the errors (screenshots below) on each of the workbooks. I can open each, save the recovered document, and eliminate the errors that way, but I'd obviously much rather avoid having the error in the first place.

Sorry no code to show; the workbooks are complicated enough that I have about 1,000 lines of code, and posting all that wouldn't be much use. Thanks for any thoughts.

[Hua Peng (StataCorp)]

We do need see your do-file in order to figure out the problem. Please send you do-file and dataset(s) the do-file used to generate the Excel file to tech support at [email protected]

Please also include the following information, the output from Stata commands

about
query compilenumber

and the version of Excel.

[ben earnhart]

Thanks. The data is fairly sensitive HR data, so I'll need to generate some fake data to use instead. Will send it later this afternoon.

[ben earnhart]

Arrgh. I was going to use our actual employee roster, just replace the employee responses with fake data, but apparently even the employee roster is too sensitive to send to you. It would be hard to generate data that replicates the hierarchy properly. So I think I'll need to take the roster and hash the names, or just live with the errors (if I open the files, let excel repair them, then save them, they work without error from that point forward). Annoying, but it's only about 20 files, so the effort to get a *real* solution vs. living with the problem is a toss-up.

[ben earnhart]

I munged the data so I could share it, and sent the data, do files, and results of about and query compiler to Stata support. I forgot to mention that I'm using MS Office 2013 with all updates.

[Hua Peng (StataCorp)]

Thanks, we will look into it.

[Andrew Wade]

Hello,
Any resolution to this issue? I have it occasionally. The actual data seems OK. It is just that all the formatting is gone.
Regards,
Andrew

[Yahya Shamekhi]

I am recently getting the same error on files that had no issue previously (i.e. same do file and Excel destination used to work fine). I have recently updated Excel from 2016 to 365. Stata version is 15.0. It does not happen to all Excel files. I have experiences mostly in Excel files with several sheets.

[Kevin Crow (StataCorp)]

One possibility is that the Excel workbook is being overloaded with too many formats, which can make the workbook really large and cause errors. Try to specify your formatting for a range of cells

putexcel D5:D7, nformat(#.##)

instead of

putexcel D5, nformat(#.##)
putexcel D6, nformat(#.##)
putexcel D7, nformat(#.##)

The first command adds one format to the Excel workbook, whereas the last set of commands adds three formats.

[Yahya Shamekhi]

Thanks, Kevin, for your response. I tried your solution but it didn't work. Try the following code, for example, on this file CopyToExcel.xlsx. Try opening the file before and after running the code.

putexcel set "C:/Users/`c(username)'/Downloads/CopyToExcel.xlsx", modify sheet("basix_income_groups")
putexcel B3:B7, nformat(#.###)