如何在<em>EXCEL</em>中自动读取ACCESS数据库中的信息？-excel 引用acc-乔山办公网

作者：乔山办公网日期：2021-01-24 21:36:42

返回目录：excel表格制作

用access自动获取excel里的数据的方法就是通过代码的方式导入。如果是对手动导入比较清楚的话，这个实现起来不难。请参考下面的介绍来实现：
首先，新建一个工程，在工程中引用如下对象ADO对象（用于连接ACCESS数据库，可用其它方式）
添加一个窗体（from1），在窗体上添加如下控件：
两个文本框，用来显示EXCEL文件路径和ACCESS路径；
四个扭钮，两个用来游览，另两个是e69da5e6ba907a686964616f331导入和退出；
两个通用对话框控件，用来打开ACCESS和EXCEL文件，一个进度条控件，用来显示导入进程。
该
实例的导入是将ACCESS数据库中表的字段名单独存放在另外一个表中,导入时按表中所存字段名的顺序进行导入,不是按EXCEL表的顺序导入,请大家注
意.这样在实际中是很实用的.因为好多时候EXCEL表中字段顺序和ACCESS中字段顺序有可能不是一致的.

代码如下：
Dim v
Option Explicit
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
If txtExcelFile.Text = "" Then
MsgBox "请选择EXCEL表"
Else
Dim new_value As String
Label2.Caption = "正在导入,请稍候..."
Screen.MousePointer = vbHourglass
DoEvents
'' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
'' Uncomment this line to make Excel visible.
excel_app.Visible = True
'' Open the Excel spreadsheet.
excel_app.Workbooks.open FileName:=txtExcelFile.Text
'' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app

End If

Dim u ''求EXCEL表中记录的条数,以便控制进度条
u = 1
Do
If Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Do
u = u + 1
Loop
bar.Max = u - 1

strSQL = "select * from TestValues"
yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ''打开记录集
Dim sql As String

sql = "select * from fields order by xue"
myRecord.open sql, myConn, adOpenDynamic, adLockBatchOptimistic ''打开字段记录集
myRecord.MoveFirst

'' Get data from the Excel spreadsheet and insert
'' it into the TestValues table.

Dim v ''导入记录,用了两层循环
v = 1
Do
If Trim$(excel_sheet.Cells(v, 1)) = "" Then Exit Do ''外层,如果EXCEL表中读取到空行,结束

yourRecord.AddNew

Dim i
For i = 1 To myRecord.RecordCount
'' Get the next value.
new_value = Trim$(excel_sheet.Cells(v, i))
'' See if it''s blank.
''If Len(new_value) = 0 Then Exit Do

'' Insert the value into the database.

Dim bb As String

bb = myRecord("name")

yourRecord(bb) = new_value

myRecord.MoveNext

Next i
bar.Value = v
v = v + 1
myRecord.MoveFirst
Loop
yourRecord.Update

'' Comment the rest of the lines to keep
'' Excel running so you can see it.
'' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
'' Close Excel.
excel_app.Quit
Set excel_sheet = Nothing
Set excel_app = Nothing
myRecord.Close
yourRecord.Close
Set myRecord = Nothing
Set yourRecord = Nothing
Label2.Caption = "导入完毕"
Screen.MousePointer = vbDefault
MsgBox "共导入" & Format$(v - 1) & "条记录"

End If
End Sub
Private Sub Command1_Click()
Unload Me
End Sub
Private Sub Command2_Click(Index As Integer)
''寻找ACCESS数据库
CommonDialog1.Filter = "ACCESS 文件(*.mdb)|*.mdb"
CommonDialog1.CancelError = True
CommonDialog1.ShowOpen
txtAccessFile.Text = CommonDialog1.FileName
End Sub
Private Sub Command3_Click()
''寻找excel数据库
CommonDialog2.Filter = "excel 文件(*.xls)|*.xls"
CommonDialog2.CancelError = True
CommonDialog2.ShowOpen
txtExcelFile.Text = CommonDialog2.FileName
End Sub

Private Sub Form_Load()
Call Module1.lianjie
txtAccessFile.Text = datapath
End Sub
模块（module1）中的代码如下：
Public myConn As New ADODB.Connection ''定义连接字符串
Public myRecord As New ADODB.Recordset ''定义记录集(字段)
Public yourRecord As New ADODB.Recordset ''定义记录集(数据)
Public cntoad As Boolean ''是否正常连接
Public ml ''姓名字段所在列
Public strSQL ''查询字符串
Public MyDatabase As Database ''定义数据库名
Public MyTable As TableDef, MyField As Field ''定义表名和字段名
Public xuehao ''读取字段序号
Public goshiRecord As New ADODB.Recordset ''定义记录集(公式)
Public hxfyn As Boolean
Public hxfbds '' 公式或条件
Public duan ''要统计的字段
Public islinshi As Boolean ''是否为临时公式
Public leiRecord As New ADODB.Recordset ''定义记录集(工资类别)
Public datapath As String ''数据库路径及名
Public table As String ''工资表名
Public lei As String '' 工资类别
Public Sub lianjie() ''打开数据库
On Error Resume Next
myConn.Close
Dim mySQL As String
''设定连接字符串
mySQL = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
mySQL = mySQL + "Data Source=" & datapath

myConn.ConnectionString = mySQL ''设定连接

myConn.open ''打开连接
myRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
myRecord.CursorLocation = adUseClient
goshiRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
goshiRecord.CursorLocation = adUseClient
yourRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
yourRecord.CursorLocation = adUseClient
End Sub

用access自动获取excel里的数据的方法就是通过代码的方式导入。
如果是手动导入比较清楚的话，这个实现起来不难，请参考下面的介绍来实现：
首先，新建一个工程，在工程中引用如下对象ADO对象（用于连接ACCESS数据库，可用其它方式），添加一个窗体（from1），在窗体上添加如下控件：
两个文本框，用来显示EXCEL文件路径和ACCESS路径；
四个扭钮，两个用来游览，另两个是导入和退出；
两个通用对话框控件，用来打开ACCESS和EXCEL文件，一个进度条控件，用来显示导入进程。
该实例的导入是将ACCESS数据库中表的字段名单独存放在另外一个表中,导入时按表中所存字段名的顺序进行导入,不是按EXCEL表的顺序导入,这样在实际中是很实用的.
因为好多时候EXCEL表中字段顺序和ACCESS中字段顺序有可能不是一致的.
代码如e799bee5baa6e79fa5e98193e58685e5aeb9333下：
Dim v
Option Explicit
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
If txtExcelFile.Text = "" Then
MsgBox "请选择EXCEL表"
Else
Dim new_value As String
Label2.Caption = "正在导入,请稍候..."
Screen.MousePointer = vbHourglass
DoEvents
'' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
'' Uncomment this line to make Excel visible.
excel_app.Visible = True
'' Open the Excel spreadsheet.
excel_app.Workbooks.open FileName:=txtExcelFile.Text
'' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If

Dim u ''求EXCEL表中记录的条数,以便控制进度条
u = 1
Do
If Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Do
u = u + 1
Loop
bar.Max = u - 1
strSQL = "select * from TestValues"
yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ''打开记录集
Dim sql As String

sql = "select * from fields order by xue"
myRecord.open sql, myConn, adOpenDynamic, adLockBatchOptimistic ''打开字段记录集
myRecord.MoveFirst
'' Get data from the Excel spreadsheet and insert
'' it into the TestValues table.

Dim v ''导入记录,用了两层循环
v = 1
Do
If Trim$(excel_sheet.Cells(v, 1)) = "" Then Exit Do ''外层,
yourRecord.AddNew
Dim i
For i = 1 To myRecord.RecordCount
'' Get the next value.
new_value = Trim$(excel_sheet.Cells(v, i))
'' See if it''s blank.
''If Len(new_value) = 0 Then Exit Do
'' Insert the value into the database.
Dim bb As String
bb = myRecord("name")
yourRecord(bb) = new_value
myRecord.MoveNext
Next i
bar.Value = v
v = v + 1
myRecord.MoveFirst
Loop
yourRecord.Update

'' Comment the rest of the lines to keep
'' Excel running so you can see it.
'' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
'' Close Excel.
excel_app.Quit
Set excel_sheet = Nothing
Set excel_app = Nothing
myRecord.Close
yourRecord.Close
Set myRecord = Nothing
Set yourRecord = Nothing
Label2.Caption = "导入完毕"
Screen.MousePointer = vbDefault
MsgBox "共导入" & Format$(v - 1) & "条记录"

End If
End Sub
Private Sub Command1_Click()
Unload Me
End Sub
Private Sub Command2_Click(Index As Integer)
''寻找ACCESS数据库
CommonDialog1.Filter = "ACCESS 文件(*.mdb)|*.mdb"
CommonDialog1.CancelError = True
CommonDialog1.ShowOpen
txtAccessFile.Text = CommonDialog1.FileName
End Sub
Private Sub Command3_Click()
''寻找excel数据库
CommonDialog2.Filter = "excel 文件(*.xls)|*.xls"
CommonDialog2.CancelError = True
CommonDialog2.ShowOpen
txtExcelFile.Text = CommonDialog2.FileName
End Sub

Private Sub Form_Load()
Call Module1.lianjie
txtAccessFile.Text = datapath
End Sub
模块（module1）中的代码如下：
Public myConn As New ADODB.Connection ''定义连接字符串
Public myRecord As New ADODB.Recordset ''定义记录集(字段)
Public yourRecord As New ADODB.Recordset ''定义记录集(数据)
Public cntoad As Boolean ''是否正常连接
Public ml ''姓名字段所在列
Public strSQL ''查询字符串
Public MyDatabase As Database ''定义数据库名
Public MyTable As TableDef, MyField As Field ''定义表名和字段名
Public xuehao ''读取字段序号
Public goshiRecord As New ADODB.Recordset ''定义记录集(公式)
Public hxfyn As Boolean
Public hxfbds '' 公式或条件
Public duan ''要统计的字段
Public islinshi As Boolean ''是否为临时公式
Public leiRecord As New ADODB.Recordset ''定义记录集(工资类别)
Public datapath As String ''数据库路径及名
Public table As String ''工资表名
Public lei As String '' 工资类别
Public Sub lianjie() ''打开数据库
On Error Resume Next
myConn.Close
Dim mySQL As String
''设定连接字符串
mySQL = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
mySQL = mySQL + "Data Source=" & datapath
myConn.ConnectionString = mySQL ''设定连接
myConn.open ''打开连接
myRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
myRecord.CursorLocation = adUseClient
goshiRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
goshiRecord.CursorLocation = adUseClient
yourRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
yourRecord.CursorLocation = adUseClient
End Sub

EXCEL里的来数据复制到ACCESS里的具体操作步骤如下：

1、打开access，执行文件源，选择新建命令，右边的新建文件，选择空数据库选项。

2、弹出文件新建数据库百，选择表，点击使用设度计器创建表。

3、执行文件，获取外部数据，知导入命令，选择扩展名为xls，点击导入。

4、导入数据向导，根据向导提示点击下一步，执行完成。

5、在数据库选择表，点击名称为sheet1的表。

6、双击sheet1打开，excel中的数据即可复制到道access表里。

1、连接e799bee5baa6e997aee7ad94e78988e69d83332ACCESS2007，2007以上版本代码都差不多：

Sub AC()
Dim cnn As New Connection
Dim rs As New Recordset
Dim sql As String
cnn.Open "Provider=Microsoft.Ace.OleDB.12.0;data Source=" & ThisWorkbook.Path & "\数据库.accdb"
sql = "select * from [宏站]"
rs.Open sql, cnn
  '复制字段名
    For i = 1 To rs.Fields.Count
 
        Cells(1, i) = rs.Fields(i - 1).Name
    Next i
 
[a1].CopyFromRecordset rs
   rs.Close
 
    cnn.Close
 
End Sub

1、连接ACCESS2003：

Dim cnn As New Connection
Dim rs As New Recordset
Dim sql As String
cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & ThisWorkbook.Path & "\数据库.mdb"
sql = "select * from [宏站] "
rs.Open sql, cnn
  '复制字段名
    For i = 1 To rs.Fields.Count
 
        Cells(1, i) = rs.Fields(i - 1).Name
    Next i
   '复制全部数据
 
    Range("A2").CopyFromRecordset rs
 
    rs.Close
 
    cnn.Close
 
 
End Sub

本文标签：excel access(9)excel 引用access(1)

如何在<em>EXCEL</em>中自动读取ACCESS数据库中的信息？-excel 引用acc

返回目录：excel表格制作

相关阅读