Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Sheet export and save with its name in an Access DB

Status
Not open for further replies.

zytalyb

Programmer
Oct 17, 2011
15
DE
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top