DATA Step, Macro, Functions and more

Problem in export the data to .xlsm file

Reply
Frequent Contributor
Posts: 124

Problem in export the data to .xlsm file

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;

 

 

Frequent Contributor
Posts: 124

Re: Problem in export the data to .xlsm file

Anybody knows the solution...

Valued Guide
Posts: 505

Re: Problem in export the data to .xlsm file

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;
PROC Star
Posts: 7,363

Re: Problem in export the data to .xlsm file

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

Ask a Question
Discussion stats
  • 3 replies
  • 266 views
  • 1 like
  • 3 in conversation