[Excel VBA]从Excel选择范围并插入Access数据库 [英] [Excel VBA]Select range from Excel and insert into Access database

问题描述

我有一个用Excel构建的表，稍后(在操作之后)将其粘贴到Access表中.它适用于Windows中的复制粘贴，但是我想自动化该过程.我们正在谈论大量的列(A:AY)和大量的记录(10.000).我已经看到它是使用ADO记录集完成的，但只能逐行执行.有批量方法吗?

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?

`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

` 因此，基本上，我需要的是:从工作表中指定一个范围，然后将其插入Access表中.任何帮助是极大的赞赏. 干杯

` 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

推荐答案

一种方法(未经测试)可能是在Excel项目中使用Access对象. DoCmd.TransferSpreadsheet 是在Access中快速导入Excel文件的方法. 您可以通过引用 Microsoft Access Object XX.0 (XX.0是系统中安装的版本)来使用此功能. 然后，在代码中您将得到以下内容:

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:

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

有关该功能的详细信息 DoCmd. TransferSpreadSheet

Details about the function DoCmd.TransferSpreadSheet

这篇关于[Excel VBA]从Excel选择范围并插入Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！