请教怎么在access中用VBA导入excel数据到access库

方法一：在eccel VBA编辑器编写代码，使用FOR循环导入：

Sub 利用Excel的VBA将数据写入Access()

'定义ADODB 连接Access数据库

Dim Cnn As New ADODBConnection

Dim Rs As New ADODBRecordset

Dim strCon As String

Dim strFileName As String '数据库文件名

strFileName = InputBox("请输入文件路径及文件名：", "Excel传递数据至Access", "E:\ExcelTest\Staffmdb")

'连接字符串，我这里是Access2010,所提供者参数为：Provider=MicrosoftACEOLEDB120;

'如果此参数不能使用，根据自己的电脑修改这个字符串

'至于连接至的数据库名，根据自己的电脑修改，或用一个对话框或其他方式将此值传递进程序

strCon = "provider=MicrosoftjetOLEDB40;" _

& "Data Source=" & strFileName & ";"

CnnOpen strCon

RsActiveConnection = Cnn

RsLockType = adLockOptimistic

RsOpen "Employee" '假设表为Employee

'定义Excel表中的数据区域以写入Access

Dim Sht As Worksheet

Dim Rn As Long

Dim Cn As Long

Set Sht = ThisWorkbookSheets("Sheet1")

'假设将 Sheet1 表的 2-6行的1、2、3列写入Access表

For Rn = 2 To 6

RsAddNew

Rs!num = ShtCells(Rn, 1) ’num,name,department是数据库中指定表的字段

Rs!Name = ShtCells(Rn, 2)

Rs!department = ShtCells(Rn, 3)

RsUpdate

Next Rn

MsgBox "完成!"

RsClose

CnnClose

Set Rs = Nothing

Set Cnn = Nothing

Set Sht = Nothing

End Sub

方法二：在access VBA编辑器中编写代码

Sub ExcelToAccess()

DoCmdTransferSpreadsheet acImport, , "Staff", "E:\ExcelTest\Employeexls", True, "Sheet1!" ‘DoCmdTransferSpreadsheet是access编辑器中函数

End Sub

可以使用宏来实现，给你一个简单的例子。

Sub adoTest()

Dim adoRt As Object

Dim strSQL As String

'// 创建ADO记录集对象

Set adoRt = CreateObject("ADODBRecordSet")

'// 设置SQL 语句

strSQL = "SELECT FROM uTemp WHERE False"

With adoRt

'// 设置需要连接数据库

ActiveConnection = "Provider=MicrosoftJetOLEDB40;Data Source=D:\栽培者\程序\打印类\测试\printTestmdb"

CursorLocation = 3

CursorType = 1

LockType = 3

'// 设置SQL语句

Source = strSQL

'// 准备打开记录集

Open

'// 新增记录

AddNew

'// 赋值

Fields("Name")Value = Range("A1")Value

Fields("Sex")Value = Range("B1")Value

Fields("Age")Value = Range("C1")Value

'// 更新

Update

'// 判断并关闭记录

If State = 1 Then

Close

End If

End With

'// 从内存中释放

Set adoRt = Nothing

End Sub

一、导入方法：

Sub 把Excel数据插入数据库中()

Dim conn As ADODBConnection

Dim WN As String

Dim TableName As String

Dim sSql As String

Dim tStr As String

'数据库名，请自行修改，路径与当前工作簿在同一目录

WN = "进销存表mdb"

'数据库的表名与当前工作表名一致

TableName = ActiveSheetName

Set conn = New ADODBConnection

connConnectionString = "Provider=MicrosoftJetOledb40;" & _

"Extended Properties=Excel 80;" & _

"Data Source=" & ThisWorkbookPath & "\" & ActiveWorkbookName

connOpen

If connState = adStateOpen Then

sSql = "Insert Into [;DataBase=" & ActiveWorkbookPath & "\" & WN & "]" & TableName & " Select From [" & ActiveSheetName & "$]"

connExecute sSql

MsgBox "成功把数据插入到“" & TableName & "”中！", , ">

你数据对不上是少数据吗你的语句中用了like,但没有通配符,试试修改

sqls = "SELECT FROM NDYX where FIELD(0) like'" & a & "'" '定义SQL查询命令字符串"

第一步：连接数据库，下面是参考代码

Set conn = CreateObject("ADODBConnection")

connConnectionString = "Provider=MicrosoftAceOleDB120;Data Source=" & mypath

connConnectionString = connConnectionString & ";Jet OLEDB:Database Password=1"

connOpen

第二步：插入数据，参考代码如下：

Sql = "insert into skmxb (内部管理号,收款日期,金额"

Sql = Sql & ") values ('" & nbglH & "', #" & Format(myD, "yyyy/mm/dd") & "#,"

Sql = Sql & jE

Sql = Sql & ")"

connExecute Sql

以上就是关于请教怎么在access中用VBA导入excel数据到access库全部的内容，包括:请教怎么在access中用VBA导入excel数据到access库、我想请教各位高手一个有关EXCEL借助VBA与入ACCESS数据库的问题、EXCEL VBA 打开Access数据库文件等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！