Combine all Sheets in Excel

[mipakteh]

Hi All,

I want combine all sheet in excel Sheet1 to Sheet30.How to do it.It possible if we have 1 to 3 sheet write manually.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim _filename As String = "C:\Users\Uset\Documents\Book3.xlsx"

Dim _conn As String
_conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filename & ";Extended Properties='Excel 12.0;IMEX=1;'"

Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
Dim _command As OleDbCommand = New OleDbCommand()

_command.Connection = _connection
_command.CommandText = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
da.SelectCommand = _command

Try
da.Fill(ds1, "Sheet1" & "Sheet2")
MessageBox.Show("The import is complete!")
Me.DataGridView1.DataSource = ds1
Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"

Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!")
End Try
End Sub


Thank

Continue reading...