Open file browser from SAP B1 and read data from excel sheet

Purpose of the Document:

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

        Try

            ShowFolderBrowserThread = New Threading.Thread(AddressOf ShowFolderBrowser)

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

                ShowFolderBrowserThread.SetApartmentState(System.Threading.ApartmentState.STA)

                ShowFolderBrowserThread.Start()

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

                ShowFolderBrowserThread.Start()

                ShowFolderBrowserThread.Join()

            End If

            While ShowFolderBrowserThread.ThreadState = Threading.ThreadState.Running

                System.Windows.Forms.Application.DoEvents()

            End While

            If FileName <> “” Then

                Return FileName

            End If

        Catch ex As Exception

            ‘SBO_Application.MessageBox(“FileFile” & ex.Message)

            MessageBox.Show(ex.ToString())

        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

        Try

            OpenFile.Multiselect = False

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

            Dim filterindex As Integer = 0

            Try

                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

                        OpenFile.Dispose()

                    Else

                        System.Windows.Forms.Application.ExitThread()

                    End If

                Next

            End If

        Catch ex As Exception

            SBO_Application.MessageBox(“File Format is not correct. Please Check the file again…”)

            ‘SBO_Application.MessageBox(ex.Message)

            FileName = “”

        Finally

            OpenFile.Dispose()

        End Try

        ‘To Read data from Excel Sheet

        If (FileName <> “”) Then

            Read_Excel(FileName)

        Else

        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

            Get

                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

        Try

            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)

            myConnection.Open()

            mycommand.Connection = myConnection

            Dim myReader As OleDbDataReader = mycommand.ExecuteReader()

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

                Try

                    Try

                        ‘——-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

                    ‘SBO_Application.MessageBox(ex1.ToString())

                End Try

            End While

            myConnection.Close()

        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…”)

            ‘SBO_Application.MessageBox(ex.ToString())

        End Try

    End Sub

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

Regards,

Amrut Sabnis.