如何将XLS的数据导入到mdb数据库中! 如何将XLS的数据导入到mdb数据库中! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 用ADO读取XLS,再保存到MDB中即可或者创建EXCEL对象,直接读取CELL中的TEXT值,再保存也行 请使用 DoCmd 对象的 TransferSpreadsheet 方法,具体请查阅access的帮助文件 Private Sub ExcelSheetToAccess(sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String)Dim cn As ADODB.Connection, rs As ADODB.RecordsetDim sSQL As StringOn Error Resume Nextcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAccessDBPathrs.Open "Select * From " & sAccessTable, cnIf Err.Number Then sSQL = "Select * Into " & sAccessTable & " FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""Else sSQL = "Insert Into " & sAccessTable & " Select * FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""End Ifrs.Closecn.Execute sSQLcn.CloseSet rs = NothingSet cn = NothingEnd Sub使用范例如下:將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTableExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb" 要引用Microsoft ActiveX Data Object 2.6 Library 把楼上的代码改成以下就不用引用Private Sub ExcelSheetToAccess(sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String)Dim cn As object, rs As object '修改了Dim sSQL As StringOn Error Resume Nextset cn=createObject("ADODB.Connection") '加了set rs=createObject("ADODB.Recordset") '加了cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAccessDBPathrs.Open "Select * From " & sAccessTable, cnIf Err.Number Then sSQL = "Select * Into " & sAccessTable & " FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""Else sSQL = "Insert Into " & sAccessTable & " Select * FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""End Ifrs.Closecn.Execute sSQLcn.CloseSet rs = NothingSet cn = NothingEnd Sub使用范例如下:將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTableExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb" 怎樣用vb.net實現攝像頭人臉識別 鼠标键盘钩子的问题 大家好,如何用VB写虚拟鼠标点击执行具体路径的EXE 谢谢!!加分 100 vb6如何声明在vc6中带有指针的结构体?? 100分关于引用的低级问题(在线等) 发布后软件在别的机子运行,控件text和Label的字为什么看起来怪怪的? VB中的RichEdit 3.0控件,那里找? vb2005中,如何将大型文件以二进制流的形式存入access数据库中,我需要详细的操作过程和代码,谢谢 如何判断LISTVIEW中选中的几行中某一列的几个值是否相同! 如何向串口发送82H指令(不是数据)? 数据导入问题 编写递归函数,求5!
或者创建EXCEL对象,直接读取CELL中的TEXT值,再保存也行
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim sSQL As StringOn Error Resume Next
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAccessDBPath
rs.Open "Select * From " & sAccessTable, cnIf Err.Number Then
sSQL = "Select * Into " & sAccessTable & " FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""
Else
sSQL = "Insert Into " & sAccessTable & " Select * FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""
End Ifrs.Closecn.Execute sSQLcn.CloseSet rs = Nothing
Set cn = Nothing
End Sub使用范例如下:將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTableExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb"
Dim cn As object, rs As object '修改了
Dim sSQL As StringOn Error Resume Next
set cn=createObject("ADODB.Connection") '加了
set rs=createObject("ADODB.Recordset") '加了
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAccessDBPath
rs.Open "Select * From " & sAccessTable, cnIf Err.Number Then
sSQL = "Select * Into " & sAccessTable & " FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""
Else
sSQL = "Insert Into " & sAccessTable & " Select * FROM [" & sSheetName & "$] In """ & sExcelPath & """ ""EXCEL 5.0;"""
End Ifrs.Closecn.Execute sSQLcn.CloseSet rs = Nothing
Set cn = Nothing
End Sub使用范例如下:將 C:\book1.xls 中的 Sheet1 导入 C:\Test.mdb 成为 TestTableExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb"