如何用VB实现Access 与Excel之间互相导入导出

ly0020 2018-07-19 12:53:06

由于工作原因，需要做大量ACCESS与Excel之间的转换，修改Excel后再导入access，然后用access生成CAD图。工作量很大，请问有没有大神帮帮忙，给个解决办法，怎样实现ACCESS与Excel之间的快速转换。Excel文件名和access里面的表名要一样。本人对编程一窍不通。请大神们帮帮忙！[/quote]

...全文

1160 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

of123 2018-08-27

打赏
举报

Excel2MDB 函数可以直接复制和调用。不过要在你的工程中添加 ADO 对象的引用。

例如，在 VB 中选择 Project - References...，在弹出窗口中找到并勾选 Microsoft ActiveX Data Objects 2.x Library，确定。

在工程窗体中粘贴 Excel2MDB 函数代码。

调用时，ExcelPath 为 xls 文件的路径和文件名，用单引号括起来。SheetNm 为其中的表名，如 '$Sheet1'。AccessPath 为 mdb 文件路径及文件名，AccessTable 为 Access 数据库表名。

of123 2018-08-27

打赏
举报

注意，上述函数的导入新数据时，完全删除原有数据。这是为了适用不同字段结构的表。如果你的表结构都是相同的，可以将删除语句注掉，就可持续导入新数据。

nidodo1 2018-08-24

打赏
举报

[/quote]
您好，请问这些代码怎样用啊？本人小白一个，能否给一个详细的操作方法。不胜感激！[/quotewa]
完全一点都不用修改的代码基本上的没有，除非特地帮你做，但你的具体需求又不清楚，所以你要根据大神给的代码自己改

ly0020 2018-07-25

打赏
举报

引用 4 楼 cbm666 的回复:

Private Sub Excel2MDB(ExcelPath As String, SheetNm As String, AccessPath As String, AccessTable As String)
Conn.CursorLocation = adUseClient
If Conn.State = adStateOpen Then Conn.Close
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & ExcelPath & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
Call Conn.Execute("Delete From [;database=" & AccessPath & "]." & AccessTable) '表已存在必须先清除表内的数据
Call Conn.Execute("Insert into [;database=" & AccessPath & "]." & AccessTable & " Select * FROM [" & SheetNm & "$]")
MsgBox "数据导入MDB成功!", vbInformation, "Excel导入MDB表"
End Sub

Public Sub MDB2Excel(MdbNm As String, MdbTable As String, ExcelNm As String)
On Error Resume Next
Set acApp = GetObject(MdbNm, "Access.Application")
acApp.DoCmd.OutputTo acOutputTable, MdbTable, "Microsoft Excel (*.xls)", ExcelNm
acApp.CloseCurrentDatabase
Set acApp = Nothing
End Sub

您好，请问这些代码怎样用啊？本人小白一个，能否给一个详细的操作方法。不胜感激！

熊孩子开学喽 2018-07-24

打赏
举报

引用 4 楼 cbm666 的回复:

Private Sub Excel2MDB(ExcelPath As String, SheetNm As String, AccessPath As String, AccessTable As String)
Conn.CursorLocation = adUseClient
If Conn.State = adStateOpen Then Conn.Close
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & ExcelPath & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
Call Conn.Execute("Delete From [;database=" & AccessPath & "]." & AccessTable) '表已存在必须先清除表内的数据
Call Conn.Execute("Insert into [;database=" & AccessPath & "]." & AccessTable & " Select * FROM [" & SheetNm & "$]")
MsgBox "数据导入MDB成功!", vbInformation, "Excel导入MDB表"
End Sub

Public Sub MDB2Excel(MdbNm As String, MdbTable As String, ExcelNm As String)
On Error Resume Next
Set acApp = GetObject(MdbNm, "Access.Application")
acApp.DoCmd.OutputTo acOutputTable, MdbTable, "Microsoft Excel (*.xls)", ExcelNm
acApp.CloseCurrentDatabase
Set acApp = Nothing
End Sub

正解!
直接使用access的引擎导入导出excel是最快的. 比SQL或其他方式要快至少一个数量级.

cbm6666 2018-07-22

打赏
举报

Private Sub Excel2MDB(ExcelPath As String, SheetNm As String, AccessPath As String, AccessTable As String)
Conn.CursorLocation = adUseClient
If Conn.State = adStateOpen Then Conn.Close
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & ExcelPath & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
Call Conn.Execute("Delete From [;database=" & AccessPath & "]." & AccessTable) '表已存在必须先清除表内的数据
Call Conn.Execute("Insert into [;database=" & AccessPath & "]." & AccessTable & " Select * FROM [" & SheetNm & "$]")
MsgBox "数据导入MDB成功!", vbInformation, "Excel导入MDB表"
End Sub

Public Sub MDB2Excel(MdbNm As String, MdbTable As String, ExcelNm As String)
On Error Resume Next
Set acApp = GetObject(MdbNm, "Access.Application")
acApp.DoCmd.OutputTo acOutputTable, MdbTable, "Microsoft Excel (*.xls)", ExcelNm
acApp.CloseCurrentDatabase
Set acApp = Nothing
End Sub

of123 2018-07-20