topic Re: Reading a macro enabled Excel (XLSM) file. in SAS Programming

Reading a macro enabled Excel (XLSM) file.

Dreamer — Wed, 16 Sep 2015 17:29:32 GMT

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!!

Re: Reading a macro enabled Excel (XLSM) file.

art297 — Wed, 16 Sep 2015 20:36:24 GMT

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

dbms=excel

Re: Reading a macro enabled Excel (XLSM) file.

Kurt_Bremser — Thu, 17 Sep 2015 06:05:31 GMT

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

Re: Reading a macro enabled Excel (XLSM) file.

Dreamer — Thu, 17 Sep 2015 16:54:33 GMT

Yes, but also didn't work.

Re: Reading a macro enabled Excel (XLSM) file.

Dreamer — Thu, 17 Sep 2015 16:57:57 GMT

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.

Re: Reading a macro enabled Excel (XLSM) file.

Kurt_Bremser — Fri, 18 Sep 2015 06:50:03 GMT

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.