将数据集插入到指定的sheet页,还有一种笨方法 可以sas和VBA混合使用.通过sas调用excel已经写好的VBA宏.
下面是我自己以前写的一个宏,可能代码不是很好看.但是能实现基本的功能.适用于html,csv文件.默认excel文件和要导入到excel的文件存放在同一目录下.excel中有事先写好的宏.
SAS代码:
%let excelpath=E:\DDE\sasdatatoexcel\;
/*excel文件所在路径注意最后面的'\'符号*/
%macro toxls(excelname,sheetname,filename);
/*变量说明:*/
/*excelname-要导入数据的excel文件名*/
/*sheetname-sheet名*/
/*要导入到excel的文件名*/
/*打开excel程序*/
options noxsync noxwait xmin;
filename sas2xl dde 'excel|system';
data _null_;
length fid rc start stop time 8;
fid = fopen('sas2xl','s');
if (fid le 0) then do;
rc = system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid= fopen('sas2xl','s');
time=datetime();
if (time ge stop) then fid =1;
end;
end;
rc=fclose(fid);
run;
/*将文件导入到指定excel文件名的指定sheet*/
filename excel DDE "EXCEL|SYSTEM";
filename export DDE "EXCEL|sheetname!r1c1:r100c100" notab;
data _null_;
file excel;
put "[open(""&excelpath.&filename"")]";
put "[open(""&excelpath.&excelname"")]";
put "[run(""base"")]";
run;
data _null_;
file export dlm='09'x;
put "&sheetname";
put "&excelname";
put "&filename";
run;
data _null_;
file excel;
put "[run(""toxls"")]";
put "[run(""deletesht"")]";
put "[save]";
run;
%mend;
VBA代码:
Function WorksheetExists(wb As Workbook, sName As String) As Boolean
Dim s As String
On Error GoTo ErrHandle
s = wb.Worksheets(sName).Name
WorksheetExists = True
Exit Function
ErrHandle:
WorksheetExists = False
End Function
Sub crtsht()
Dim shtname As String
shtname = Sheets("sheetname").Cells(1, 1)
If WorksheetExists(ThisWorkbook, shtname) = False Then
Sheets.Add.Name = shtname
Else
Application.DisplayAlerts = False
Sheets(shtname).Delete
Sheets.Add.Name = shtname
End If
End Sub
Sub base()
Dim str As String
str = "sheetname"
If WorksheetExists(ThisWorkbook, str) = False Then
Sheets.Add.Name = str
Else
Sheets(str).Select
End If
End Sub
Sub deletesht()
If WorksheetExists(ThisWorkbook, "sheetname") = True Then
Application.DisplayAlerts = False
Sheets("sheetname").Select
Cells.Select
Selection.Clear
Sheets("sheetname").Delete
End If
End Sub
Sub toxls()
Dim shtname As String
Dim excelname As String
Dim filename As String
shtname = Sheets("sheetname").Cells(1, 1)
excelname = Sheets("sheetname").Cells(2, 1)
filename = Sheets("sheetname").Cells(3, 1)
If WorksheetExists(ThisWorkbook, shtname) = False Then
Workbooks(filename).Sheets(shtname).Move After:=Workbooks(excelname).Sheets(ActiveWorkbook.Sheets.Count)
Else
Application.DisplayAlerts = False
Sheets(shtname).Delete
Workbooks(filename).Sheets(shtname).Move After:=Workbooks(excelname).Sheets(ActiveWorkbook.Sheets.Count)
End If
End Sub
|