Protected Function ExcelConnection() As OleDbCommand
Dim Econn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" + TxtFile.Text + ";" & _
"Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"
Dim objConn As New OleDbConnection(Econn)
objConn.Open()
Dim objcom As New OleDbCommand("Select * From [Sheet1$]", objConn)
Return objcom
End Function
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim myStream As IO.Stream
Dim openfile1 As New OpenFileDialog()
openfile1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
openfile1.Filter = "Excel Files (*.xls)|*.xls|All files (*.*)|*.*"
openfile1.FilterIndex = 2
openfile1.RestoreDirectory = True
If openfile1.ShowDialog() = Windows.Forms.DialogResult.OK Then
myStream = openfile1.OpenFile()
If Not (myStream Is Nothing) Then
myStream.Close()
End If
TxtFile.Text = openfile1.FileName
End If
Dim aPp As New Excel.Application
Dim woRkBook As Excel.Workbook
Dim woRkSheet As Excel.Worksheet
woRkBook = aPp.Workbooks.Open(TxtFile.Text)
woRkSheet = woRkBook.Worksheets("Sheet1")
aPp.DisplayAlerts = False
woRkBook.Close()
woRkBook = Nothing
Dim objDataAdaptor As New OleDbDataAdapter()
objDataAdaptor.SelectCommand = ExcelConnection()
Dim objDataset As New DataSet
objDataAdaptor.Fill(objDataset)
End Sub
Tag : .NET, VBScript, Excel (Excel.Application), Win (Windows App), Windows