Dim xls As New Excel.Application Dim xbook As New Excel.Workbook ' Dim xsheet As New Excel.Worksheet '建立一个EXCEL工作表对象 Set xbook = xls.Workbooks.Open("E:\yh\各分店订单统计.xls") '打开一个已经建立好的模板文件 Set xsheet = xbook.Worksheets(1) '将当前工作薄的第一个工作表赋给XSHEET对象,现在对Sheet1进行操作 'xls.Visible = True 'cn.ConnectionString = "Provider=oracledb.oracle;Data Source=oradb;user id=system;password=manager;" cn.Open Set rs = cn.Execute("select * from orders") xsheet.Columns("A:A").NumberFormatLocal = "@" '将第A列设置成文本型 i = 5 '输出数据从第5行开始 Do Until rs.EOF i = i + 1 For j = 0 To rs.Fields.Count - 1 xsheet.Cells(i, j + 1) = rs.Fields(j).Value Next j If (i - 3) / 20 = (i - 3) \ 20 Then '每20条记录为一页 SendKeys "^{end}" '向应用程序EXCEL发送一个组合键 CTRL+END,使光标移到文件尾 xsheet.HPageBreaks.Add before:=ActiveCell '添加一个分页符 End If rs.MoveNext Loop xsheet.SaveAs "e:\yh\" & Format(Now, "yyyy_mm_dd_hh_mm_ss") & "-第n分店.xls" xbook.Close
Dim xbook As New Excel.Workbook '
Dim xsheet As New Excel.Worksheet '建立一个EXCEL工作表对象
Set xbook = xls.Workbooks.Open("E:\yh\各分店订单统计.xls") '打开一个已经建立好的模板文件
Set xsheet = xbook.Worksheets(1) '将当前工作薄的第一个工作表赋给XSHEET对象,现在对Sheet1进行操作
'xls.Visible = True
'cn.ConnectionString = "Provider=oracledb.oracle;Data Source=oradb;user id=system;password=manager;"
cn.Open
Set rs = cn.Execute("select * from orders")
xsheet.Columns("A:A").NumberFormatLocal = "@" '将第A列设置成文本型
i = 5 '输出数据从第5行开始
Do Until rs.EOF
i = i + 1
For j = 0 To rs.Fields.Count - 1
xsheet.Cells(i, j + 1) = rs.Fields(j).Value
Next j
If (i - 3) / 20 = (i - 3) \ 20 Then '每20条记录为一页
SendKeys "^{end}" '向应用程序EXCEL发送一个组合键 CTRL+END,使光标移到文件尾
xsheet.HPageBreaks.Add before:=ActiveCell '添加一个分页符
End If
rs.MoveNext
Loop
xsheet.SaveAs "e:\yh\" & Format(Now, "yyyy_mm_dd_hh_mm_ss") & "-第n分店.xls"
xbook.Close