Home > BaaN, ERP, Infor > Excel data upload to BaaN System (OLE Automation)

Excel data upload to BaaN System (OLE Automation)


Most of the times, Infor BaaN users have a requirement of uploading data from excel to BaaN database.This blog post will explain everything about the above topic. Some of the common questions asked are answered below.

Q.1 Is it possible to upload/download data directly from Excel ?
Ans: It is absolutely possible to upload or download data, right from your excel file.

Q.2 Can I use the BaaN business logic, while uploading data ?
Ans : Answer is yes, absolutely yes.

Q.3 Do I need a third party tool for this ?
Ans : No, apart from MS Excel no other third party tool.

Q.4 How will I connect to BaaN ? Dont want to hit the DB at backend.
Ans : You can use OLE to connect to BaaN System and call a BaaN DLL to perform database transactions, database is not directly hit.

Q.5 What is this OLE ?
Ans : Object Linking and Embedding (OLE) is a technology developed by Microsoft that allows embedding and linking to documents and other objects.

Q.6. How exactly this works ?
Ans : You will need to write a VBA code inside excel, the belows points explains the same

To start with, you will have to write a VBA macro in excel, Assuming you know the about macros, I won’t go deep into how to write a macro code, it can all together be a different post. I will jump directly into the macro code and concentrate more on BaaN connectivity.

The first step is create an object for setting a connection to BaaN legacy system. The following code snippets, shows the same.

Dim BaanObj As Variant
Dim value As String

Set BaanObj = CreateObject(value)

The CreateObject method creates the actual underlying IDL object. The argc, argv, and argpal parameters are used to supply parameters to the underlying IDL object’s Init method. If the Init method does not have any parameters, the caller sets argc, argv, and argpal to 0, NULL, and NULL, respectively.

The OLE Automation object, i.e BaanObj in this case have methods and properties. Methods are nothing but functions and properties are attribute that has a value. Property values can be set and/or retrieved.

The methods available for BaaN Application Objects are

  • ParseExecFunction “<DLL_NAME>”,”<FUNCTION_CALL>”
  • Quit

Properties available for BaaN Application objects are

  • Timeout
  • FunctionCall
  • Error
  • ReturnValue

You can see the variable “value” used as a argument for CreateObject, now to connect to BaaN Sytem, we will be making use of the BW configuration file. Assign the classname defined under the automation tab in BaaN BW configuration dialog box to the variable “value”. This will establish connection with the BaaN legacy sytem using the user id defined in the configuration. If username/password is not saved in the configuration, then it will prompt for the same.

Now the next step is to define the BaaN DLL and call the function defined in the DLL. Below code snippet demonstrate the same.

Dim B_function As String
B_function = <FUNCTION_NAME> (<arg-1>,<arg-2> ,..,<arg-n>)

BaanObj.ParseExecFunction “otdpurdllxxxxxx”, B_function

This will call the Baan DLL, where in the Baan Business logic is written, all db transactions are performed using this DLL.

Last but not the least,  for ending the automation you will need to use the “Quit” method as in the below code snippet.

BaanObj.Quit
Set BaanObj = Nothing

Quit method will end the lifecycle of the object. The second line in the code will initialise it to “Nothing”, to free the memory allocation.

There are some limitations though in this method

  •  Array Arguments not supported
  • Variable number or Optional  arguments not supported
  • 4K Limit on the max size of function call (bshell limit)
  • 4K Limit on the max size of ReturnValue (bshell limit)

Example Code (BaaN DLL)

function extern string upload.currency.rates(     ref string ccur,
ref string srate,
ref string prate)
{
code for updating the currency rates

}

Example Code ( Excel VBA )

      Dim RetVal As String
      Dim BaanObj As Object

      Sub Function_XYZ ()
          On Error GoTo ConnectionError

          Set BaanObj = CreateObject("Baan.Application")

          On Error GoTo AutomationError

          BaanObj.ParseExecFunction "<BaaN_DLL>", "<BaaN_FUNCTION>"
          If (BaanObj.Error <> 0) Then GoTo AutomationError
          	RetVal = Baan4Object.ReturnValue

          Row = 1
          Column = 1 

	  While (RetVal <> "") 

                Worksheets("Main_Sheet").Cells(Row, Column) = RetVal
                Row = Row + 1 

                If Row > 15 Then
		   Row = 1
		   Column = Column + 2
              	End If

             	BaanObj.ParseExecFunction "<BaaN_DLL>", "<BaaN_FUNCTION>" 

            	If (BaanObj.Error <> 0) Then GoTo AutomationError
             	   RetVal = BaanObj.ReturnValue
          Wend

          BaanObj.Quit
          Set BaanObj = Nothing
          Exit Sub

      ConnectionError:
          MsgBox "Connection Error"
          Exit Sub

      AutomationError:
          MsgBox "Automation error"
          BaanObj.Quit
          Set BaanObj = Nothing
          Exit Sub

      End Sub
Advertisements
Categories: BaaN, ERP, Infor Tags: , ,
  1. February 12, 2013 at 9:31 pm

    “Excel data upload to BaaN System (OLE Automation)
    Techbuzz” was in fact a fantastic posting.
    If only there was a whole lot more personal blogs similar to
    this amazing one in the the net. Anyhow, thanks for your personal time, Scott

  2. May 7, 2013 at 12:15 am

    I just like the valuable info you provide on your articles.
    I will bookmark your weblog and take a look at once more here frequently.
    I am quite certain I will be told plenty of new stuff right here!
    Good luck for the following!

  3. December 6, 2014 at 12:54 pm

    Hi there, after reading this remarkable paragraph i am also happy to share my knowledge here with friends.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: