I have a large group of Excel files that are my data source for a vb.net application. I use OleDb to extract the relevant data from each spreadsheet (one row of data per spreadsheet).
My end product will be a table with each one of those data rows from above.
I need help figuring out how to dump the data into a single vb.net DataSet. (I don't really understand details about TableMappings and DataSet.)
The code below works fine for single iteration, but I need to uncomment the "for loop" and get everything into the dataset and datagridview. Can someone help?
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
DtSet = New System.Data.DataSet
#For Each xMarket in MarketArray
Dim xMarket = "Atlanta"
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.ACE.OLEDB.12.0;" & _
" Data Source='" xMarket & ".xlsx" & "'; " & _
" Extended Properties=" + Convert.ToChar(34).ToString() +
"Excel 12.0;HDR=Yes" + Convert.ToChar(34).ToString())
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & myTab & "$] where Week like '%" & xWeek & "%'", MyConnection)
DataGridView1.DataSource = DtSet.Tables(0)