Reading a macro enabled Excel (XLSM) file.

[Dreamer]

Hi All,

I am trying to import a Macro enabled Excel file (xlsm).

I am using following code to do this:

PROC IMPORT DATAFILE='/home/Excelfile.xlsm'  
DBMS=xlsx replace
OUT=work.test ;
SHEET="Sheet1";
GETNAMES=YES;
RUN;

But it shows following error:
ERROR: XLSX file does not exist -> /home//Excelfile.xlsm.xlsx

I don't understand why its adding extra "/" and "xlsx" around the file name.

This code works fine for a "xlsx: file format.

I am using SAS 9.3 and EG 5.1

Can anyone help me in reading a xlsm file format?

Any help will be much appreciated.

Thanks in advance!!

[art297]

Have you tried simply changing the line that is currently: dbms=xlxs

to

dbms=excel

[Dreamer]

Yes, but also didn't work.

[Kurt_Bremser]

What keeps you from saving the data in a .xlsx, or even better, a .csv?

[Dreamer]

This file is delivered from a different source and I have only read access to it.

I can write VBA to save this file as XLSX to some different location and then point SAS to read it from there.

But before doint that first I want confirm that there is no way I can read a XLSM file through SAS.

[Kurt_Bremser]

It might be that the .xlsm format is only supported on Windows, as a VBA engine is needed to run the macros. And since even pre-ODF macros (Excel 4.0) are supported, I suspect that the .xlsm format may contain all kinds of (insert suitable 4-letter expletive here).

BTW, I don't see ANY reasson why a simple transfer of data needs macros. There seems to be a serious process design flaw here.