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.
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
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.
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.
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"
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.
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.
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 🙂
11 Comments