Public Function ExcelToDataTable_Copy(ByVal xlsFilaName As String, ByRef ErrMsg As String) As DataTable

        Try

            Dim SheetName As String

            Dim dt As DataTable

            dt = New DataTable

            Dim sExcelFile As String

            sExcelFile = xlsFilaName

            Dim sConnectionString As String

            Dim TmpFileName As String

            Dim strFilePath As String

            Dim myExcel As Excel.Application = New Excel.Application

            Dim missing As Object = System.Reflection.Missing.Value

            strFilePath = “ddd”

            myExcel.Application.Workbooks.Open(strFilePath)

            Dim mybook As Excel.Workbook = myExcel.Workbooks(1)

            Dim sheet As Excel.Worksheet = mybook.Sheets(1)

            Dim strSheetName As String = sheet.Name

            mybook.Close()

            myExcel.Quit()

            mybook = Nothing

            myExcel = Nothing

            TmpFileName = I_FILE_NAME.Text.Trim.Substring(I_FILE_NAME.Text.LastIndexOf("."), I_FILE_NAME.Text.Length - I_FILE_NAME.Text.LastIndexOf("."))

 

            'Excel2007

            If TmpFileName.Equals(".xlsx") Then

                sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sExcelFile & "; Extended Properties='Excel 12.0;HDR=NO;IMEX=1;'"

                'Excel2003

            ElseIf TmpFileName.Equals(".xls") Then

                sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sExcelFile & "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"

            End If

            Dim connection As OleDb.OleDbConnection

            connection = New OleDb.OleDbConnection(sConnectionString)

            connection.Open()

            dt = connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

            Dim sql_select_commands As String

            sql_select_commands = "Select * from [" + strSheetName + "$]"

            Dim adp As OleDb.OleDbDataAdapter

            adp = New OleDb.OleDbDataAdapter(sql_select_commands, connection)

            Dim ds As Data.DataSet

            ds = New Data.DataSet()

            adp.Fill(ds, strSheetName)

            dt = ds.Tables(0)

            If (connection.State = ConnectionState.Open) Then

                connection.Close()

            End If

 

            ErrMsg = CheckTableInfo(dt)

 

            Return dt

        Catch ex As Exception

            Select Case Err.Number

                Case Else

                    MSG = Trim(GetMsgDesc(10024)) & " = " & Str(Err.Number) & vbCrLf & Trim(GetMsgDesc(10025)) & " = " & Err.Source

                    MSG = MSG & vbCrLf & Trim(GetMsgDesc(10026)) & vbCrLf & Err.Description

                    MsgBox(MSG, MsgBoxStyle.Critical)

            End Select

            Err.Clear()

 

            BWrite(ex.Message.ToString())

        End Try

    End Function