A while back, I received some awesome assistance with learning how to fetch data from an Excel range, and place it directly into a DataGridView container using the following code:
Private Sub RefreshData()
'Create the connection string to connect to the Microsoft Excel Workbook
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& XL & ";Extended Properties = ""Excel 12.0 Xml;HDR=YES"""
'Create a standard SELECT SQL statement
Dim selectStatement As String = _
"SELECT Player_No, Player_Name, SL, Team_ID, Team_No, Team_Name FROM [Roster$]"
Dim adapter As New OleDbDataAdapter(selectStatement, connectionString)
Dim excelData As New DataTable
'Create a DataAdapter that will be used to populate a DataTable with data
'The code to open a connection and execute a statement
Catch ex As InvalidOperationException
Catch ex As OleDbException
'Any tidy up code here to release resources
'Display the data in the excelDataGridView
PlayerDataGrid.DataSource = excelData
Since then, my project has transitioned to using all bound data tables which originate from Access. One offshoot is that I no longer use just the one datagridview, but have separated the data into a more relational database format (Roster data in one table, Team data in another, etc.).
I thought for sure I could do this easily enough, but I'm stuck scratching my head. Perhaps someone could point me in the right direction?
Also, I would like to at the very least allow users who have older versions of Excel (.XLS) and even those with no Excel (for example, they could format it as a .CSV) to accomplish the same result.
If you want the scheme for the Roster and Teams data files, it looks something like this:
The desired initial layout of the DataRoster.xlsx, .xls or .csv file would be something like this:
APA_ID, Player_Name, SL, Team_ID, Team_No, Team_Name
Just to clarify the difference between Team_ID and Team_No, the APA (American Poolplayers Association) which manages everything, changes the Team_No each season, so I assign my own Team_ID for the Teams data table so I can maintain long term team statistics. Because of that, I would populate the Roster data table first, then do a SELECT DISTINCT to populate the Teams data table.
Anyway, once I learn how to do the smaller subset of columns, I should be able to figure out how to add all the other fields from the two tables.