Sap ошибка доступа к приложению ms excel

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.

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, = 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.

V_V_V

17.05.12 — 17:40

Axel2009

1 — 17.05.12 — 17:42

файл то где находится?

V_V_V

2 — 17.05.12 — 17:44

Лоботряс

3 — 17.05.12 — 17:44

У файла вывод должен быть разрешен кажется…

Лоботряс

4 — 17.05.12 — 17:45

Если на сервере с ним работать

Axel2009

5 — 17.05.12 — 17:45

(2) круто. а код сервера откуда запускается?

smaharbA

6 — 17.05.12 — 17:46

у сервера права как на файл так и на ком

V_V_V

7 — 17.05.12 — 17:46

(3) Это где? В свойствах самого файла? На Клиенте добавляет без левых манипуляций…

V_V_V

8 — 17.05.12 — 17:46

(5) На этой же машине

Axel2009

9 — 17.05.12 — 17:47

(8) а если подумать?

V_V_V

10 — 17.05.12 — 17:47

+(8) На моей машине есть и сервер 1С и SQL

V_V_V

11 — 17.05.12 — 17:48

(9) Вот не думается. Чего и спрашиваю…

Axel2009

12 — 17.05.12 — 17:49

(11) ну ты запиши файл текстовый и посмотри где он создастся

V_V_V

13 — 17.05.12 — 17:50

V_V_V

14 — 17.05.12 — 17:59

Может я неправильно сформулировал… Если я выполняю вышеприведенный код &НаКлиенте — отрабатывает без вопросов. Если &НаСервере — валится ошибка.

V_V_V

15 — 18.05.12 — 11:37

V_V_V

16 — 18.05.12 — 11:41

DEVIce

17 — 18.05.12 — 11:51

(14). У компа на котором сервер предприятия крутится есть доступ до этого файла?

pumbaEO

18 — 18.05.12 — 11:54

код, как сохраняешь ФайлРезервовДляКлиента покажи.

V_V_V

19 — 18.05.12 — 11:58

V_V_V

20 — 18.05.12 — 11:59

Повторюсь из (14) — Если я выполняю вышеприведенный код &НаКлиенте — отрабатывает без вопросов. Если &НаСервере — валится ошибка.

pumbaEO

21 — 18.05.12 — 12:01

хотя бы раз excel от имени пользователя 1С запускал?

V_V_V

22 — 18.05.12 — 12:01

+(21) Выполнять надо именно &НаСервере — это будет регламентное задание

V_V_V

23 — 18.05.12 — 12:01

(21) Нет

DEVIce

24 — 18.05.12 — 12:05

Запусти в отладчике и посмотри на момент открытия файла он реально есть?

DEVIce

25 — 18.05.12 — 12:06

DEVIce

26 — 18.05.12 — 12:08

Ну и может файл только на чтение? В темпе он ведь не просто так создался.

V_V_V

27 — 18.05.12 — 12:08

(25) Файл существует, данные в нем имеются, корректные. Открывается, редактируется и тд и тп…

V_V_V

28 — 18.05.12 — 12:10

(26) Редактируется. Хотя это мне и не нужно в данном случае. А в темп — так я его сознательно туда загоняю, через КаталогВременныхФайлов()

DEVIce

29 — 18.05.12 — 12:12

Да и все-таки запусти хоть раз от имени пользователя 1С Ексель интерактивно. Ибо Ексель при первом запуске требует настройки.

pumbaEO

30 — 18.05.12 — 12:16

Если сервер 1С от своего имени запустишь, работает?

V_V_V

31 — 18.05.12 — 12:17

V_V_V

32 — 18.05.12 — 12:18

(30) Наверняка заработает — у меня админские права на локальную машину. Но надо запустить в режиме приближенном к боевому, как на сервере. Там я свои права давать не буду…

pumbaEO

33 — 18.05.12 — 12:20

выбранный режим входа для данного пользователя на этом компьютере  — т.е. процессу не разрешенно работать с рабочим столом, возможно в этом проблема.

V_V_V

34 — 18.05.12 — 12:28

(33) Пока я пользователя USR1CV82 не добавил в DCOM — вообще ругалось на  COMОбъект(«Excel.Application»). С рабочим столом работа и не предполагалась. Из того что я начитался, народу было достаточно добавления в DCOM прав пользователю на запуск Экселя.

V_V_V

35 — 18.05.12 — 12:39

Кажется я упустил один момент — у меня 64-х битная Виндовс 7, Эксель 2010 тоже 64-х битный, а вот сервер 1С крутится 32-х битный. Это важно? Может в этом затыка?

izekia

36 — 18.05.12 — 12:47

(35) да в этом проблема

izekia

37 — 18.05.12 — 12:47

куча ссылок но не на этом форуме

V_V_V

38 — 18.05.12 — 12:49

(37) А дай хоть что-то по этой теме. Уже и так читано-перечитано, прежде чем сюда писать, но может новенькое попадется…

izekia

39 — 18.05.12 — 12:59

(39) Хорошо, спасибо, гляну.

Я разрабатываю решение для связи MS Excel (макрос VBA) с SAP. Иногда слишком сложно связаться со специалистами службы поддержки SAP в любой компании, в которой вы работаете, поэтому лучше найти собственное решение.

По этой причине я начал с основного: «подключить мой собственный профиль пользователя к SAP R/3». Проведя небольшое исследование в Google, я нашел хороший бумаги с подключением, и вот ключевая часть кода:

Dim objBAPIControl As Object 'Function Control (Collective object)
Dim sapConnection As Object 'Connection object
Set objBAPIControl = CreateObject("SAP.Functions")
Set sapConnection = objBAPIControl.Connection

sapConnection.client = "32"
sapConnection.user = "myUser"
sapConnection.Language = "EN"
sapConnection.hostname = "qwerty.example.com"
sapConnection.Password = "myPass"

(и другие значения профиля пользователя……..)

Позже я сделал второй шаг, который читал некоторую таблицу (в данном случае таблицу пользователей).

If sapConnection.logon(1, True) <> True Then
MsgBox "No connection to R/3!"
Exit Sub 'End program
End If
Set objUserList = objBAPIControl.Add("BAPI_USER_GETLIST")
Set objUserDetail = objBAPIControl.Add("BAPI_USER_GET_DETAIL")

returnFunc = objUserList.Call
If returnFunc = True Then
Dim objTable As Object
Set objTable = objUserList.Tables("USERLIST")
ActiveSheet.Cells(1, 1) = "User count :" & objTable.RowCount

Но теперь вот мой вопрос: как запустить какую-либо транзакцию (LM02, LS26, LX03 или т. д.), запущенную из VBA?

Спасибо вам, ребята!

PS. Я использую MS Office 2007 и Windows 7.

— ОТРЕДАКТИРОВАНО:

Эй, я изменил способ, которым я атаковал вызов (я позволю ОП выше помочь другому парню)

Это нужно добавить к первому

Dim RfcCallTransaction As Object
Dim Messages As Object
Dim BdcTable As Object

Подключение к SAP такое же, но после входа в систему:

If objBAPIControl.Connection.Logon(0, False) <> True Then
    Exit Sub
End If

Set RfcCallTransaction = objBAPIControl.Add("RFC_CALL_TRANSACTION_USING") 

здесь вы можете найти «новые» аргументы для RFC_CALL_TRANSACTION старая функция.

    RfcCallTransaction.exports("tcode") = "SE16"
    RfcCallTransaction.exports("mode") = "N"
Set BdcTable = RfcCallTransaction.Tables("bt_data")

До этой части я уверен в трех вещах:

1) Он подключается к SAP R/3.

2) Выполняет транзакцию «SE16»

3) Он может получать пакетный ввод из таблицы BdcTable.

Недостающая часть заключается в том, как «отформатировать» BdcTable (я думаю, она должна быть на языке ABAP), чтобы загрузить точные данные (таблицу), которые я хочу запустить в SE16.

EDITED: я нашел пример таблицы «форматирования», которую я ищу:

add_bdcdata BdcTable, "SAPLSETB", "230", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "DATABROWSE-TABLENAME"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=ANZE"
add_bdcdata BdcTable, "", "", "", "DATABROWSE-TABLENAME", "KNA1"
add_bdcdata BdcTable, "/1BCDWB/DBKNA1", "1000", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "MAX_SEL"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=ONLI"
add_bdcdata BdcTable, "", "", "", "LIST_BRE", "250"
add_bdcdata BdcTable, "", "", "", "MAX_SEL", "5"
add_bdcdata BdcTable, "SAPMSSY0", "120", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "01/02/2012"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=%EX"
add_bdcdata BdcTable, "/1BCDWB/DBKNA1", "1000", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "/EE"
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "I1-LOW"
add_bdcdata BdcTable, "SAPLSETB", "230", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "/EBACK"
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "DATABROWSE-TABLENAME"

Приведенный выше код по-прежнему является частью макроса VBA. Но я не знаю, что означают все эти заливки.

После завершения этой задачи второй задачей является автоматическое сохранение данных в лист MS Excel.

(Не могли бы вы сказать мне, работает ли все редактирование или я должен просто вставить новый код и стереть историю?)

EDITED: я задал тот же вопрос, что и резюме на платформе SCN. Вы можете просмотреть его здесь.

EDITED: Моя цель в этой задаче — запустить транзакцию (TCODE, который я ищу, это LM02, транзакция между ящиками для склада) через макрос VBA, загрузить некоторую дату, извлеченную из файла MS Excel, и выполнить транзакцию.

— Последнее редактирование: кажется, что эту задачу невозможно выполнить без помощи ИТ-поддержки SAP. 19/сен/12 | все еще исследую эту тему. 25 | 09 —> попробую AutoIT для ведения журнала запустите базовый Tcode и отобразите отчет. Я бы прокомментировал свои результаты после того, как дважды проверил их.

Просмотр нерешенных тем
Сообщения без ответов | Активные темы

Автор	Сообщение
Grif	Заголовок сообщения: Не открывается Excel Добавлено: Ср, июл 09 2008, 07:32
Ассистент Зарегистрирован: Чт, фев 21 2008, 09:42 Сообщения: 45	Добрый день При открытии Excel формы в Эксплуотационном приказе выдаётся сообщение , что процес блокирован. Процесс Excel при этом запускается , а форма не выдаётся. При подтверждении открыть другим редактором данные куда то переносяться , в SAP директориях ничего нет.Office переставили не помогло. Подскажите в чём может быть причина.
Вернуться к началу

Vadimus	Заголовок сообщения: Добавлено: Ср, июл 09 2008, 08:29
Старший специалист Зарегистрирован: Чт, сен 08 2005, 13:23 Сообщения: 481 Откуда: Москва Пол: Мужской	1) Накатить последние патчи на SAP GUI 2) Проверить настройки безопасности макросов. 3) Проверить, установлена ли галочка «Trust Access to Visual Basic Project» в настройках безопасности макросов на закладке «Trusted publishers».
Вернуться к началу

MIX82	Заголовок сообщения: Добавлено: Ср, июл 09 2008, 09:07
Зарегистрирован: Пн, фев 04 2008, 06:44 Сообщения: 137	Поставь в EXCEL галочку в сервис-надстройки
Вернуться к началу

3 ответа

Set src = Workbooks("EXPORT.XLSX")

session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&PC"
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").setFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").text = "C:Data"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "data.txt"
session.findById("wnd[1]").sendVKey 11 

Sub text_to_sheet()
Dim wb As Workbook, txt As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("tmp_sheet") 'a temporary sheet
Set txt = Workbooks.Open("C:Datadata.txt")
txt.Sheets(1).Cells.Copy ws.Cells
txt.Close SaveChanges:=False
End Sub

Sub exp_csv()
trgt = Environ("USERPROFILE") & "Desktop"
fn = "desired_filename"
Application.DisplayAlerts = False
ThisWorkbook.Sheets(2).Copy
ActiveWorkbook.SaveAs Filename:=trgt & fn,FileFormat:=xlCSV 'FileFormat:=xlWorkbookDefault , for regular xlsx
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

' Choose one
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long '32 bit Windows
Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As LongPtr '64 bit Windows

Sub closeOtherExcelInstances()
    Dim oServ As Object
    Dim cProc As Variant
    Dim oProc As Object
    Dim currentProcId

    currentProcId = GetCurrentProcessId

    Set oServ = GetObject("winmgmts:")
    Set cProc = oServ.ExecQuery("Select * from Win32_Process where NAME = 'EXCEL.EXE'")

    For Each oProc In cProc
        If oProc.ProcessId <> currentProcId Then
            Debug.Print oProc.Name, oProc.ProcessId
            errReturnCode = oProc.Terminate()
        End If
    Next
End Sub

Dim oShell : Set oShell = CreateObject("WScript.Shell")             'check all process runing

oShell.Run "taskkill /im excel.exe", , True                         'kill firts SAP excel opening attempt
oShell.Run "taskkill /f /im excel.exe", , True                      'kill second system excel opening attempt

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.

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, = 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.