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

About Me:

Open file browser from SAP B1 and read data from excel sheet 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

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 and rate!!!.

Regards,

 

Amrut Sabnis.