Problem in export the data to .xlsm file

pp2014 — Wed, 29 Mar 2017 16:16:29 GMT

I am using following export procedure to export data to .xlsm extention file. It does the export but creates new sheet instead of replacing the old one. It creates sheet called output1. Can anybody help me with this?

Below is the code:

proc export
data=extract
outfile="C:\temp\testXYZ.xlsm"
dbms=excel2000 replace ;
sheet='output';
run;

Re: Problem in export the data to .xlsm file

pp2014 — Wed, 29 Mar 2017 17:03:54 GMT

Anybody knows the solution...

Re: Problem in export the data to .xlsm file

rogerjdeangelis — Wed, 29 Mar 2017 19:21:41 GMT

Replace sheet 'class' in a macro enabled (xlsm) workbook

This just links back to this message - I cross posied to SAS-L
inspired by
https://goo.gl/pACrZt
https://communities.sas.com/t5/Base-SAS-Programming/Problem-in-export-the-data-to-xlsm-file/m-p/345438

I could not do this with R, so I swithched to Python.

As a side note Puthon Win32com and RDCOM may eliminate
the need to DDE?

HAVE macro enabled excel 2010 64bit  workbook d:/xls/classcop.xlsm
==================================================================

MALES ONLY SASHELP.CLASS in the XLSM WORKBOOK

  +----------------------------------------------------------------+
  |     A      |    B       |     C      |    D       |    E       |
  +----------------------------------------------------------------+
1 | NAME       |   SEX      |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+
2 | ALFRED     |    M       |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+------------+
N | WILLIAM    |    M       |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+------------+

[class]

* manually add this VBA macro;
/*
Sub AddTicks()
  Dim LastPlace, Z As Variant, X As Variant
  LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
  ActiveSheet.Range(Cells(1, 1), LastPlace).Select
  Z = Selection.Address   'Get the address
      For Each X In ActiveSheet.Range(Z)  'Do while
          If Len(X) > 0 Then      'Find cells with something
              X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X))  '39 is code for tick
          Else
              X.FormulaR1C1 = ""  'If empty do not put tick
          End If
          If X = "TIUQ" Then
              Exit Sub
          End If
      Next
End Sub
*/


WANT  replace sheet class with 'Females only SASHELP.CLASS" and keep VBA code
============================================================================

REPLACE WIT FEMALES ONLY

  +----------------------------------------------------------------+
  |     A      |    B       |     C      |    D       |    E       |
  +----------------------------------------------------------------+
1 | NAME       |   SEX      |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+
2 | ALICE      |    F       |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+------------+
N | MARY       |    F       |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+------------+

[class]

WORKING CODE (note keep_vba option)
====================================

    wb = load_workbook(filename='d:/xls/classcop.xlsm', read_only=False, keep_vba=True);

    libname sd1 "d:/sd1";
    call missing(of _all_);
    output xel.class;

    manually add a VBA macro and save as macro enabled d:/xls/class.xlsm


FULL SOLUTION
==============

* delete dataset;
proc datasets lib=sd1;
delete class;
run;quit;

* delete spreadsheets;
* create dataset and xlsx file;
%let fyl=d:/xls/class.xlsx;
%utlfkil(&fyl); * delete if exist;

* create empty workbook with males only SASHELP.CLASS;
libname xel "&fyl";

options validvarname=upcase;
libname sd1 "d:/sd1";

data xel.class;
  set sashelp.class(where=(sex='M'));
run;quit;

libname xel clear;

* manually add a VBA macro and save as macro enabled d:/xls/class.xlsm;
/*
Sub AddTicks()
  Dim LastPlace, Z As Variant, X As Variant
  LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
  ActiveSheet.Range(Cells(1, 1), LastPlace).Select
  Z = Selection.Address   'Get the address
      For Each X In ActiveSheet.Range(Z)  'Do while
          If Len(X) > 0 Then      'Find cells with something
              X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X))  '39 is code for tick
          Else
              X.FormulaR1C1 = ""  'If empty do not put tick
          End If
          If X = "TIUQ" Then
              Exit Sub
          End If
      Next
End Sub
*/


* copy the macro enabled workbook and update the copy(backup VBA code);
%bincop(in=d:/xls/class.xlsm,out=d:/xls/classcop.xlsm);

/*
You can do this by hand
*/

* create sashelp.class with females only;
libname sd1 "d:/sd1";
data sd1.classf;
  set sashelp.class(where=(sex='F'));
run;quit;

*            _   _
 _ __  _   _| |_| |__   ___  _ __
| '_ \| | | | __| '_ \ / _ \| '_ \
| |_) | |_| | |_| | | | (_) | | | |
| .__/ \__, |\__|_| |_|\___/|_| |_|
|_|    |___/
;

%utl_submit_py64old("
from openpyxl.utils.dataframe import dataframe_to_rows;
from openpyxl import Workbook;
from openpyxl import load_workbook;
from sas7bdat import SAS7BDAT;
with SAS7BDAT('d:/sd1/classf.sas7bdat') as m:;
.   clas = m.to_data_frame();
print(clas);
wb = load_workbook(filename='d:/xls/classcop.xlsm', read_only=False, keep_vba=True);
ws = wb.get_sheet_by_name('class');
rows = dataframe_to_rows(clas);
for r_idx, row in enumerate(rows, 1):;
.   for c_idx, value in enumerate(row, 1):;
.        ws.cell(row=r_idx, column=c_idx, value=value);
wb.save('d:/xls/classcop.xlsm');
");

*
 _ __ ___   __ _  ___ _ __ ___  ___
| '_ ` _ \ / _` |/ __| '__/ _ \/ __|
| | | | | | (_| | (__| | | (_) \__ \
|_| |_| |_|\__,_|\___|_|  \___/|___/

;

* macros;

%macro utl_submit_py64old(pgm)/des="Semi colon separated set of py commands";
  * write the program to a temporary file;
  filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
  data _null_;
    length pgm  $32755 cmd $1024;
    file py_pgm ;
    pgm=&pgm;
    semi=countc(pgm,';');
      do idx=1 to semi;
        cmd=cats(scan(pgm,idx,';'));
        if cmd=:'.' then cmd=substr(cmd,2);
        put cmd $char96.;
        putlog cmd $char96.;
      end;
  run;

  run;quit;
  %let _loc=%sysfunc(pathname(py_pgm));
  %put &_loc;
  filename rut pipe  "C:\Python_27_64bit/python.exe &_loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;
  filename rut clear;
  filename py_pgm clear;
%mend utl_submit_py64;


%macro BinCop(
   in=\\filesrv04\stat\peggcsf\sd01\meta\datamart\bonepain_2008\docs\rogerdeangelis\bne.xls
   ,out=c:\pis\bne.xls
  ) / des="Copy a binary file";
data _null_;
  infile "&in" recfm=n;
  file "&out" recfm=n;
  input byt $char1. @@;
  put byt $char1. @@;
run;
%mend BinCop;

Re: Problem in export the data to .xlsm file

art297 — Wed, 29 Mar 2017 19:22:54 GMT

Have you tried it with the

newfile=yes;

statement?

It also might work if you change the dbms to XLSX. However, in that case, I don't think it supports the newfile=yes statement, but might do what you want.

Art, CEO, AnalystFinder.com

topic Problem in export the data to .xlsm file in SAS Programming

Problem in export the data to .xlsm file

Re: Problem in export the data to .xlsm file

Re: Problem in export the data to .xlsm file

Re: Problem in export the data to .xlsm file