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;
Click image to register for webinar
