[Excel VBA]从Excel中选择范围并插入到Access数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26906778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
[Excel VBA]Select range from Excel and insert into Access database
提问by John Bonham
I have a table constructed in Excel to be pasted later(after manipulation) into an Access table. It works with copy-paste in Windows, but I want to automate the process. We are talking about a large number of columns(A:AY) and a large number of records(10.000). I've seen it done with ADO recordset but only row by row. Is there a bulk method?
我有一个在 Excel 中构建的表，稍后（操作后）将其粘贴到 Access 表中。它适用于 Windows 中的复制粘贴，但我想自动化该过程。我们正在谈论大量列（A：AY）和大量记录（10.000）。我已经看到它是用 ADO 记录集完成的，但只是逐行完成的。有批量方法吗？
`Public Sub Test()
Dim connDB As New ADODB.Connection
'Dim rng As Range
strDBName = "Kiian.mdb"
strMyPath = "d:\Work\kiian"
strDB = strMyPath & "\" & strDBName
Dim xlXML As Object
Dim adoRecordset As Object
Dim rng As Range
'this is a trick I found on the boards to easily create a recordset from range
'without a connection, but it creates an object, not a recordset
Sheets("mdb all").Activate
Set rng = Range("A1:ay3")
Set adoRecordset = CreateObject("ADODB.Recordset")
Set xlXML = CreateObject("MSXML2.DOMDocument")
xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
adoRecordset.Open xlXML
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
'delete records in the PVAnag Table:
strSQL = "INSERT INTO PVAnag SELECT * FROM adoRecordset"
'connDB.Execute CommandText:=strSQL
connDB.Execute strSQL, nr
MsgBox (nr)
'Sheets("mdb_all").Range("A1:AY3").CopyFromRecordset rstData
'close the objects
connDB.Close
'destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing
End Sub
` So basically, what i need is this: specify a range from a sheet and block insert that into an Access table. Any help is greatly appreciated. Cheers
` 所以基本上，我需要的是：从工作表中指定一个范围并阻止将其插入到 Access 表中。任何帮助是极大的赞赏。干杯
采纳答案by CynicalSection
A method (that I haven't tested) it could be to use Access objects in your Excel project. A very fast to import Excel files in Access is DoCmd.TransferSpreadsheet. You can have use this by referencing Microsoft Access Object XX.0(XX.0 is the version installed in your system). Then, in code you have something like:
一种方法（我尚未测试过）可能是在 Excel 项目中使用 Access 对象。在 Access 中非常快速地导入 Excel 文件是DoCmd.TransferSpreadsheet。您可以通过引用Microsoft Access Object XX.0（XX.0 是您系统中安装的版本）来使用它。然后，在代码中你有类似的东西：
Dim db AS Access.Application
Set db = new Access.Application
db.OpenCurrentDatabase filepath="D:\Database1.accdb", Exclusive:=True
db.DoCmd.TransferSpreadsheet 'all_params_here
db.CloseCurrentDatabase
db.Quit acQuitSaveAll
Details about the function DoCmd.TransferSpreadSheet
有关DoCmd.TransferSpreadSheet函数的详细信息
回答by John Bonham
After banging my head for a couple of hours trying to use TransferSpreadsheet, i found a clue on another forum and I came up with this:
在尝试使用 TransferSpreadsheet 敲了几个小时后，我在另一个论坛上找到了一个线索，我想出了这个：
Public Sub a()
Sheets("mdb all").Select
Range("a1:ay3").Select
Selection.Copy
strDBName = "Kiian.mdb"
strMyPath = "d:\Work\kiian"
strDB = strMyPath & "\" & strDBName
Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
appAccess.Visible = True
appAccess.DoCmd.OpenTable "PVAnag", acViewNormal, acEdit
appAccess.DoCmd.RunCommand acCmdPasteAppend
appAccess.CloseCurrentDatabase
appAccess.Quit acQuitSaveAll
End Sub
This method uses the clipboard and just copies from Excel, opens Access and pastes(special=append). For whatever reason, I just couldn't get TransferSpreadsheet to work. But thanks for the input and for getting me off my idea to use SQL.
此方法使用剪贴板并仅从 Excel 复制，打开 Access 并粘贴（特殊 = 附加）。无论出于何种原因，我都无法让 TransferSpreadsheet 工作。但是感谢您的输入并让我放弃使用 SQL 的想法。
