Reading a macro enabled Excel (XLSM) file.

Reply
Frequent Contributor
Posts: 89

Reading a macro enabled Excel (XLSM) file.

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

Esteemed Advisor
Posts: 7,302

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

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

to

dbms=excel

 

Frequent Contributor
Posts: 89

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

Yes, but also didn't work.

Esteemed Advisor
Posts: 6,737

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 89

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

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.

Esteemed Advisor
Posts: 6,737

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

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.

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 1591 views
  • 1 like
  • 3 in conversation