Data from SAP to MS Office Applications
Using the connection SAP with MS Office, users spreads data from SAP application to right within Microsoft Office desktop applications, including Excel, Outlook, Word and Power point.
With the SAP-MS Office connectivity, end-users can interact with SAP transactions directly within their Excel spreadsheet, their Outlook e-mail screen, and their Word documents and on their Power point presentations.
Here we have a simple application to connect SAP with MS-office tool’s Excel.
But before that……Why? How? And Use!
Why Data from SAP to MS Office Applications
In spite of large moneys in SAP, most business users continue to be unsatisfied with it because data from SAP is not easily accessible in their favorite desktop productivity tools — Microsoft Excel, Microsoft Outlook and Microsoft Word. Since Microsoft Office Applications are not connected to SAP, users are forced to
- Use error-prone cut-copy-paste to populate their Excel spreadsheets.
- Switch screens from Outlook to gather data and take decisions as they respond to e-mails.
- Manually update data from Excel Spreadsheets to enterprise applications.
Benefits of Data from SAP to Microsoft Office Applications
SAP-MS Office Connectivity extends data from SAP to Microsoft Excel, Microsoft Outlook and Microsoft Word using a SOA based Information Delivery Server and Microsoft Office Add-ins, and provides the following benefits
- Eliminates cut-and-paste based data collecting mechanism from SAP to Microsoft Excel.
- Enables users to get real-time data from SAP within Excel with a single-click Refresh.
- Enables users to query and update SAP transactions from within Outlook screens.
- Allows the users to update SAP from within Excel, Outlook and Word.
- Avoids any data-integrity issues with copying and reduces compliance issues.
Who Benefits
- Production and Logistics Managers who want to create Excel reports from latest SAP data.
- Managers who want to track projects and status automatically as they receive status e-mails.
- Analysts who want to combine data from SAP and other enterprise data stores for budgeting purposes.
Example:
Here is the main part comes up. We shall take simple scenario for SAP with MS Excel connectivity. So let’s begin!!
Just before start, I would like to give little overview on what we are actually going to do. So, here are the scenarios!!
Excel File Scenarios
- Create a MS excel file for user input.
- Create user interface in excel sheet to input data for the customer master and output cells.
- Apply VB code to make connection with SAP.
- Read the input data for customer master from the excel file sheet.
- Fetch data from SAP using connection code from SAP.
- Display output within excel sheet itself.
SAP Scenarios
- Create a function module in ABAP that is “Remote Enabled”.
- Create IMPORT, EXPORT and TABLES parameters as per requirement from MS excel.
- Write logic to display customer master details based on input from Excel file.
- Activate the function module.
Well……………“A picture says thousand words”. So, let’s begin!
MS Excel – User Interface
First of all, lets create MS excel file with customer master input. We shall create simple selection screen for user input.
Well……filling cell values are simple here but how to add buttons (Get Address and Reset Output) here? This was also new in my case since new version of MS office.
Just follow following steps for this:
Click MS office button and select “Excel Options” button from the menu.
You will see here a new tab named “Developer”
Click on “Insert” and then select “Button” from the form control. Later on you can double click on the button and set its properties (Caption, Color, etc.)
Congratulations! Our input screen is finished here. Let’s design our output screen now.
I have created my output screen just after the input screen. You can create anywhere in the excel file. Here terms ‘anywhere’ descries to other worksheet also.
The output screen is simple as you see. Nothing much to do here. I just simple colored the cells for better look.
Great! Looks like we finished user interface here. (Did we miss something…’Naah’ for now!)
SAP – Select Data
Since we are taking simple example to fetch detail from customer master, let’s create a function module for it.
Create a function module.
Enter function group and Short text.
Make sure your function module is “Remote Enabled”.
Now let’s create “Tables” parameters:
Here we have two “Tables” parameters
- ET_KUNNR (Input) : Customer numbers pass in to the function module
- ET_CUST_LIST (Output): Data will be fetched in function module and display on the excel sheet.
- Note: We have created a Z structure (ZNM_CUST_LIST) for the output list. Here are the fields for the structure:
We are all set with input and output structures.
Now, let’s fetch the data. Here is the only coding part came up in SAP. Look at below screen.
Here, we are selecting all the customer details from the KNA1 (customer master) table from the input table (ET_KUNNR) from excel file and return to the table (ET_CUST_LIST).
Now we have all the records in the table ET_CUST_LIST.
So, again congratulation friends! You have completed most of the things. The remaining step now is to display the list in the excel file.
Let’s now connect the SAP and MS Excel.
Go to the ‘Developer’ tab and click on “Visual Basic” icon.
The VB editor will be opened and we are going to write the code for connection of excel and SAP with sending and receiving data.
Here is the variable declaration list. These are the global variables. Let’s understand each of them.
|
Variable |
Description |
|
objBAPIControl |
For creating object to access SAP functions. |
|
objgetaddress |
To make BAPI function call via objBAPIControl. |
|
vLastRow |
Last row of output list |
|
vRows |
Total number of records returned from the SAP |
|
vcount_add |
Variable for increment records |
|
Index_add |
Variable for next record (Index) |
|
objaddress |
Object for SAP table (for customer master output) |
|
objkunnr |
Object for SAP table (for customer master input) |
|
Variable |
Description |
|
LogonControl |
Logon control is to make login in SAP. |
|
R3Connection |
Ro make connection to SAP R/3 using logon control. |
|
retcd |
Return Code |
|
SilentLogon |
If ‘True’ no popup will ask to enter SAP login details |
Here are the properties for the “Get Address” button.
Let’s code when “Get Address” button is clicked.
Setup the local variables
Private Sub GetAddress_Click()
Now let’s setup the connection with SAP R/3 using following code.
Let’s do SAP Login here.
Perfect!! We have made the connection with SAP R/3. Now we are able to send and receive the data from MS Excel to SAP R/3 and vice versa.
But how to send my customer details and receive? Hmmm…..we need to use internal tables those are created in SAP function module. (Do you remember?….NO?… checkout Tables parameters in function ZNM_GET_CUSTOMER_DETAILS). So, let’s do this.
Here, ZNM_GET_CUSTOMER_DETAILS is our function module created in SAP.
ET_KUNNR: Customer details input details
ET_CUST_LIST: Customer output details.
We are reading here each cell from excel worksheet for input.
Here we have called the FM and passed the input details to process.
Result:
vcount_add returns total number of records from the SAP. And we have already set the loop to display records in the cells.
Here, R3Connection.Logoff is to sign off from your SAP account.
Here is the output screen:
Wow!! We have output result. Good job!! But what is the use of another button “Reset Output” here? Yes…good question.
The “Reset Output” button will clear all the data and messages from the screen. Look at below code:
———————————————————————————————————————–
Now we have completed the entire example here. N’ joy.
Here is the entire code:
SHEET1:
Option Explicit
Private LogonControl As SAPLogonCtrl.SAPLogonControl
Private R3Connection As SAPLogonCtrl.Connection
Private TableFactory As SAPTableFactory
Public Functions As SAPFunctionsOCX.SAPFunctions
Dim objBAPIControl, objgetaddress As Object
Dim vLastRow, vRows As Integer
Dim vcount_add, index_add As Integer
Dim rng As Range
Public objaddress, objkunnr As SAPTableFactoryCtrl.Table
MODULE:
Sub GetAddress_click()
Dim retcd As Boolean
Dim SilentLogon As Boolean
‘ Set Connection
Set LogonControl = CreateObject(“SAP.LogonControl.1”)
Set objBAPIControl = CreateObject(“SAP.Functions”)
Set R3Connection = LogonControl.NewConnection
R3Connection.Client = “700”
R3Connection.ApplicationServer = “”
R3Connection.Language = “EN”
R3Connection.User = “”
R3Connection.Password = “”
R3Connection.System = “”
R3Connection.SystemNumber = “”
R3Connection.UseSAPLogonIni = False
SilentLogon = False
retcd = R3Connection.Logon(0, SilentLogon)
If retcd <> True Then MsgBox “Logon failed”: Exit Sub
objBAPIControl.Connection = R3Connection
Set objgetaddress = objBAPIControl.Add(“ZNM_GET_EMPLOYEE_DETAILS”)
Set objkunnr = objgetaddress.Tables(“ET_KUNNR”)
Set objaddress = objgetaddress.Tables(“ET_CUST_LIST”)
Dim sht As Worksheet
Set sht = ThisWorkbook.ActiveSheet
‘ Changes for the Loop
‘Dim int1 As Integer
‘Do While sht.Cells(int1, 2).Value <> ” “
‘ Changes for the Loop End
If sht.Cells(6, 2).Value <> ” ” Then
‘If ThisWorkbook.ActiveSheet.Cells(6, “B”).Value <> ” ” Then
‘objkunnr.Rows.Add objkunnr.Value(1, “SIGN”) = ThisWorkbook.ActiveSheet.Cells(6, 2).Value
objkunnr.Rows.Add
objkunnr.Value(1, “SIGN”) = sht.Cells(6, 2).Value
‘objkunnr.Value(1, “OPTION”) = ThisWorkbook.ActiveSheet.Cells(6, 3).Value
objkunnr.Value(1, “OPTION”) = sht.Cells(6, 3).Value
‘objkunnr.Value(1, “LOW”) = ThisWorkbook.ActiveSheet.Cells(6, 4).Value
objkunnr.Value(1, “LOW”) = sht.Cells(6, 4).Value
‘objkunnr.Value(1, “HIGH”) = ThisWorkbook.ActiveSheet.Cells(6, 5).Value
objkunnr.Value(1, “HIGH”) = sht.Cells(6, 5).Value
‘End If
End If
returnfunc = objgetaddress.call
If returnfunc = True Then
vcount_add = objaddress.Rows.Count
For index_add = 1 To vcount_add
vRows = 11 + index_add
sht.Cells(vRows, 2) = objaddress.Value(index_add, “KUNNR”)
sht.Cells(vRows, 3) = objaddress.Value(index_add, “LAND1”)
sht.Cells(vRows, 4) = objaddress.Value(index_add, “NAME1”)
sht.Cells(vRows, 5) = objaddress.Value(index_add, “ORT01”)
sht.Cells(vRows, 6) = objaddress.Value(index_add, “PSTLZ”)
sht.Cells(vRows, 7) = objaddress.Value(index_add, “REGIO”)
sht.Cells(vRows, 8) = objaddress.Value(index_add, “KTOKD”)
sht.Cells(vRows, 9) = objaddress.Value(index_add, “TELF1”)
sht.Cells(vRows, 10) = objaddress.Value(index_add, “TELFX”)
Next index_add
End If
‘ If address not exist then Show error
If vcount_add = “” Then
sht.Cells(10, 11) = “Invalid Input”
Else
‘ ActiveSheet.Cells(10, 12) = “BAPI Call is Successfull”
‘ ActiveSheet.Cells(11, 12) = vcount_add & “rows are entered”
sht.Cells(10, 12) = “BAPI Call is Successfull”
sht.Cells(11, 12) = vcount_add & ” rows are entered”
End If
R3Connection.Logoff
End Sub
COMMON ERROR: User defined Type not defined
So…let’s try to remove the one. As our friends have posted solutions, here are the steps to avoid the error.
1. Go to your VBA Project and Select Tool –> References.
2. “References” window will be opened as below.
3. Select “Browse…” and select .OCX files for Active X control from below path. (SAP GUI 7.3 already installed on my machine)
4. Select wdtaocxU.ocx, wdtfuncU.ocx, wdtlogU.ocx files from the
location.Add wdobapiU.ocx if you are needed.
5. Make your references are selected. Press OK. And save.
6. That’s it.
Nicely explained. a step-by-step guide. Thanks for sharing!
Excellent tutorial. Thank you!
This is great! Is it possible to integrate date from more than one SAP function module to a single excel file?
Very useful. Thank you!
No problem. Good luck buddy!
I can’t find the wdtaocxU.ocx, wdtfuncU.ocx, wdtlogU.ocx
currently i have reference set to SAP GUI Scripting API , is it the same ?
No, it’s sapfewse.ocx
the wdt files are after wdb
Hi Sandra,
Thanks for the reply but my doubt still continues( sorry to ask silly )
I have selected the “sapfewse.ocx” from ” Browse” section then which one should i select as
reference in the Tool>References
I mean: the SAP GUI Scripting API correspond to the file sapfewse.ocx, i.e. it’s not related to the wdt files you are looking for
Hi,
I struck very badly with the below issue.
I have to export data from SAP Tables (Eg: BUT000) to Excel. But I dont have Edit access in SAP only thing is I have view access i.e I cant write any program in ABAP editor. So Please help me in quering tables of SAP using VB Macro. I have to Query BUT000 table and fetch the information when BPID=”100002345″. Please help me.
Thanks,
Hari.
Hari,
You could use the generic RFC function module RFC_READ_TABLE.
You give it the table and the selections and it will return the data. Try it in SE37.
Maybe there are some examples if you search.
Very useful. Thank you!
Hi – I am getting “User defined Type not defined” error on “SAPTableFactoryCtrl.Table”
can you tell me which REFERENCE do I need to select in EXCEL.
Thanks!
Hi Praveen,
Please Declare the below in VB Sheet1
Option Explicit
Private LogonControl As SAPLogonCtrl.SAPLogonControl
Private R3Connection As SAPLogonCtrl.Connection
Private TableFactory As SAPTableFactory
Public Functions As SAPFunctionsOCX.SAPFunctions
Dim objBAPIControl, objgetaddressn As Object
Dim vLastRow, vRows As Integer
Dim vcount_add, index_add As Integer
Private objaddress, objkunnr As SAPTableFactoryCtrl.Table
It will not give any error.
Hello.
I am still having the error “User defined Type not defined“, do I need to select any reference?
Thank you in advance,
Jeffrey
Hi Rohny,
When I am trying to login the SAP system from Excel it is showing me an error
When I am connecting using remote desktop of my client
SAP_CMINIT3 : rc=20 > Connect to SAP gateway failed and please find the details:
Error Group
RFC_ERROR_COMMUNICATION
Message
SAP_CMINIT3 : rc=20 > Connect to SAP gateway failed
Connect_PM GWHOST=212.78.237.247, GWSERV=sapgw00, SYSNR=00
LOCATION CPIC (TCP/IP) on local host
ERROR partner ‘212.78.237.247:3300’ not reached
TIME Mon Aug 26 15:03:37 2013
RELEASE 720
COMPONENT NI (network interface)
VERSION 40
RC -10
MODULE nixxi.cpp
LINE 3286
DETAIL NiPConnect2: 212.78.237.247:3300
SYSTEM CALL connect
ERRNO 10060
ERRNO TEXT WSAETIMEDOUT: Connection timed out
COUNTER 1
*******************************************************************************************
When I am trying it from my system:
CMALLC: rc=27 > Connect from SAP Gateway to RFC server failed.
Please find the Details below:
Error Group
RFC_ERROR_COMMUNICATION
Message
CMALLC : rc=27 > Connect from SAP gateway to RFC server failed
Connect_PM GWHOST=212.78.237.247, GWSERV=sapgw00, SYSNR=00
LOCATION SAP-Gateway on host HEXLSVR001.dixons.co.uk / sapgw00
ERROR timeout during allocate
TIME Mon Aug 26 15:14:24 2013
RELEASE 720
COMPONENT SAP-Gateway
VERSION 2
RC 242
MODULE gwr3cpic.c
LINE 2025
DETAIL no connect of TP sapdp00 from host 212.78.237.247 after 20 sec
COUNTER 2
*******************************************************************
Can you please tell me anything further has to be done other than the mentioned in the blog.
Hi Giriesh
I’m facing the same issue here and i’d like to ask you if you found any solution on this. i’ve added the references wdtaocxU.ocx, wdtfuncU.ocx, wdtlogU.ocx wdobapiU.ocx but this didn’t have any effects on this issue
RFC_ERROR_COMMUNICATION
Mensaje
SAP_CMINIT3 : rc=20 > Connect to SAP gateway failed
Hello,
Thank you for sharing knowledge
all the best erwin
Good one.
Hello.
I am having the following error “User defined Type not defined“, when the system tried to compile line:
Public objaddress, objkunnr As SAPTableFactoryCtrl.Table
Do I need to select any reference?
Thank you in advance,
Jeffrey
Hi Giriesh,
I am getting “User defined Type not defined” error on Public Functions As SAPFunctionsOCX.SAPFunctions.
After implementing the changes in your comment.
Can you please help me with the issue.
Thanks in advance,
Sidd
Hi Siddharth,
Paste this code in the sheet1 of your VB Script.
Hope you are doing it in the Module and because of that you are getting the error.
The code below to enter:
Option Explicit
Private LogonControl As SAPLogonCtrl.SAPLogonControl
Private R3Connection As SAPLogonCtrl.Connection
Private TableFactory As SAPTableFactory
Public Functions As SAPFunctionsOCX.SAPFunctions
Dim objBAPIControl, objgetaddressn As Object
Dim vLastRow, vRows As Integer
Dim vcount_add, index_add As Integer
Private objaddress, objkunnr As SAPTableFactoryCtrl.Table
I am unable to attach screen shot, so I will try to explain it in words:
choose visual basic option from the developer key, you can see
VBAProject(name of your excel) followed by Microsoft excel projects folder and if you expand it you can see the sheet1, were you can post the above code. If you are still facing the issue please revert back.
With Regards,
Giriesh M
Hi ,
Thanks for your reply but i am still getting the same error.
Error with
Public Functions As SAPFunctionsOCX.SAPFunctions as not defined.
Regards,
Sidd
Hi Giriesh,
I have tried a lot but nothing works same error everytime.
I wanted to know is there any tools->references needs to be enabled for this.
If not then, please let me know the solution.
Regards,
Sidd
Hi Siddharth,
You dont want to do anything explicit other than mention in this blog. I am not in the exact position to say what went wrong. Please mention your mail Id so that I can send the excel which I have done for your reference. (But it is not completed till the end, as I am facing connectivity issue).
With Regards,
Giriesh M
Hi Giriesh,
Thanks for sharing such a wonderful tutorial and I am trying to implement the same this, but i am getting error ” User defined type not defined”.
If possible can you send me the excel. That will be really help full.
my id id: REMOVED BY SCN MODERATOR
Regards
Neeraj Tiwari
Hi Neeraj,
You can send me a mail. Find my mail Id in my profile.
With Regards,
Giriesh M
Indeed…Nicely explained. a step-by-step guide. thank you very much. But now, can you give us step-by-step guide for transferring data from Excel to SAP??? Means….I have data in Excel sheet and I want to transfer that data in SAP. And one more thing…..Can we connect to sap without code(I mean not directly through excel…..pls read above comments…lots of people facing “User defined Type not defined” error on Public Functions As SAPFunctionsOCX.SAPFunctions problem……thats why I am asking).
Thanks in advance……..waiting for your reply with Excel to SAP data transfer tutorial……
Good explanation to read the data, I have also done a similar way but Data Save mode.
Here’s the link.
Insert Data Via BAPI Using Excel-VBs
Thanks.
Avirat
Very Informative..
heloo,
nice explanation. everythig seems soothing.
but hardly know anything of vb. just want to know where to write vb code.
do we need to install some another software .
please guide me where to code this vb code.
i mean platform…just like we write abap code in abap editor.
here abap is the language and abap editor is the platform.
Moreover, i dont see DEVELOPER option in our excel. how to enable that.
Hi Abhishek,
You have to make some configuration in Excel for getting the Developer tab. Check in Google as it varies for MS Office versions. The code used is VB Script.
Cheers!!!
Giriesh M
ya…i got it girish.
but still have confusion. from where to declare these variables?
Hi Giriesh,
I am getting “User defined Type not defined” error on ‘Public objaddress, objkunnr As SAPTableFactoryCtrl.Table ‘ and can’t declared .
Can you please help me with this issue.
Thanks ,
Eli Pai
Hi Eli,
I have modified some of the codes done by Nimesh Master. You can have a look and make changes to the Macro excel. If still you have problem send a mail to me (avail from business card). I can send you the excel for reference, but before that try from the document and explore to know much about it.
I don’t find any option to attach the file here else I would upload it.
With Regards,
Giriesh M
Dear Giriesh,
I am also getting the same error, “User defined Type not defined”. I have send you mail on your email id, please send me the excel in reply as I am unable to find the resolution for this error. Or else please specify here what code you have modified so that we all can be benefited from your learning as well.
Thanks in advance for your help and support.
Regards,
Prameet Gopal Verma
I used an ActiveX control button rather than a forms button. Because of this I had to write the full code in the Sheet code pane, rather than in the module to trigger the event when clicking the button.
As a sideeffect, the code that was previously stated in the module, is now subject to the “Options Explicit” statement, which changes your variables from the type variable to what needs to be an explicit type.
There are however a couple of mistakes in the declarations. In VBA you cannot declare two variables at once like this. Try writing this instead:
Dim objBAPIControl As Object, objgetaddress As Object
Dim vLastRow As Integer, vRows As Integer
Dim vcount_Add As Integer, index_add As Integer
Public objaddress As SAPTableFactoryCtrl.Table, objkunnr As SAPTableFactoryCtrl.Table
Also, one variable has not been declared at all, this will also need to be done inside the sub getAddress_Click()
Dim returnFunc As Boolean
Regards,
Niels
Be careful with building VBA macros using SAP Automation based on remote functions.
This functionality is going out of maintenance and may stop to work in near future. Read the thread http://scn.sap.com/thread/3445197. I can reccomend the CCo library instead since it uses SAP NetWeaver RFC API. I used it writing EasyInput tool and it works perfectly.
Thank you. This will be very helpful.
Very nice blog , very helpful.
Anybody having the error ” User defined Type not defined” . The solution is to add Three references to your VBA Project.
– WDTLOG.OCX (non unicode)
– WDTFUNC.OCX (non unicode)
– WDTTAOCX.OCX (non unicode)
or
– WDTLOGU.OCX (unicode)
– WDTFUNCU.OCX (unicode)
– WDTTAOCXU.OCX (unicode)
These can be found in the SAPGUI Program Folder…
-Bob
Friends,
Thanks for the posts and comments. Apology for not following the post since was little busy with else.
I saw many posts related to “User defined Type not defined“.
So…let’s try to remove the one. As our friends have posted solutions, here are the steps to avoid the error.
1. Go to your VBA Project and Select Tool –> References.
2. “References” window will be opened as below.
3. Select “Browse…” and select .OCX files for Active X control from below path. (SAP GUI 7.3 already installed on my machine)
4. Select wdtaocxU.ocx, wdtfuncU.ocx, wdtlogU.ocx files from the
location.Add wdobapiU.ocx if you are needed.
5. Make your references are selected. Press OK. And save.
6. That’s it. Try your luck!!
Dear Team,
I tried this in my test system, but does not gets the output.I debug and found objaddress.Rows.Count does not receive any count.
that vcount_add = objaddress.Rows.Count
Although when I tried with BAPI , it runs successfully in SAP.
Hi Prem,
some reasons why a BAPI behaves differently when called via RFC than when executed in SE37 are listed in SAP Note 206068. Perhaps one of these also applies here?
If not: make an RFC trace of your scenario, then we can see, what input data your application sends to SAP, and what kind of response SAP returns.
Best Regard, Ulrich
Need to put loop in BAPI in SAP in order to ready the table value in where condition.
Anybody upgrade to GUI 750, and make required changes to allow previously running VBA code to continue to work?
I posted the following to SCN.
http://SCN posting regarding VBA and GUI 750
Hi Bruce,
could you correctyour URL please?
Thanks your help, Zsolt
When I do the steps as mentioned, the excel pop up an error message:
run-time error ‘429’: ActiveX component can’t create object
Do any body know where I’m wrong?
I solved it by myself.
I uninstall the office-64 and then install the office-32