SubReadDBData() On Error GoToErrorHand Dim dbHelper As NewdbHelper Dim sqlSQL As String Dim rs AsADODB.Recordset Dim row As Integer If dbHelper.OpenConnection(GetConnString()) Then sqlSQL = "select top(500) * from View_Column" Set rs =dbHelper.ExecuteRecordset(sqlSQL) Dim r Asrange 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 Asrange Set r = range(Sheet2.Cells(1, 1).Address(0, 0))
address(0,0)返回的是“F10”形式字符串。