Excel Export Error

[Jyuen204]

I am creating an excel export and am running into a semi-error.

the below is my export and i am exporting to and XLS format. THe below export code exports based on my report macros. The output is exaclty as I want the report on every tab. However when It exports and emails to my distribution (email string not included) ; When I open the file attachment I get the following message:

"The file format and extension of 'Report Package (034).xls' don't match. The file could be corrupt or unsafe ....."

The file opens just fine and looks fine in all aspects. I just do not know why it would give this message when i open the excel document 

%let report_dir = %sysfunc(pathname(work));

ods tagsets.ExcelXP path= "&report_dir" file= 'Report Package.xls' Style=Printer;
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Daily Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9,9,9,9,9');
%report_page1(Nordia_Daily);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9');
%report_page2(Nordia_Weekly);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Monthly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9');
%report_page3(Nordia_Monthly);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Week over Week % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9,9,9,9,9');
%report_page4(NORDIA_WEEKLY_PCT);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month over Month % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9');
%report_page5(NORDIA_MONTHLY_PCT);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month to Date Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9');
%report_page6(NORDIA_MTD_TRANSFERS);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Agent Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='23,20,6.5,6.5,6.5,6.5,6.5,6.5');
%report_page7(AGENT_REPORT);

ods tagsets.ExcelXP close;

[Tom]

Excel is mad because the extension does not match the type of file you created.  You created an XML file but you used XLS as the extension.  It suspects that someone might have sent you a virus.  Change the extension to XML and Excel will not give that error.

Or if your SAS version is recent (last ?? years) then use ODS EXECL instead of ODS TAGSETS.EXCELXP to create an XLSX file instead.

[Jyuen204]

If I change it to an XML file format, it sends me a file with *.xml and I do not want that either.

I tried using ODS EXCEl instead of ODS EXCELXP and that did not work and still generated the same error.

I would use the XLSX option but I do not know why all of my formatting options are thrown out the window. Unless there is a completely different set of syntax required to format each column differing from what i coded for XLS...

[ballardw]

---

@Jyuen204 wrote:

If I change it to an XML file format, it sends me a file with *.xml and I do not want that either.

I tried using ODS EXCEl instead of ODS EXCELXP and that did not work and still generated the same error.

I would use the XLSX option but I do not know why all of my formatting options are thrown out the window. Unless there is a completely different set of syntax required to format each column differing from what i coded for XLS...

---

Pick one: the correct file type or a complaint by Excel every time you open the file. Or open once in Excel and do a file save as to force a conversion to native Excel format.

[Tom]

---

@Jyuen204 wrote:

If I change it to an XML file format, it sends me a file with *.xml and I do not want that either.

I tried using ODS EXCEl instead of ODS EXCELXP and that did not work and still generated the same error.

I would use the XLSX option but I do not know why all of my formatting options are thrown out the window. Unless there is a completely different set of syntax required to format each column differing from what i coded for XLS...

---

You could explore why getting a file with XML extension is a problem. Perhaps there is a solution for that. Either in how you tag the file when sending it or by telling the machine that is receiving the file to use EXCEL to open .XML files.

If you want to switch from TAGSETS.EXCELXP to EXCEL you need to switch everything. And yes there are some changes in what you can do and how you do it when controlling the formatting of the output with the EXCEL destination.  In general it works the same, but the details are slightly different so some features that TAGSETS.EXCELXP supports might require different syntax or might not even be possible with ODS EXECL. The reverse is also probably true, there might be features that you need that TAGSETS.EXCELXP did not support and EXCEL does.

[Reeza]

---

@Jyuen204 wrote:

If I change it to an XML file format, it sends me a file with *.xml and I do not want that either.

I tried using ODS EXCEl instead of ODS EXCELXP and that did not work and still generated the same error.

I would use the XLSX option but I do not know why all of my formatting options are thrown out the window. Unless there is a completely different set of syntax required to format each column differing from what i coded for XLS...

---

ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Monthly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9');
...
ods tagsets.ExcelXP close;

You didn't close your ODS EXCEL file properly in the code you posted or specify the options.

Change them all to ODS EXCEL and ensure the names are the same in the new tagset.

The options are listed here and most seem to be valid, except AUTOFIT_HEIGHT. 

ods excel options(sheet_interval="none" sheet_name='Monthly Summary Report' absolute_column_width='43,9,9,9,9,9,9');

ods excel close;

https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVe...

[Jyuen204]

in Addition, I have tried the XLSX in the code below. And while it does not produce the errror as with the CLS option, my column and tab formatting are all off wack. I used ABSOLUTE COLUMN WIDTH for sizing the columns in each report tab to be a specific size. however with the xlsx format it seemingly ignores all of my entries and also Bolds my tab titles which i never asked it to do

%let report_dir = %sysfunc(pathname(work));

ods excel file = "&report_dir./Bell MTS - Nordia Report Package.xlsx";
ods tagsets.ExcelXP path= "&report_dir" file= 'Bell MTS - Nordia Report Package.xls' Style=Printer;
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Daily Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9,9,9,9,9');
%report_page1(Nordia_Daily);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9');
%report_page2(Nordia_Weekly);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Monthly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9');
%report_page3(Nordia_Monthly);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Week over Week % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9,9,9,9,9');
%report_page4(NORDIA_WEEKLY_PCT);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month over Month % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9');
%report_page5(NORDIA_MONTHLY_PCT);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month to Date Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9');
%report_page6(NORDIA_MTD_TRANSFERS);
ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Agent Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='23,20,6.5,6.5,6.5,6.5,6.5,6.5');
%report_page7(AGENT_REPORT);

ods tagsets.ExcelXP close;