vba 如何将 VB.Net 连接到 Excel 作为数据库连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22557371/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect VB.Net to Excel as Database Connection?
提问by Gin
I need a database connection using microsoft excel but i don't know what codes i should
use. I can only do a connection between access and VB but in excel I can't
我需要一个使用 microsoft excel 的数据库连接，但我不知道我应该
使用什么代码。我只能在 access 和 VB 之间建立连接，但在 excel 中我不能
I copied this in the net but it doesn't work on me
我在网上复制了这个，但它对我不起作用
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
'Fill the [Excel file fullpath] with specific value
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.ACE.OLEDB.12.0;Data Source= E:\DATABASE\VBtoExcel\VBtoExcel\bin\DataBaseExcel.xlsx; Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter _
("select * from [Sheet1]", MyConnection)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()
End Sub
回答by Steve
The syntax to select from a sheet of an Excel file requires the $ character at the end of the sheet name.
从 Excel 文件工作表中进行选择的语法要求工作表名称末尾有 $ 字符。
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
^^^^^^^^^^
A tip, add the Import Statementfor the System.Data.OleDb to your file. So you could type shorter names
提示，将System.Data.OleDb的导入语句添加到您的文件中。所以你可以输入更短的名字
MyCommand = New OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
回答by saravana
Imports System.Data.OleDb
Public Class Form1
Dim cn As New OleDbConnection
Dim cm As New OleDbCommand
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
cn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source =O:\design\Connect to Excel\Agent.xls;extended properties=excel 8.0;"
cn.Open()
With cm
.Connection = cn
.CommandText = "insert into [Data Agent$]values('" & TxtId.Text & "','" & TxtName.Text & "')"
.ExecuteNonQuery()
End With
cn.Close()
MsgBox("Sucessfully", MsgBoxStyle.Information, Text)
End Sub
Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click
' Close()
End Sub
End Class
