怎么将多个excel表导入一个access数据库

B实现
此一模块共有四个参数：
1、sSheetName：要导出资料的文件名称 (Sheet name)，例如 Sheet1
2、sExcelPath：要导出资料的 Excel 档案路径名称 (Workbook path)，例如 C:\\book1xls
3、sAccessTable：要导入的 Access Table 名称，例如 TestTable
4、sAccessDBPath：要导入的 Access 档案路径名称，例如 C:\\Testmdb
在声明中加入以下：
Private Sub ExportExcelSheetToAccess(sSheetName As String, _
sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 50")
Call dbExecute("Select into [;database=" & sAccessDBPath & "]" & _
sAccessTable & " FROM [" & sSheetName & "$]")
MsgBox "Table exported successfully", vbInformation, "Yams"
End Sub
使用范例如下：将 C:\\book1xls 中的 Sheet1 导入 C:\\Testmdb 成为 TestTable
ExportExcelSheetToAccess "Sheet1", "C:\\book1xls", "TestTable", "C:\\Testmdb"
--------------------------------------------------------------------------------
ASP
以下代码优点,不用打开EXCEL进程,不会出现EXCEL进程无法结束而死机缺点:可能会出现导入空格的现像．
<%
sub dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn)
'定义
dim myConnection
dim strName
dim rsXsl,rsSql
dim str_Xsl,str_Sql
dim myConn_Xsl
dim cmd
dim i,j
dim maxId
strName=strFileName
set myConnection=servercreateobject("adodbconnection")
set rsXsl=ServerCreateobject("ADODBRecordset")
set rsSql=ServerCreateObject("ADODBRecordset")
set cmd=ServerCreateObject("ADODBCommand")
cmdActiveConnection=myConn

myConn_Xsl="Provider=MicrosoftJetOLEDB40;Data Source=" & strName & ";Extended Properties=Excel 80"
'myConn_Xsl="Provider=MicrosoftJetOLEDB40;Data Source=" & Application("ASP_Directory") & "chapter05\database\" & strName & ";Extended Properties=Excel 80"
'打开连接
myConnectionopen myConn_Xsl
'打开表
str_Xsl="select from ["& strSheetName &"$]"
rsXslopen str_Xsl,myConnection,1,1
j=1
Do While not rsXsleof
'取出最大值
str_Sql="select Max(id) as maxId from new"
rsSqlopen str_Sql,myConn,1,3
If Not rsSqlEof Then
If not isNull(rsSql("maxId")) Then
maxId=Clng(rsSql("maxId"))+1
Else
maxId=1
End if
else
maxId=1
End if
rsSqlclose'//关闭对象
'加入数据库
strqbsr=""
yssr=""
str_Sql="insert into new values("&maxId&",'"&rsXsl(1)&"','"&rsXsl(2)&"','"&rsXsl(3)&"','"&rsXsl(4)&"','"&rsXsl(5)&"','"&rsXsl(6)&"','"&rsXsl(7)&"','"&rsXsl(8)&"','"&rsXsl(9)&"','"&rsXsl(10)&"','"&rsXsl(11)&"','"&rsXsl(12)&"','"&rsXsl(13)&"','"&rsXsl(14)&"','"&rsXsl(15)&"','"&rsXsl(16)&"','"&strqbsr&"','"&yssr&"')"
cmdCommandText=str_Sql
cmdExecute()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
j=j+1
rsXslmovenext
Loop
responsewrite "共导入<font color='red'>" & j-1 & "</font>条记录<br/>"
responsewrite "<a href=javascript:historyback()>确定</a>"
set rsXsl=nothing
set rsSql=nothing
set myconnection=nothing
set cmd=nothing
end sub
file1="'"&requestform("filename2")&"'"
strtj=mid(requestform("filename2"),instrrev(file1,"\"),(instrrev(file1,"")-instrrev(file1,"\")-1))
if file1="" then
responsewrite "请选择您要导入的Excel表!<p></p>"
%>
<a href=javascript:historyback()>上一页</a>
<%
responseend
end if
'responswrite "'"&requestform("filename")&"'"
myconn="DRIVER={SQL SERVER};SERVER=(local);uid=sa;pwd=sa;DATABASE=qjgsj_data"
dataIntoSqlServer_ceritificate file1,""&strtj&"",myconn
%>

以上就是关于怎么将多个excel表导入一个access数据库全部的内容，包括:怎么将多个excel表导入一个access数据库、、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！