vba oledb读取Excel数据

引用方法1

ADODB Set cnn = CreateObject(“ADODB.Connection”)

引用方法2

引用ADO相关组件：打开VBA编辑器，在菜单中点选“工具”—》“引用”。确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾选上。引用后再声明:

Dim cnn As New Connection  '声明链接对象
Dim rst As New Recordset       '声明记录集对象

参考

• https://blog.csdn.net/iamlaosong/article/details/18043433
• https://blog.csdn.net/haijs/article/details/52437888?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2

Sub test_oledb()
    Dim objconn As ADODB.Connection Dim objrs As ADODB.Recordset Set objconn = New ADODB.Connection objconn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=no';data source=C:\temp1\123.xlsb"
    stName = "Reconciliation" Dim sql As String sql = "SELECT f4,f5,f6,f7,f8 FROM [" & stName & "$]"      Dim rs As Object   Set rs = CreateObject("ADODB.Recordset")
    Set rs = objconn.Execute(sql)   While Not rs.EOF       Debug.Print rs(1) & "|" & rs(2)       rs.MoveNext   Wend  'wend  while the complete
    'copy top 10 rows   shtest.Range("a1").CopyFromRecordset objconn.Execute(sql), 10      objconn.Close      Set objconn = Nothing      Application.ScreenUpdating = True
End Sub