topic Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt" in SAS Programming

"The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

Jeff_DOC — Mon, 01 Nov 2021 16:48:51 GMT

Good morning.

I have code that run and periodically generates no qualifying records. When this happens I'd like the user to receive a report with the same name but with just two lines stating no qualifying records exist for this reporting period.

I have no idea what I'm doing wrong here.

Thanks very much.

%END;
%ELSE %DO;
data _null_;
ods Excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx";
put "No qualifying records were found for the report period";
put "SECTION NAME";
run;
%END;

Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

ballardw — Mon, 01 Nov 2021 18:06:03 GMT

@Jeff_DOC wrote:

Good morning.

I have code that run and periodically generates no qualifying records. When this happens I'd like the user to receive a report with the same name but with just two lines stating no qualifying records exist for this reporting period.

I have no idea what I'm doing wrong here.

Thanks very much.

%END;
%ELSE %DO;
data _null_;
ods Excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx";
put "No qualifying records were found for the report period";
put "SECTION NAME";
run;
%END;

Your posted code does not create any ODS output. So without any content your may have an issue. PUT writes to the log. You need more work to write to a file.

Missing an "Ods Excel Close;" statement likely but can't tell with partial code. Missing close means SAS is still attempting to use the file and opening in Excel will have issues.

Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

Jeff_DOC — Mon, 01 Nov 2021 18:12:53 GMT

Well now that's interesting. Could you tell me how I would write a line(s) to the Excel file? I had no idea PUT only worked for the log.

There is an entire section above that code that will generate output for the same named file (if there is any that is). This section is only used if no output records exist. Could I use PROC EXPORT and write lines to the file?

Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

Tom — Mon, 01 Nov 2021 18:32:48 GMT

You can use PUT to write to other places than the LOG. But you need execute a FILE statement first.

%IF .... %THEN %DO;
....
%END;
%ELSE %DO;
ods excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx";
data _null_;
  file print;
  put "No qualifying records were found for the report period";
  put "SECTION NAME";
run;
ods excel close;
%END;

Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

Jeff_DOC — Mon, 01 Nov 2021 18:40:03 GMT

Worked like a charm (after correcting all my typos). Thanks very much.

Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

Reeza — Mon, 01 Nov 2021 19:05:40 GMT

Or save the data to a data set and use PROC REPORT with the NOHEADER option to export.

Re: "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

Jeff_DOC — Mon, 01 Nov 2021 19:15:58 GMT

Another great idea. Thank you Reeza. I will keep both in mind.