机房收费--收取金额查询

生成窗体

Form

Me.Width = 11520 ‘初始大小
Me.Height = 11520

查询

 Dim mrc As ADODB.Recordset
    Dim txtSQL As String
    Dim MsgText As String
    Dim date1 As String
    Dim date2 As String

'连接充值表
    txtSQL = "select * from recharge_info where date >='" & Trim(DTP1.Value) & "'and date<='" & Trim(DTP2.Value) & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)

    date1 = DTP1.Value
    date2 = DTP2.Value

    '比较两个日期的大小
    If DateDiff("n", CDate(date1), CDate(date2)) < 0 Then
        MsgBox "起始日期与终止日期有冲突，请重新选择日期！", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    End If

  With MSF1
        .Rows = 1
        .CellAlignment = 4    '不能为0 ，导致下面的为无效的行值
        .TextMatrix(0, 0) = "卡号"
        .TextMatrix(0, 1) = "充值金额"
        .TextMatrix(0, 2) = "充值日期"
        .TextMatrix(0, 3) = "充值时间"
        .TextMatrix(0, 4) = "充值教师"
        .TextMatrix(0, 5) = "结账状态"

        Do While mrc.EOF = False
            .Rows = .Rows + 1
            .CellAlignment = 4
            .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(2))
            .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3))
            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(4))
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(5) & "")
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(6) & "")
            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(8) & "")
            mrc.MoveNext   
        Loop
    End With

导出Excel

Dim i As Integer
    Dim j As Integer
    Dim xlapp As excel.Application                                              'Excel对象模型中最高层级的对象，代表Excel应用程序本身
    Dim xlbook As excel.Workbook                                                '设置xlbook为一个工作薄
    Dim xlsheet As excel.Worksheet                                              '设置xlsheet为一个工作簿的工作表

    If MSF1.Rows = 2 Then
        MsgBox "目前没有记录显示！", vbOKOnly + vbInformation, "提示"
        Exit Sub

    Else

        Set xlapp = CreateObject("Excel.Application")

        xlapp.Visible = True
        Set xlbook = xlapp.Workbooks.Add
        Set xlsheet = xlbook.Worksheets(1)

        For i = 0 To MSF1.Rows - 1
            For j = 0 To MSF1.Cols - 1
                MSF1.Row = i
                MSF1.Col = j
                xlsheet.Cells(i + 1, j + 1) = Trim(MSF1.Text)
            Next
        Next
    End If