关于SQL查出来的数据存到数组里

Evan1981

老师们好，我有个问题，我有如下代码
    MyConnect = "Provider=Microsoft.ace.oledb.12.0;" & _
                    "Data Source=" & sPath & sTemp & ";" & _
                    "Extended Properties = excel 12.0"
                  
        MySQL = "SELECT * FROM [sheet1$A1:R44]"
        Set MyRecordset = New ADODB.Recordset
      
        MyRecordset.Open MySQL, MyConnect ', adOpenStatic, adLockReadOnly
      
        Range("a1").CopyFromRecordset MyRecordset

问题是如何把MyRecordset 中的二维数据直接付给我的二维数组Arr，不是粘贴到单元格里再付给Arr, 我试了Arr=MyRecordset 好像不行，先谢谢了

abc123281

代码仅供参考  自己参照吧

1. Sub test()
   
2. Dim cnn As Object, rs As Object
   
3. Dim sql$
   
4. Dim arr, brr(1 To 10000, 1 To 18)
   
5. Set cnn = CreateObject("ADODB.Connection")
   
6. cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
   
7. sql = "SELECT * FROM [sheet1$A1:R44]"
   
8. Set rs = cnn.Execute(sql)
   
9. arr = rs.GetRows
   
10. For i = 0 To UBound(arr, 2)
    
11.   m = m + 1
    
12.   For j = 0 To 17
    
13.     brr(m, j + 1) = arr(j, i)
    
14.   Next
    
15. Next
    
16. [a1].Resize(m, 18) = brr
    
17. rs.Close
    
18. cnn.Close
    
19. Set rs = Nothing
    
20. Set cnn = Nothing
    
21. End Sub

复制代码

Evan1981

abc123281 发表于 2018-6-26 12:35
代码仅供参考  自己参照吧

嗯，我看看，谢谢老师！