BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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;

 

 

3 REPLIES 3
pp2014
Fluorite | Level 6

Anybody knows the solution...

rogerjdeangelis
Barite | Level 11
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;
art297
Opal | Level 21

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2317 views
  • 1 like
  • 3 in conversation