Skip to Content

1. Prepare the Excel Files 

In SAP Business One i have created a user defined table of type Master Data called MACROUDO. I have created two user defined fields for this table UDF1 and UDF2.

Firstly create two new tabs in a new Microsoft excel file – tab 1 will contain the database connection details for the SAP Business One database we will connect to via the DI API and tab 2 will contain all the UDO information we want to import into the UDO table we have defined in Business One.

image

image

A. Display the excel Forms menu by clicking View -> Toolbars -> Forms

B. Create a new button on the UDO tab by dragging and dropping the button icon onto the excel file.

C. When the Assign Macro window appears change the name of the button function (this is the name you’ll use in the code)

D. You can change the caption of the button by right clicking on the Button, highlighting the text and replacing it

image

E. We need to open the Visual Basic Editor. To do this click on Tools -> Macro -> Visual Basic Editor.

F. Create a new Module where we will write our code. To do this right click on Microsoft Excel Objects -> Insert -> Module. You can rename Module1 if you wish.

image

G. Add the SAP Buiness One DI API Version reference via Tools -> References. We need this dll so we can connect and access the SAP Business One database.

image

2. How to connect to the DI API

Create a new procedure called Public Sub login() which will read in the values set in the Login tab of the excel sheet and connect to the DI API with these values. If all is successful you will receive a successful error message “Connected to: db name”

image

3. Filling the UDO table 

Now that we are successfully connected to the DI, we need to add the values to our UDO table by reading them in from the excel file. We have set the Range A6 to A11 as i have values only in these fields. So if you have more/less rows this range will need to be changed.

image

4. How to run the program

We need to trigger this code in order for the import to start. So in the button click event we place the following code which will call the login method, call the import_UDO method to fill the UDO table. It will finally display a message to confirm the import is complete.

image

To run the code, simply click on the new button on your excel sheet and viola! I have also attached the excel file so you can download and test it:

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/b0b25168-e25b-2c10-eab7-855eec5517d0   

The UDO functionality is planned to be included in the next DTW patch – DTW 2005.0.27 which is planned to be released with SAP Business One 2007A SP1 PL06 (end of July).

 

Our next blog will cover the topic of DI Performance and how to help find a possible source of a DI Performance issue. So catch you then 🙂

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Malika Sanoune
    Hi,

    We cannot do the import to UDO directly from DTW 88.0.16 (it means SAP 8.0 patch level 15).

    because when I try to do it he show unknown error 1500

    Why ?

    Best Regards

    Malika

    (0) 
    1. Lisa Mulchinock Post author
      Hello Malika,

      I have not heard about this error and according to my tests there is no issue importing to UDOs using the DTW.
      Can you ensure you have filled the templates correctly by reviewing Note 1353428.

      Let me know if you still get the error after checking this.

      Cheers,
      Lisa

      (0) 
      1. Community User
        Desperately seeking help ….

        I am trying to load a UDO table via the DTW.  I did create the templtaes from the DTW using the generate template tool but the import fails with error Unknown error – 1005 !65171. 
        The DTW version id 88.1.6 with a DI API version of 8.81.314

        Can anyone offer any assistence !!!

        (0) 
      2. Madhavan Raghavan

        Hello Lisa,

        I try to simulate in my machine and getting the following error.

        runtime error -1002

        Service Not Found


        It is not passing the last code in the list below which i highlighted and underlined


        If table <> Empty Then  ‘check if table name cell is empty

                ‘this service provides access to UDOs

                Dim oGeneralService As SAPbobsCOM.GeneralService

                ‘Represents a single record of a UDO

                Dim oGeneralData As SAPbobsCOM.GeneralData

                Dim oCmpSrv As SAPbobsCOM.CompanyService

              

                Set oCmpSrv = oCompany.GetCompanyService

                ‘Returns an instance of GeneralService for the specified user-defined object (UDO)

                Set oGeneralService = oCmpSrv.GetGeneralService(table)

        I was trying to send the email to Lisa Mulchinock@sap.com but not accepting.

        Please let me know what to do.

        Regards,

        Maddy

        (0) 
  2. Daniel Ebi
    Hi Lisa!
    I am having trouble to import intrastat data via Workbench.
    It says that the DTWB “Cannot add rows to a MasterData type UserTable U_BNITMDV”.

    I do not understand why! About a year ago and using a older version of the workbench an import of intrastat-data worked just fine!

    Is there a Problem with the release for SAP 8.8 PL19??
    Do I really need to do the visual-basic thing to get the data into the system??

    Thanks for your support!
    Cheers, daniel

    (0) 
    1. Madhavan Raghavan

      Hello Lisa,

      I try to simulate in my machine and getting the following error.

      runtime error -1002

      Service Not Found


      It is not passing the last code in the list below which i highlighted and underlined


      If table <> Empty Then  ‘check if table name cell is empty

              ‘this service provides access to UDOs

              Dim oGeneralService As SAPbobsCOM.GeneralService

              ‘Represents a single record of a UDO

              Dim oGeneralData As SAPbobsCOM.GeneralData

              Dim oCmpSrv As SAPbobsCOM.CompanyService

             

              Set oCmpSrv = oCompany.GetCompanyService

              ‘Returns an instance of GeneralService for the specified user-defined object (UDO)

              Set oGeneralService = oCmpSrv.GetGeneralService(table)

      I was trying to send the email to Lisa Mulchinock@sap.com but not accepting.

      Please let me know what to do.

      Regards,

      Maddy

      (0) 
  3. Hendra Prakasa

    Hi Lisa,

    I’ve tried this method using win 7 32 bit, and it success.

    but when I tried on win 7 64 bit, when executing the macro, get error :

    runtime error ‘429’

    ActiveX component can’t create object

    do u possibly know, what caused the problem ?

    How get I get activeX component ?

    (0) 

Leave a Reply