Not able to export XLSM files through SAS EG

[Reddy_GS]

Hi Team,

we are not able export xlsm file through SAS EG using PC file server. can you please advice how to proceed.

Here is the error message we are getting:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

ERROR: Error in the LIBNAME statement.

Current Environment: Linux x64 , EG 6.1

Do we need to Install Excel ODBC Drivers on our Linux box please advice how to resolve this issue.

[Reeza]

If EG is only on the server you can't use libname method to export. You didn't say that's how you're trying to export, but I'm assuming based on the error.

You'll need to use proc export instead, try with DBMS=XLSX or DBMS=EXCELCS

[Reddy_GS]

Hi Reeza,

Thank for quick response,

I used proc export and here is sample program

Proc EXPORT DATA=sashelp.shoes

OUTFILE= "\\SAS\UserData\newxlsm.xlsm" 

DBMS=EXCELCS  REPLACE;

PORT=9621;

SERVER= 'T01LPS2021.ent.rt.com';

sheet="xlsm";

RUN;

See the below error:

1. if I give DBMS=xlsx

ERROR is: ERROR 180-322: Statement is not valid or it is used out of proper order.

If I am giving DBMS=EXCELCS Error message is

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

ERROR: Error in the LIBNAME statement.

[Reeza]

Do you have a valid license to SAS Access to PC Files?

You can check this by running

proc stint;run; *Checks license;

proc product_status;run; *checks installation;

[Reddy_GS]

Hi Team,

Yes we do have valid licens and currently using PC SAS 9.2 our users are able to create XLSM , XLSX file but in EG we are not able to creat same. I just wanted to know specific reason for it.

PC file server installed on Window's box. PC file server works fine for us to create MDB and normal excel file. But its not support for XLSX adn XLSM files.

[RW9]

Just to check, I note that your exporting data to XSLX output destination, however appear to be talking about an XLSM file.  These are very subtley different file types.  XLSX is a zip file containing XML documents which reflect the data within the sheets.  XLSM are macro enabled (possibly with compiled VBA macro module), so I would not understand why you would want to create such as file from SAS.  The export to XLSX from SAS actually doesn't create an Excel XLSX file (unless your on the cutting edge of SAS releases), but a plain XML document which can be opened and interpreted by Excel.

So could you clarify what you are attempting to achieve.  Also, try using:
ods tagsets.excelxp file="abc.xls";

proc report data=...;

ods tagsets.excelxp close;

So the point is to create an output Excel file for review, as Excel is not a data transport format.  If you wanted to export to Excel to transfer data, then export to CSV or other delimited file.

[Reeza]

RW9 wrote:

 The export to XLSX from SAS actually doesn't create an Excel XLSX file (unless your on the cutting edge of SAS releases), but a plain XML documewhich can be opened and interpreted by Excel.

I think thats only with regards to tagsets, not proc export.

[RW9]

Yes, so long since used proc export.  You are quite correct. With the proc export dbms=xlsx it does create a zipped Open Office document.  Still not sure why he is trying to create XLSM though which are macro enabled zipped Open Office documents.

[Gordon_KeeperofMaps]

XLSM are macro enabled (possibly with compiled VBA macro module), so I would not understand why you would want to create such as file from SAS. 

I have a similar requirement. I do the heavy lifting in SAS and write the output of the process into an Excel (.xls) file where it drives a pivot table that in turn drives a bunch of dynamic data tables, which uses a custom VBA function to format the appearance of data values in cells. Recently, the output had more rows than a .xls file can handle, so I recreated the .xls in a .xlsx, which whinged about the VBA, so I used a .xlsm. When I attempted to write directory to the .xlsm from SAS using PROC EXPORT, I didn't have a lot of success. As a work around, I modified my pivot tables to externally reference a .xlsx that I create using SAS. It's a bit clunky because it means I have to have two files where I once had one (i.e. the .xlsx with the data and the .xlsm that does what I need it to do), but it's better than not being able to have my dynamic tables.

So, that's why I would like to be able to directly write into a .xlsm.

[Reeza]

For the record, you can write export directly to an XLSM - but I think you need SAS 9.3+.

Using Windows 7 and SAS 9.3 I use the libname method with out issues.

[Premier]

Reeza, do you happen to have sample code using the libname statement you could share that write exports direclty to an XLSM file? If so, it would be greatly appreciated.

[LinusH]

Make sure that your Excel file is not locked in any way, and that is not write protected.

45835 - A CLI error occurs when you try to establish a connection while importing Excel files with t...

[Doc_Duke]

Reddy,

You may want to repost this to the Enterprise Guide forum.  A lot of the responses you have gotten refer to writing code rather than the EGuide export task (and it is unclear which you used originally).  Also, you said you are using a PC Files server, but the error message indicates the server is on a Linux box.

[jakarman]

It is not SAS EG you should mention.

The port number 9621 and SERVER= 'T01LPS2021.ent.rt.com'   are that PC-files server on a Windows box.  That should be installed in 32 or 62 bit version but needing the same bitness office drivers (ACE) on that windows machine being installed and there should be an authentication part being active. That code is just normal SAS-Base could could be run with any kind of tool not only Eguide.

Saying this and you have problems with that concept it could be the deployment is not validated / not functional working. Who has done that installation and who is helping you?