7,763
社区成员
发帖
与我相关
我的任务
分享
Private Sub Command1_Click()
Dim iRow, iCol, iRowCount, iColCount As Integer
Dim sSource, sDestination, sRange As String
Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet
sSource = App.Path & "\缴费表打印模板.xls"
sDestination = App.Path & "\temp.xls"
FileCopy sSource, sDestination
'将模板文件拷贝到一个临时文件
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
'隐藏Excel应用程序窗口
ExcelApp.Caption = "缴费情况表打印"
Set ExcelBook = ExcelApp.Workbooks.Open(sDestination)
Set ExcelSheet = ExcelBook.Worksheets(1)
With Adodc1.Recordset
.MoveLast
If .RecordCount < 1 Then
MsgBox ("Error 没有记录!")
Exit Sub
End If
iRowCount = .RecordCount '记录总数
iColCount = .Fields.Count '字段总数
If iRowCount > 2 Then
ExcelSheet.Range("A6").Select
For iRow = iRowCount To 3 Step -1 '递减循环,step步长-1
'模板中已有两行
ExcelApp.Selection.EntireRow.Insert
Next
sRange = "E5:E" & LTrim(Str(iRowCount + 4))
ExcelSheet.Range("E5").Select
ExcelApp.Selection.AutoFill Destination:=Range(sRange), Type:=x1FillDefault
sRange = "F5:F" & LTrim(Str(iRowCount + 4))
ExcelSheet.Range("F5").Select
ExcelApp.Selection.AutoFill Destination:=Range(sRange), Type:=xlFillDefault
sRange = "G5:G" & LTrim(Str(iRowCount + 4))
ExcelSheet.Range("G5").Select
ExcelApp.Selection.AutoFill Destination:=Range(sRange), Type:=xlFillDefault
End If
.MoveFirst
For iRow = 1 To iRowCount
For iCol = 1 To iColCount
ExcelSheet.Cells(iRow + 4, iCol).Value = .Fields(iCol - 1)
Next
If Not .EOF Then .MoveNext
Next
End With
ExcelApp.Visible = True
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set ExcelApp = Nothing
MsgBox ("导出成功!")
End Sub
Private Sub Command1_Click()
Dim ExcelApp As New Excel.Application
Dim ExcelBook As New Excel.Workbook
Dim ExcelSheet As New Excel.Worksheet
Dim iRow, iCol, iRowCount, iColCount As Integer
Dim sSource, sDestination, sRange As String
Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet
sSource = App.Path & "\缴费表打印模板.xls"
sDestination = App.Path & "\temp.xls"
FileCopy sSource, sDestination
'将模板文件拷贝到一个临时文件
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
'隐藏Excel应用程序窗口
ExcelApp.Caption = "缴费情况表打印"
Set ExcelBook = ExcelApp.Workbooks.Open(sDestination)
Set ExcelSheet = ExcelBook.Worksheets(1)
With Adodc1.Recordset
.MoveLast
If .RecordCount < 1 Then
MsgBox ("Error 没有记录!")
Exit Sub
End If
iRowCount = .RecordCount '记录总数
iColCount = .Fields.Count '字段总数
If iRowCount > 2 Then
ExcelSheet.Range("A6").Select
For iRow = iRowCount To 3 Step -1 '递减循环,step步长-1
'模板中已有两行
ExcelApp.Selection.EntireRow.Insert
Next
sRange = "E5:E" & LTrim(Str(iRowCount + 4))
ExcelSheet.Range("E5").Select
ExcelApp.Selection.AutoFill Destination:=Range(sRange), Type:=x1FillDefault
sRange = "F5:F" & LTrim(Str(iRowCount + 4))
ExcelSheet.Range("F5").Select
ExcelApp.Selection.AutoFill Destination:=Range(sRange), Type:=xlFillDefault
sRange = "G5:G" & LTrim(Str(iRowCount + 4))
ExcelSheet.Range("G5").Select
ExcelApp.Selection.AutoFill Destination:=Range(sRange), Type:=xlFillDefault
End If
.MoveFirst
For iRow = 1 To iRowCount
For iCol = 1 To iColCount
ExcelSheet.Cells(iRow + 4, iCol).Value = .Fields(iCol - 1)
Next
If Not .EOF Then .MoveNext
Next
End With
ExcelApp.Visible = True
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set ExcelApp = Nothing
MsgBox ("导出成功!")
End Sub