I want to import an entire Excel into a DataGridView.
Public Sub Excel()
Dim conn As OleDbConnection
Dim dta As OleDbDataAdapter
Dim OpenFileDialog As New OpenFileDialog
Try
OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog.ShowDialog(Form1) = System.Windows.Forms.DialogResult.OK) Then
Dim fi As New FileInfo(OpenFileDialog.FileName)
Dim FileName As String = OpenFileDialog.FileName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fi.FullName + ";Extended Properties=Excel 12.0;")
dta = New OleDbDataAdapter("Select * From [" + FileName + "$]", conn)
Dim ds As New DataTable("Excel")
Dim dt As New DataTable
dta.Fill(ds)
Form1.DataGridView1.DataSource = ds
conn.Close()
With Form1.DataGridView1
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "Direcciones"
End With
Form1.DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
Else
Exit Sub
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
I see that I have problems with the name of the file or something similar...
At the moment I have copied the content to a notepad and read it from there line by line, but for future occasions I would like to know how to do it... Thanks! :)
In this line you must remove $ . Try this: