Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
Showing results for 
Search instead for 
Did you mean: 
Former Member

About Me:

I have 4 years of experience in implementation and developing Addons of B1 8.81, 8.82.

Consisting need of reading the data from Excel sheet and using it in SAP B1, I have defined steps to be followed for reading excel sheet data.

I know that SAP has given ASAP methodology but in this blog am going to explain it in differ way.

Purpose of the BLOG:

How to read contents of Excel sheet by SAP B1 Addon. Sometimes, While preparing GRPO, my Client had to enter Serial numbers of many Items. Their suppliers used to provide them that data in excel format also.

So I have developed addon to read the data from excel sheet

Steps -

'1. I have created one Button on Serial Number Setup window, on Button click event I have called method showOpenFileDialog which calls other method.

Public Function showOpenFileDialog() As String

        Dim ShowFolderBrowserThread As Threading.Thread


            ShowFolderBrowserThread = New Threading.Thread(AddressOf ShowFolderBrowser)

            If ShowFolderBrowserThread.ThreadState = System.Threading.ThreadState.Unstarted Then



            ElseIf ShowFolderBrowserThread.ThreadState = System.Threading.ThreadState.Stopped Then



            End If

            While ShowFolderBrowserThread.ThreadState = Threading.ThreadState.Running


            End While

            If FileName <> "" Then

                Return FileName

            End If

        Catch ex As Exception

            'SBO_Application.MessageBox("FileFile" & ex.Message)


        End Try

        Return ""

    End Function

'2. To filter Excel files only and open that file

    Public Sub ShowFolderBrowser()

        Dim MyProcs() As System.Diagnostics.Process

        FileName = ""

        Dim OpenFile As New OpenFileDialog


            OpenFile.Multiselect = False

            OpenFile.Filter = "Excel 2003 files(*.xls)|*.xls|Excel 2007 Files(*.xlsx)|*.xlsx"

            Dim filterindex As Integer = 0


                filterindex = 0

            Catch ex As Exception

            End Try

            OpenFile.FilterIndex = filterindex

            OpenFile.RestoreDirectory = True

            MyProcs = System.Diagnostics.Process.GetProcessesByName("SAP Business One")

            If MyProcs.Length = 1 Then

                For i As Integer = 0 To MyProcs.Length - 1

                    Dim MyWindow As New WindowWrapper(MyProcs(i).MainWindowHandle)

                    Dim ret As DialogResult = OpenFile.ShowDialog(MyWindow)

                    If ret = DialogResult.OK Then

                        FileName = OpenFile.FileName




                    End If


            End If

        Catch ex As Exception

            SBO_Application.MessageBox("File Format is not correct. Please Check the file again...")


            FileName = ""



        End Try

        'To Read data from Excel Sheet

        If (FileName <> "") Then



        End If

    End Sub

'3. Window wrapper class

    Private Class WindowWrapper

        Implements System.Windows.Forms.IWin32Window

        Private _hwnd As System.IntPtr

'4. Initialsise Handle

        Public Sub New(ByVal handle As System.IntPtr)

            _hwnd = handle

        End Sub

        Private ReadOnly Property Handle() As System.IntPtr Implements System.Windows.Forms.IWin32Window.Handle


                Return _hwnd

            End Get

        End Property

    End Class

'5. Read excel shet data. GridItemcode is variable which is same as Sheet Name. You can replace your variable here

    Private Sub Read_Excel(ByVal aFileName As String)


        'For Reading Excel Sheet Data


            Dim XlSheetRowNo As Int16 = 1

            Dim GridRowNo As Int16 = 1

            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + aFileName + "; Extended Properties =Excel 8.0;"

            Dim mycommand As OleDbCommand = New OleDbCommand("Select * from [" + GridItemcode + "$];")

            Dim myConnection As OleDbConnection = New OleDbConnection(ConnectionString)


            mycommand.Connection = myConnection

            Dim myReader As OleDbDataReader = mycommand.ExecuteReader()

            While (myReader.Read() And XlSheetRowNo < 5001)



                        '-------Long Number

                        Dim SerialNo As Long

                        SerialNo = myReader.GetValue(1)

                        AddData(GridRowNo, SerialNo.ToString())

                        GridRowNo = GridRowNo + 1

                    Catch ex As Exception

                        '-------String Number

                        Dim SerialNo As String

                        SerialNo = myReader.Item(1).ToString()

                        AddData(GridRowNo, SerialNo)

                        GridRowNo = GridRowNo + 1

                    End Try

                    XlSheetRowNo = XlSheetRowNo + 1

                Catch ex1 As Exception


                End Try

            End While


        Catch ex As Exception

            SBO_Application.MessageBox("Excel File is not open or the File is not in correct format. Please Check the file again...")


        End Try

    End Sub

Any questions or if anyone has any tips for me please post below and don't forget to like and rate!!!.


Amrut Sabnis.