Sub ReadDBData() On Error GoTo ErrorHand Dim dbHelper As New dbHelper Dim sqlSQL As String Dim rs As ADODB.Recordset Dim row As Integer If dbHelper.OpenConnection(GetConnString()) Then sqlSQL = "select top(500) * from View_Column" Set rs = dbHelper.ExecuteRecordset(sqlSQL) Dim r As range Set r = range(Sheet2.Cells(1, 1).Address(0, 0)) r.CopyFromRecordset rs ' row = 1 ' Do While Not rs.EOF ' For i = 1 To rs.Fields.Count Step 1 ' Sheet2.Cells(row, i).Value = rs.Fields(i - 1).Value ' Next i ' row = row + 1 ' rs.MoveNext ' Loop rs.Close End If ErrorHand: dbHelper.Dispose End Sub
一定要用r.CopyFromRecordset这个方法,可以瞬间将数据写入Excel。
如果用上面注释的代码的话,一个cell一个cell的赋值,要花2分钟左右。数据量也只有500条而已。效率天上地下,切忌切忌!
另外,cell转range的方法,是如下的样子
Dim r As range Set r = range(Sheet2.Cells(1, 1).Address(0, 0))
address(0,0)返回的是“F10”形式字符串。