Imports System.Data.SqlClient
Public Class Class1

    '    Public Function Button1_Click()

    Public Function SendBack(ByVal FileName As String, ByVal FileType As String) As DataSet

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DataSet As System.Data.DataSet
        Dim myTableName As DataTable
        Dim Table As DataTable
        Dim SheetName As String
        Dim RowD As DataRow
        Dim ShowMe As String
        Dim z As Decimal = 0
        Dim Char9 As Char
        Dim Char3 As Char
        Dim Char6 As Char
        Dim Jobno As Char
        Dim InvOut As Char
        Dim DateOut As Char
        Dim SpreadTotal As Decimal
        Dim No_More_Bypass As Boolean = False
        Dim DumpHeaders As Boolean = False
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        FileName = "C:\Playarea\parenting.xlsx"
        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=Excel 12.0;")
        MyConnection.Open()
        myTableName = MyConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)


        For Each row As DataRow In myTableName.Rows
            SheetName = row("TABLE_NAME").ToString()
            Exit For
        Next row

        Try
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" + SheetName + "]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Net-informations.com")
            DataSet = New System.Data.DataSet
            MyCommand.Fill(DataSet)
            '            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        Catch ex As Exception

        End Try

        Table = DataSet.Tables(0)
        DataSet.Tables(0).Columns.Add("Account", GetType(String)) '0
        DataSet.Tables(0).Columns.Add("SnapCode", GetType(String)) '1
        DataSet.Tables(0).Columns.Add("SnapPub", GetType(Decimal)) '1-A
        DataSet.Tables(0).Columns.Add("SnapCust", GetType(Decimal)) '1-B
        DataSet.Tables(0).Columns.Add("AccountName", GetType(String)) '2
        DataSet.Tables(0).Columns.Add("ScheduleDate", GetType(String)) '4
        DataSet.Tables(0).Columns.Add("OpportunityName", GetType(String)) '5
        DataSet.Tables(0).Columns.Add("BookEntry", GetType(String)) '7
        DataSet.Tables(0).Columns.Add("PriceBook", GetType(String)) '8
        DataSet.Tables(0).Columns.Add("SnapRepIn", GetType(String)) '11
        DataSet.Tables(0).Columns.Add("ValueIn", GetType(String)) '12
        DataSet.Tables(0).Columns.Add("AxProductName", GetType(String)) '13
        DataSet.Tables(0).Columns.Add("ListPriceBill", GetType(Decimal)) '15
        DataSet.Tables(0).Columns.Add("DiscountIn", GetType(Decimal)) '17
        DataSet.Tables(0).Columns.Add("BillingAmt", GetType(Decimal)) '19
        DataSet.Tables(0).Columns.Add("OpportunityId", GetType(String)) '20
        DataSet.Tables(0).Columns.Add("JobNumber", GetType(String)) '21
        DataSet.Tables(0).Columns.Add("AdjustmentReason", GetType(String)) '26
        DataSet.Tables(0).Columns.Add("InvNumber", GetType(String)) '27
        DataSet.Tables(0).Columns.Add("ErrorCode", GetType(String)) '27
        z = 0
        If SheetName <> "Remaining_Items$" Then
            SpreadTotal = 0
            For Each CountRow As DataRow In DataSet.Tables(0).Rows
                z = z + 1
                ShowMe = CountRow.Item(0).ToString()
                If No_More_Bypass = False Then

                    If ((CountRow.Item(0).ToString() <> "")) Then
                        If ShowMe <> "" Then
                            RowD = Table.Rows(z - 1)
                            RowD.Item("Account") = CountRow.Item(0).ToString()
                            RowD.Item("SnapCode") = CountRow.Item(1).ToString()
                            Char9 = CountRow.Item(1).ToString()
                            'Char3 = %SUBST(Char9 , 1 , 3)
                            'Char6 = %SUBST(Char9 , 4 , 6)
                            Try
                                'RowD.Item("SnapPub") = Convert.ToDecimal(RowD.Item("SnapPub").subString(CountRow.Item(1).ToString(), 1, 3))
                            Catch ex As Exception
                                RowD.Item("SnapPub") = 999
                            End Try
                            Try
                                'RowD.Item("SnapCust") = Convert.ToDecimal(RowD.Item("SnapCust").SubString(CountRow.Item(1).ToString(), 4, 6))
                            Catch ex As Exception
                                RowD.Item("SnapCust") = 999999
                            End Try
                            RowD.Item("AccountName") = CountRow.Item(2).ToString()
                            '    ISODate = System.Convert.ToDateTime(CountRow.Item(4).ToString())
                            'DateOut = ISODate
                            RowD.Item("ScheduleDate") = DateOut
                            '        InvOut = %SUBST(DateOut , 1 , 4) + %SUBST(DateOut , 6 , 2)
                            RowD.Item("InvNumber") = InvOut
                            RowD.Item("OpportunityName") = CountRow.Item(5).ToString()
                            RowD.Item("BookEntry") = CountRow.Item(7).ToString()
                            RowD.Item("PriceBook") = CountRow.Item(8).ToString()
                            RowD.Item("SnapRepIn") = CountRow.Item(11).ToString() ' 11
                            RowD.Item("ValueIn") = CountRow.Item(12).ToString() ' 12
                            RowD.Item("AxProductName") = CountRow.Item(13).ToString() ' 13
                            RowD.Item("ListPriceBill") = Convert.ToDecimal(CountRow.Item(15).ToString()) ' 15
                            RowD.Item("DiscountIn") = Convert.ToDecimal(CountRow.Item(17).ToString()) ' 17
                            RowD.Item("BillingAmt") = Convert.ToDecimal(CountRow.Item(19).ToString()) ' 19
                            '                 Jobno = %SUBST(CountRow.Item(20).ToString(), 8  , 10) '21
                            RowD.Item("JobNumber") = Jobno
                            RowD.Item("OpportunityId") = CountRow.Item(21).ToString() ' 20
                            RowD.Item("AdjustmentReason") = CountRow.Item(26).ToString() '26
                            RowD.Item("ErrorCode") = ""
                            SpreadTotal = SpreadTotal + Convert.ToDecimal(CountRow.Item(19).ToString())
                        Else
                            DataSet.Tables(0).Rows(z - 1).Delete()
                        End If
                    Else
                        If DumpHeaders Then
                            DataSet.Tables(0).Rows(z - 1).Delete()
                            No_More_Bypass = True
                            DumpHeaders = False
                        Else
                            RowD = Table.Rows(z - 1)
                            RowD.Item("sbInvoice") = CountRow.Item(2).ToString()
                        End If
                    End If
                End If


            Next
        End If



        Return DataSet
    End Function


End Class