SAS/Python: Updating excel macro enabled workbooks
I don't have SAS 32 or 16bit windows available.
Win 7 64bit
Excel 2010 64bit
SAS 64bit
Pythin 2.7 - openpyxl 2.4
I think only Python can do this? Not SAS, R or Perl?
Recent WPS has an interface to Python can do this. I don't have it.
HAVE macro enabled excel 2010 64bit workbook d:/xls/classcop.xlsm
==================================================================
Only one empty sheet in
+----------------------------------------------------------------+
| A | B | C | C | ... |
-----------------------------------------------------------------+
1 | | | | | |
+------------+------------+------------+------------+------------+
2 | | | | | |
+------------+------------+------------+------------+------------+
...
+------------+------------+------------+------------+------------+
. | ... | ... | ... | ... | ... |
+------------+------------+------------+------------+------------+
WANT update sheet class with sashelp.class
===========================================
+----------------------------------------------------------------+
| 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]
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
==============
proc datsets lib=sd1;
delete class;
run;quit;
%let fyl=d:/xls/class.xlsx;
%utlfkil(&fyl);
libname xel "&fyl";
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.class xel.class;
set sashelp.class;
output sd1.class;
call missing(of _all_);
output xel.class;
run;quit;
proc sql;
drop table xel.class
;quit;
libname xel clear;
/*
Up to 40 obs from sd1.class total obs=19
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Barbara F 13 65.3 98.0
4 Carol F 14 62.8 102.5
d:/xls/class.xlsx
+----------------------------------------------------------------+
| A | B | C | C | ... |
-----------------------------------------------------------------+
1 | | | | | |
+------------+------------+------------+------------+------------+
[CLASS]
*/
* 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
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file "d:/xls/classcop.xlsm" is:
Filename=d:\xls\classcop.xlsm,
*/
* _ _
_ __ _ _| |_| |__ ___ _ __
| '_ \| | | | __| '_ \ / _ \| '_ \
| |_) | |_| | |_| | | | (_) | | | |
| .__/ \__, |\__|_| |_|\___/|_| |_|
|_| |___/
;
%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/class.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;
