Data Grid Replaces the next File Data instead of copying after the first file

[meho2021]

Dear All

I am having a code which copies excel files data from specific folder and paste them to Data Grid view.

The Data Grid Read all data of all files but the problem that it does not give the data of the first file and the second file respectively but it replaces the first file data with the second file data as a result, it always gives the last file data only instead of giving the first and the second file data together, I know there is something missing in my code which I do not know

Here is my code

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            DataGridView1.Rows.Clear()
            Dim Directory = FolderBrowserDialog1.SelectedPath
            Dim Files() As System.IO.FileInfo
            Dim DirInfo As New System.IO.DirectoryInfo(Directory)
            Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
            For Each File In Files
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                Dim DtSet As DataSet
                MyConnection = New System.Data.OleDb.OleDbConnection
    ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & File.FullName & "';  
    Extended Properties=Excel 4.0;")
               MyCommand = New OleDbDataAdapter("select SN, BarMark, Diameter, Length, Quantity 
    ,BBSName from [SCHEDULE$]", MyConnection)
                MyCommand.TableMappings.Add("Table", "Test")
                DtSet = New DataSet
                MyCommand.Fill(DtSet)
                DataGridView1.DataSource = DtSet.Tables(0)
                MyConnection.Close()
            Next
        End If
    End Sub

Appreciate your support since this is important to my work

Thanks, Regards

 

[JohnH]

Inside the loop (each file) you create a new DataSet, fill it, and assign it to DataSource. This means DataSet only contains one file, and only the last assignment to DataSource shows.

 

[meho2021]

Inside the loop (each file) you create a new DataSet, fill it, and assign it to DataSource. This means DataSet only contains one file, and only the last assignment to DataSource shows.

Dear John

You are referring that the error is creating dataset inside the loop, if this is wrong what is the alternative ? Also, the role of For Each File in files is to read each file and paste its data to the dataset then with next it goes to the next file and do the same, what do you think?

 

[JohnH]

You are referring that the error is creating dataset inside the loop, if this is wrong what is the alternative ?

Create it outside the loop, so you can fill it with data from all files.