从SQL数据库导出到Excel(二)
Next i
mrc.MoveFirst
xlsheet1.Range("A2").CopyFromRecordset mrc
mrc.Close
Set mrc = Nothing
xlapp1.Visible = True
Set xlapp1 = Nothing
End Sub
'查询某段时间内的充值记录 www.2cto.com
Private Sub cmdQuery_Click()
If DTPStart.Value > DTPEnd.Value Then
MsgBox "请重新选择日期!", vbOKOnly + vbExclamation, "提示"
DTPStart.SetFocus
Exit Sub
End If
strSQL = "select cardno 卡号,addcash 充值金额,date 充值日期,time 充值时间,userid 充值教师 from recharge_info where date>'" & DTPStart.Value & " '" & "and date<'" & DTPEnd.Value & "'"
Set mrc = ExecuteSQL(strSQL, MsgText)
With myMSFlexGrid
.Rows = 1
.CellAlignment = 4
Dim k As Integer
For k = 0 To mrc.Fields.Count - 1
.TextMatrix(0, k) = mrc.Fields(k).Name
.ColWidth(k) = 1300
Next k
Do While mrc.EOF = False
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 0) = mrc.Fields("卡号").Value
.TextMatrix(.Rows - 1, 1) = mrc.Fields("充值金额").Value
.TextMatrix(.Rows - 1, 2) = mrc.Fields("充值日期").Value
.TextMatrix(.Rows - 1, 3) = mrc.Fields("充值时间").Value
.TextMatrix(.Rows - 1, 4) = mrc.Fields("充值教师").Value
mrc.MoveNext
Loop
End With
End Sub
www.2cto.com
我们可以用一个循环的嵌套来实现导入的过程,过程如下
Dim m As Integer
Dim n As Integer
Do While mrc.EOF = False
For m = 0 To mrc.RecordCount - 1
For n = 0 To mrc.Fields.Count - 1
xlsheet1.Cells(m + 2, n +1).Value = mrc.Fields(n).Value
Next n
Next m
mrc.MoveNext
Loop www.2cto.com
而上面我只用了一句代码: xlsheet1.Range("A2").CopyFromRecordset mrc,他们的作用完全相同,只是后者省去了写代码的时间,更重要的是我们再也不用在m + 2, n + 1等易错地方纠结了.