作者：乔山办公网日期：
返回目录：excel表格制作
Excel数据传入7a64e59b9ee7ad94331Access理容易些,这里给你Word数据传入Access的代码:
Sub TableToAccess()
'Created 2-18-99 by Helen Feddema
'Last modified 12-13-2001
On Error GoTo ErrorHandler
Dim strSiteName As String
Dim strIDName As String
Dim strIDValue As String
Dim strDBName As String
Dim DAO As New DAO.DBEngine
Dim dbs As Database
Dim rstOne As Recordset
Dim rstMany As Recordset
Dim wks As Workspace
Dim strDocsDir As String
Dim lngID As Long
Dim lngStartRows As Long
Dim lngRows As Long
'Pick up path to Documents folder from Registry
strDocsDir = System.PrivateProfileString("", _
"HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders", _
"Personal")
strDBName = strDocsDir & "\Logons and IDs.mdb"
Debug.Print "DBName: " & strDBName
Set wks = DAO.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)
Set rstOne = dbs.OpenRecordset("tblLogons")
Set rstMany = dbs.OpenRecordset("tblLogonValues")
Selection.HomeKey Unit:=wdStory
NextItem:
'Pick up site name from Heading 3 style
Selection.Find.ClearFormatting
Selection.Find.Style = ActiveDocument.Styles("Heading 3")
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = True
End With
Selection.Find.Execute
If Selection.Find.Found = False Then
GoTo ErrorHandlerExit
End If
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
strSiteName = Selection
Debug.Print "Site name: " & strSiteName
rstOne.AddNew
rstOne!SiteName = strSiteName
lngID = rstOne!ID
Debug.Print "ID: " & lngID
rstOne.Update
'Go to next table
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.GoTo What:=wdGoToTable, Which:=wdGoToNext, _
Count:=1, Name:=""
lngStartRows = Selection.Information(wdMaximumNumberOfRows)
'Select current cell
Selection.MoveRight Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
AddValues:
If Selection.Type = wdSelectionIP Then GoTo NextItem
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
'Save ID name and value to variables
strIDName = Selection
Debug.Print "ID name: " & strIDName
Selection.MoveRight Unit:=wdCell
strIDValue = Selection
Debug.Print "ID value: " & strIDValue
'Write ID name and value to Many table
With rstMany
.AddNew
!ID = lngID
!ItemName = strIDName
!ItemValue = strIDValue
.Update
End With
'Check whether still in table, and go to next heading if not
Selection.MoveRight Unit:=wdCell
lngRows = Selection.Information(wdMaximumNumberOfRows)
Debug.Print "Start rows: " & lngStartRows & vbCrLf & "Rows: " & lngRows
If lngRows = lngStartRows Then
If Selection.Information(wdWithInTable) = True Then
GoTo AddValues
Else
GoTo NextItem
End If
End If
ErrorHandlerExit:
rstOne.Close
rstMany.Close
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; error message: " & Err.Description
Resume ErrorHandlerExit
End Sub
用excel VBA通过ADO和SQL操作Access数据库，所谓操作数据库就是查询、删除、添加数据，你可以用添加数据方法将excel表数据添加到Access数据库。
以下e68a847a686964616f331是我用了实现从Excel导数据进ACCESS的代码，你参考一下，应该就可以做出来了：（Excel和Access两个文件放一个文件夹下）
Public Sub 客户正常供价批量维护()
If Cells(4, 6) = "" Then
MsgBox "请先选择需要维护价格的系统！", vbInformation
Cells(4, 6).Select
Exit Sub
Else
Dim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k为整数变量；sht 为excel工作表对象变量，指向某一工作表
Dim cn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象，保存数据表
Dim strCn As String, strSQL As String '字符串变量
Dim mdbFile As String
On Error GoTo add_err
mdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile '定义数据库链接字符串
cn.Open strCn '与数据库建立连接，如果成功，返回连接对象cn
Set rs = New ADODB.Recordset
rs.Open "dbl直营客户正常供价表", cn, adOpenKeyset, adLockOptimistic
k = Cells(4, 8) + 8
For i = 9 To k
If Cells(i, 7) = "" Then
Else
rs.AddNew
rs(1) = Cells(i, 2)
rs(2) = Cells(i, 7)
rs(3) = Cells(i, 8)
rs.Update
Cells(i, 7) = ""
Cells(i, 8) = ""
End If
Next
MsgBox "数据记录添加成功！", vbInformation
Cells(4, 6).Select
ActiveWorkbook.RefreshAll
add_exit:
Exit Sub
End If
add_err:
MsgBox Err() & vbCrLf & Error()
Resume add_exit
End Sub
以下是我用了实现从Excel导数据进ACCESS的代码，你参考一下，应该就可以做出来了：（Excel和Access两个文件放一个文件夹下）
Public Sub 客户正常供价批量维护()
If Cells(4, 6) = "" Then
MsgBox "请先7a686964616fe59b9ee7ad94333选择需要维护价格的系统！", vbInformation
Cells(4, 6).Select
Exit Sub
Else
Dim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k为整数变量；sht 为excel工作表对象变量，指向某一工作表
Dim cn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象，保存数据表
Dim strCn As String, strSQL As String '字符串变量
Dim mdbFile As String
On Error GoTo add_err
mdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile '定义数据库链接字符串
cn.Open strCn '与数据库建立连接，如果成功，返回连接对象cn
Set rs = New ADODB.Recordset
rs.Open "dbl直营客户正常供价表", cn, adOpenKeyset, adLockOptimistic
k = Cells(4, 8) + 8
For i = 9 To k
If Cells(i, 7) = "" Then
Else
rs.AddNew
rs(1) = Cells(i, 2)
rs(2) = Cells(i, 7)
rs(3) = Cells(i, 8)
rs.Update
Cells(i, 7) = ""
Cells(i, 8) = ""
End If
Next
MsgBox "数据记录添加成功！", vbInformation
Cells(4, 6).Select
ActiveWorkbook.RefreshAll
add_exit:
Exit Sub
End If
add_err:
MsgBox Err() & vbCrLf & Error()
Resume add_exit
End Sub