Export to Excel sheet names

[Sergiy Radyakin]

Dear All,

I have two concerns regarding the naming of the sheets in export to Excel.

1) First is that the $-sign is misinterpreted in the sheet name while exporting to Excel. Consider the following minimal example:

clear all
version 14.0
display "\$data"
sysuse auto
export excel "C:\temp\testbug.xlsx" , replace firstrow(variables) sheet("\$data")

Note that while display command obeys escaping of a macro, the export command does not and the output is placed into the sheet named "sheet1" instead of "$data".
I believe that Stata conducts the macro expansion somewhere internally where it shouldn't, evaluating $data to an empty string (if everything is running in a clean session) or more dangerously to the value of the global named data, which may cause accidental overwriting of some useful data.

2) Second, I have further checked, whether Stata is, perhaps, removing the \-slash as the first action resulting in an un-escaped sheet name. It is not doing this, and the \-slash gets saved all the way, resulting in a corrupt Excel file (Excel offers to recover data from such a workbook, since the \-slash is not permitted in sheet names).

<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<workbookPr/>
<bookViews>
  <workbookView activeTab="0"/>
</bookViews>
<sheets>
  <sheet name="\data" sheetId="1" r:id="rId2"/>
</sheets><calcPr calcId="125725" fullCalcOnLoad="true"/>
</workbook>

I wonder if these problems can be confirmed by a third party? whether they have been fixed in Stata 14.1? and what is the workaround for issue #1 in Stata 14.0?

Thank you,
Sergiy Radyakin

Stata 14.0MP/Windows

[Roger Chu]

I tried Sergiy's code and am confirming the same issue. I've also found some additional weirdness. I suspect that there's something happening during the reading of command syntax/tokens that leads to "\$data" being evaluated as a global.

When using set trace on, I see that the sheet name disappears early in the running of the export command, by the time the export ado passes off the information to export.ExpExcel. See below output from set trace on. The last line shows that "\$data" has disappeared by then.

  - else if `"`subcmd'"' == "excel" {
  = else if `"excel"' == "excel" {
  - ExpExcel `0'
  = ExpExcel  "test_sheet_name.xlsx", replace firstrow(variables) sheet("")

Additionally, I tried the following code, which did not help. Stata also evaluated the sheet name as a global.

export excel "test_sheet_name.xlsx", replace firstrow(variables) sheet("`=char(36)'data")

I tried the following code, and got the following output.

. loc sheetname "\$data"
. di "`sheetname'"

. di "`macval(sheetname)'"
$data

But when I tried

loc sheetname "\$data"
export excel "test_sheet_name.xlsx", replace firstrow(variables) sheet("`macval(sheetname)'")

the sheet name still was evaluated as a global and disappeared in the set trace on output.

No difference when running with version 14.0 or version 14.2 set.

[Sergiy Radyakin]

Dear Roger,

thank you very much for taking time to check and confirm the issue and provide this additional information. This is very helpful in tracking down the problem.
for now I will try to filter out \-slash and $-sign manually to avoid any unexpected behaviors.

Regards, Sergiy

[Kevin Crow (StataCorp)]

Thanks for reporting the bug. I will get this fixed for the next update.

[Sergiy Radyakin]

Dear Kevin, thank you very much! Other than these small problems export to Excel is working brilliantly! Sergiy