Dear all, I'm working on a monthly Report (Excel Format) that has only one Sheet (June 2013 ....July 2013.....etc.). I should make an Import for this File and Read (display) its content in a DataGridView of VB.NeT.
Then per Click of a Command Button, the Excel Sheet, should be exported in to Access dataBase and saved their as a Table with the same name given for the Excel Sheet.
I have no Problem with the first part (import and display the data of the sheet in the DataGridView of VB.Net) but my Problem is to export the Sheet's content and to save it in Access when I click the btn Save for example.
Here is my code for the first part with my hope of your Help:
Public Class frmSelectExcelFile
Dim SheetList As New ArrayList
' Part 1: Retrieve all WorkSheet names and store them into an ArrayList object
Private Sub frmSelectExcelFile_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objWorkSheets As Excel.Worksheet
Dim ExcelSheetName As String = ""
OFD.FileName = ""
OFD.Filter = "Excel files (*.xls)|*.xls|Excel(2007) files (*.xlsx)|*.xlsx"
OFD.ShowDialog()
txtPath.Text = OFD.FileName
objExcel = CreateObject("Excel.Application")
objWorkBook = objExcel.Workbooks.Open(OFD.FileName)
For Each objWorkSheets In objWorkBook.Worksheets
SheetList.Add(objWorkSheets.Name)
' ListBox1.Items.Add(objWorkSheets.Name)
txtSheets.Text = ExcelSheetName
Try
For Each totalWorkSheets In objWorkBook.Worksheets
ExcelSheetName += totalWorkSheets.Name + " / "
Next totalWorkSheets
Catch ex As Exception
End Try
Next
End Sub
Private Sub btnShowExcelData_Click(sender As Object, e As EventArgs) Handles btnShowExcelData.Click
Dim DS As DataSet
Dim MyCommand As OleDb.OleDbDataAdapter
Dim MyConnection As OleDb.OleDbConnection
MyConnection = New OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source= " + OFD.FileName + " ;Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New OleDb.OleDbDataAdapter( _
"select * from [" & SheetList(0) & "$]", MyConnection)
DS = New System.Data.DataSet()
Try
MyCommand.Fill(DS)
DGV.DataSource = DS.Tables(0).DefaultView
Catch ex As Exception
MsgBox(ex.Message.ToString)
Finally
MyConnection.Close()
End Try
Dim rows As Integer = Me.DGV.RowCount - 1
If rows = 0 Then
lblRecords.Text = "No records exist"
Else
lblRecords.Text = rows & " found in the Database"
End If
End Sub
Then per Click of a Command Button, the Excel Sheet, should be exported in to Access dataBase and saved their as a Table with the same name given for the Excel Sheet.
I have no Problem with the first part (import and display the data of the sheet in the DataGridView of VB.Net) but my Problem is to export the Sheet's content and to save it in Access when I click the btn Save for example.
Here is my code for the first part with my hope of your Help:
Public Class frmSelectExcelFile
Dim SheetList As New ArrayList
' Part 1: Retrieve all WorkSheet names and store them into an ArrayList object
Private Sub frmSelectExcelFile_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objWorkSheets As Excel.Worksheet
Dim ExcelSheetName As String = ""
OFD.FileName = ""
OFD.Filter = "Excel files (*.xls)|*.xls|Excel(2007) files (*.xlsx)|*.xlsx"
OFD.ShowDialog()
txtPath.Text = OFD.FileName
objExcel = CreateObject("Excel.Application")
objWorkBook = objExcel.Workbooks.Open(OFD.FileName)
For Each objWorkSheets In objWorkBook.Worksheets
SheetList.Add(objWorkSheets.Name)
' ListBox1.Items.Add(objWorkSheets.Name)
txtSheets.Text = ExcelSheetName
Try
For Each totalWorkSheets In objWorkBook.Worksheets
ExcelSheetName += totalWorkSheets.Name + " / "
Next totalWorkSheets
Catch ex As Exception
End Try
Next
End Sub
Private Sub btnShowExcelData_Click(sender As Object, e As EventArgs) Handles btnShowExcelData.Click
Dim DS As DataSet
Dim MyCommand As OleDb.OleDbDataAdapter
Dim MyConnection As OleDb.OleDbConnection
MyConnection = New OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source= " + OFD.FileName + " ;Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New OleDb.OleDbDataAdapter( _
"select * from [" & SheetList(0) & "$]", MyConnection)
DS = New System.Data.DataSet()
Try
MyCommand.Fill(DS)
DGV.DataSource = DS.Tables(0).DefaultView
Catch ex As Exception
MsgBox(ex.Message.ToString)
Finally
MyConnection.Close()
End Try
Dim rows As Integer = Me.DGV.RowCount - 1
If rows = 0 Then
lblRecords.Text = "No records exist"
Else
lblRecords.Text = rows & " found in the Database"
End If
End Sub