Best to break parts down.
First off I would question the drag-n-drop and ask user's to select files via a OpenFileDialog as drag-n-drop seems like a nice way many users mess it up, just a thought. Any ways I gave you a link for drag-n-drop.
Opening an Excel file can be a tad tricky but 99% of the time easy to get it right.
Example 1, the user give you a file, how do you know if the first row in the WorkSheet contains data or column names? One version of a connection string will treat the first row as data while another column names. With that said it is good you are presenting
the data in a DataGridView prior to placing the data in your database. The user can examine the data and if incorrect (thinking first row) allow them to reimport. What this means is you need to have a means for your connection to specify first row data or
first row column names. You could have a CheckBox when checked the connection is made to do first row data etc.
Although you indicate you are not expecting code from me I think it would be prudent to give you a place to start in regards to building a dynamic connection which you are welcome to use, change or not use.
Going with the above in regards to first row... SomeFile is a variable set when you handle the drag into the DataGridView. In this example I am in a click event but you would be in a DataGridView event.
Form code (note chkFirstRowIsColumnNames, a CheckBox dictates how the connection string is built). Get this to work properly is you will be ready for pushing data residing in the DataGridView to you MS-Access database table. Speaking of this you need
to make sure there are columns in the database table to accomodate data coming from Excel if you are appending data. It is wise to have a primary key, let MS-Access generate it for you.
Private dtExcel As New DataTable With {.TableName = "ExcelImports"}
Private Sub cmdOpenExcelDemo_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles cmdOpenExcelDemo.Click
DataGridView1.DataSource = Nothing
dtExcel = New DataTable
Using cn As New OleDb.OleDbConnection
If chkFirstRowIsColumnNames.Checked Then
cn.SetExcelConnectionString(SomeFile, UseHeader.Yes, ExcelImex.TryScan)
Else
cn.SetExcelConnectionString(SomeFile, UseHeader.No, ExcelImex.TryScan)
End If
Using cmd As New OleDb.OleDbCommand With
{
.Connection = cn,
.CommandText = "SELECT * FROM [Sheet1$]"
}
dtExcel.Load(cmd.ExecuteReader)
End Using
End Using
DataGridView1.DataSource = dtExcel
End Sub
NOTE: The following code must be in a code module, not a form or class. I would use the name of the module as shown below for a file name. The line beginning with #If will not be compiled, it is simply an example of how to use the language
extension.
Imports System.Data.OleDb
Module OleDbExtensions
#If KSG_DEMO_EXCEL_CONNECTION1 Then
Using cn As New OleDb.OleDbConnection
cn.SetExcelConnectionString("CompanyEmailAddresses.xlsx", UseHeader.Yes, ExcelImex.TryScan)
Console.WriteLine(cn.ConnectionString)
End Using
#End If
Public Enum UseHeader
''' <summary>
''' Indicates that the first row contains columnnames, no data
''' </summary>
''' <remarks></remarks>
Yes
''' <summary>
''' Indicates that the first row does not contain columnnames
''' </summary>
''' <remarks></remarks>
No
End Enum
Public Enum ExcelImex
TryScan = 0
Resolve = 1
End Enum
''' <summary>
''' Used to make connection to Excel easy
''' </summary>
''' <param name="sender"></param>
''' <param name="FileName"></param>
''' <param name="Header"></param>
''' <param name="IMEX"></param>
''' <remarks></remarks>
<System.Diagnostics.DebuggerStepThrough()> _
<System.Runtime.CompilerServices.Extension()> _
Public Sub SetExcelConnectionString(
ByRef sender As OleDbConnection,
ByVal FileName As String,
ByVal Header As UseHeader,
ByVal IMEX As ExcelImex)
Dim Mode As String = CInt(IMEX).ToString
Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
If IO.Path.GetExtension(FileName).ToUpper = ".XLSX" Then
Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
Builder.Add("Extended Properties", "Excel 12.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
Else
Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
Builder.Add("Extended Properties", "Excel 8.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
End If
sender.ConnectionString = Builder.ConnectionString
End Sub
End Module
Thoughts on importing to MS-Access, if you were directly importing you could work out something with OleDb that would create a table each time you do an import but that can be complicated as this method uses the sheet name as the table name and if the table
name already exists you will get a run time error. If you do the import say by appending data this is were a primary key is good do there is some method to identifiy information. So best to figure out exactly how you want to make this work prior to starting
the import to Excel.
I believe you mentioned something about archiving, get the above working first.
For now you have a good deal to work on, if you get stuck, come back and ask you question.
kevininstructor
