将Excel表格作为DAO记录集打开以进行追加 [英] Open Excel table as DAO RecordSet for Appending
本文介绍了将Excel表格作为DAO记录集打开以进行追加的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试从DAO更新Excel文件中的表,就好像它是一个数据库表一样。听起来应该可以,但我找不到任何有关使用DAO打开Excel的文档。
我打开文件并获得记录集句柄,但看到错误3027"无法更新。数据库或对象是只读的。"当我尝试添加到记录集中时。问题的一部分是,我可以将工作表视为Tabledef,但无法在该工作表中找到作为对象的EXCEL表格。可能我不知道将表作为记录集打开的正确语法。我使用的代码指定了dbOpendyaset,就像您将Access表设置为可写一样。我正在尝试的东西有可能实现吗?
错误发生在".AddNew":
Const dbOpenDynaset As Long = 2
Const dbAppendOnly As Long = 8
Const dbOptimistic As Long = 3
Public Sub OpenExcelAsDB(ByVal excelFile As String)
Dim fileExtension As String
fileExtension = Right$(excelFile, Len(excelFile) - InStrRev(excelFile, "."))
Dim connectionString As String
Select Case fileExtension
Case "xls"
connectionString = "Excel 8.0;HDR=YES;IMEX=1"
Case "xlsx"
connectionString = "Excel 12.0 Xml;HDR=YES;IMEX=1"
Case "xlsb"
connectionString = "Excel 12.0;HDR=YES;IMEX=1"
Case "xlsm"
connectionString = "Excel 12.0 Macro;HDR=YES;IMEX=1"
Case Else
connectionString = "Excel 8.0;HDR=Yes;IMEX=1"
End Select
With CreateObject("DAO.DBEngine.120")
With .OpenDatabase(excelFile, False, False, connectionString)
With .OpenRecordset("LogSheet$", dbOpenDynaset, dbAppendOnly, dbOptimistic)
.AddNew
With .Fields()
.Item("errorNumber").Value = errorNumber
.Item("errorDescription").Value = errorDescription
.Item("customNote").Value = customNote
.Item("errorDate").Value = Now()
.Item("Username").Value = UserLogon
.Item("Computer").Value = ComputerName
End With
.Update
.Close
End With
.Close
End With
End With
End Sub
推荐答案
此时此刻,这似乎是一个相当古老的技术堆栈。我认为这大约是在20世纪90年代初,而且肯定仍然存在,但支持似乎正在减少。因此,有了这个警告,您可以尝试这样的操作。
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:FolderNameDataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
就我个人而言,我会避开这些事情,专注于某种云技术堆栈。
这篇关于将Excel表格作为DAO记录集打开以进行追加的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文