VBA DAO访问类似于Excel 2010的数据库 [英] VBA DAO Accessing Excel 2010 like database

问题描述

我正在尝试使用DAO将一些VBA写入Excel2010。我希望能够像访问数据库一样访问EXCEL 2010工作簿。我正在尝试打开工作簿，而不是打开MDB文件。有没有办法将DAO用于Excel工作簿而不是实际的数据库？

Dim db As Database
Dim rst As Recordset
Dim SQL As String

SQL = "SELECT * From [DataSheet$]"

Set db = OpenDatabase(ThisWorkbook.FullName)
Set rst = db.OpenRecordset(SQL)

'displays the first record and first field
MsgBox rst.Fields(0)

'close the objects
rst.Close
db.Close

'destroy the variables
Set rst = Nothing
Set db = Nothing

我从这里借用了代码http://www.excel-spreadsheet.com/vba/dao_ado.htm

推荐答案

我解决了我的问题。使用下面的代码，您可以访问Excel文件并将其视为数据库。

Option Explicit

Private Sub btnConnect_Click()
  Dim dataConection As New ADODB.Connection
  Dim mrs As New ADODB.Recordset
  Dim SQL As String
  Dim DBPath As String
  Dim connectionString As String

  DBPath = ThisWorkbook.FullName 'Refering the sameworkbook as Data Source

  'You can provide the full path of your external file as shown below
  connectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

  'Open connection
  dataConection.Open connectionString

  'Create SQL query
  SQL = "SELECT * From [DataSheet$]"

  'Open record set (query or table, connection)
  mrs.Open SQL, dataConection

  Do While Not mrs.EOF
    Debug.Print "  " & mrs!Name
    mrs.MoveNext
  Loop

  mrs.Close

  'Close Connection
  dataConection.Close
End Sub

这篇关于VBA DAO访问类似于Excel 2010的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！