Excel to SAP R/3

[Former Member]

Hi All,

I need to create automation for uploading Excel data onto SAP R/3 using VBA.Have never done this before.Hence a few questions.

1.Once SAP is installed what references do i need to put in the VB Editor under Tools->references in order to connect to SAP and use its objects?

2.Can i achieve uploading of data from Excel using ODCBC.

3.I searched the net for excel to SAP links and found a term called RFC functions.What is RFC.

4.Do we need ABAP coding as well to achieve the same?

If someone can help me with the code for uploading data from excel to SAP,it would be great.Any reply in this regard will be highly appreciated.

Rgds

[Former Member]

You don´t need any reference. Please check this code (enjoy it!):

Public Sub Btncambiardescripcionmaterial()

Dim conn As New SAPConection

Dim sesion As Object

Set sesion = conn.sesion

j = 1

i = 1

Do While Cells(i, j) <> ""

Cells(i, j + 2) = modificardescripcionmaterial(sesion, Cells(i, j), Cells(i, j + 1))

i = i + 1

Loop

conn.Borrarsesion

Set conn = Nothing

MsgBox ("OK")

End Sub

Public Function modificardescripcionmaterial(sesion As Object, material As Variant, descripcionnueva As Variant)

On Error GoTo ext

sesion.findById("wnd[0]").maximize

sesion.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm02"

sesion.findById("wnd[0]").sendVKey 0

sesion.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = material

sesion.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 6

sesion.findById("wnd[0]").sendVKey 0

sesion.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").Text = descripcionnueva

sesion.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").caretPosition = 25

sesion.findById("wnd[0]/tbar[0]/btn[11]").press

modificardescripcionmaterial = ""

modificardescripcionmaterial = sesion.findById("wnd[0]/sbar").Text

Exit Function

ext:

modificardescripcionmaterial = ""

modificardescripcionmaterial = sesion.findById("wnd[0]/sbar").Text

End Function

Class module SAPConection:

Property Get sesion() As Object

If Not IsObject(App) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(Connection) Then

Set Connection = App.Children(0)

End If

If Not IsObject(session) Then

Set session = Connection.Children(0)

End If

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject App, "on"

End If

Set sesion = session

Set SapGuiAuto = Nothing

Set App = Nothing

Set Connection = Nothing

Set session = Nothing

Set WScript = Nothing

End Property

Public Sub Borrarsesion()

Set session = Nothing

End Sub

Excel file:

Material_code | New_description | Here the result!

Regards,

Arturo Bernal

abmemail@yahoo.com

[Former Member]

Hi Arturo,

The very first line of the code is not working when executed from EXCEL VBA.

Extract of the code ----->

Public Sub Btncambiardescripcionmaterial()

Dim conn As New SAPConection         ====> Problem on this line [ Compile Error :  User-Defined Type Not Defined]

Dim sesion As Object

Set sesion = conn.sesion

Your help would be appreciated

[former_member213011]

Hi Nag,

Did you create the Class Module SAPConnection?

[Former Member]

Dear Sayuti,

I just copied the code mentioned above, no separate CLASS was written. Your guidance will be appreciated

[former_member213011]

Dear Nag,

In the VBE, you need to insert a Class Module like you insert a normal VBA Module. Rename the Class Module to SAPConnection. Then copy Arturo's code from the Property Get sesion()... into that Class Module. Then try and re-run the 'Btncambiardescripcionmaterial' procedure.

[Former Member]

Dear Sayuti,

Thnx for the guidance, on creating the CLASS MODULE as explained, now getting error in the Class

Line " Set Connection = App.Children(0)"   [Error : Runtime Error 614, Application Defined or Object Defined Error

[former_member213011]

Dear Nag,

Try changing the object name 'Connection' into some other word e.g 'oConnection'. 'Connection' is a reserved keyword in Excel if I'm not mistaken.

[Former Member]

hi,

this <a href="/people/kathirvel.balakrishnan2/blog/2006/05/08/data-upload-into-sap-from-microsoft-excel-150-abap-part might help you get a start with RFC and excel.

if you use MS Office 2003 and SAP WAS ABAP Rel >= 6.40 you can download and install the Webservice Developer Kit 2.1 for VBA from microsoft and communicate very easily between excel and sap using webservices on ABAP and consume them from any MS Office application.

regards,

anton

[gregorw]

Hi Anton,

after a long search I've found this Download:

<a href="http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=FA36018A-E1CF-48A3-9B35-169D819ECF18">Microsoft Office 2003 Web Services Toolkit 2.01</a>

Regards

Gregor

[Former Member]

Gregor,

sorry for not having been more exact. That's the right thing you found and it really rocks once you know VBA(and its damn structures).

With this library I was able to create cool, performant and small footprint apps in excel, word and especially outlook, where I could really easily create/update/synchronize appointments with such stored in an ERP app. I called it my 'Mini Duet' (MiniMendocino at that time actually).

I think you'll like it.

regards,

anton

[gregorw]

Hello Anton,

sounds great. Are you able to Blog about you Application? We have a similar requirement because of the limitations of the SAP CRM Groupware Connector. Let's show SAP what's possible to do :-).

Regards

Gregor

[Former Member]

hi there,

sorry for not responding but I've been on holidays for some time now.

I'll consider to give some directions but I guess I'll post it here since I never came past my junior blogger status and at some time I lost my 'secret junior blogger license URL' and for me it's too complicated and too much of a hassle to re-gain it ... but stay tuned here, I'll try to find some code examples.

cheers, anton

[Former Member]

okay, here we go. As said before, I'll post here a series of comments outlining the use of webservices in the office2003 family to interact with an NW ABAP backend.

Specs: Office2003 with the Webservice Toolkit installed (Gregor's link; read the instructions carefully, especially make sure that you have the MSXML3.0-dll installed too); ABAP 6.40+ (I'll use a 7.0 system here); PHP5.2.0 with soap enabled for some quick tests;

In this first and easiest example we will specify a report within a Word document and download the coding to the Word document.

First we create the webservice on the backend, that is, a function module and let run the webservice wizard over it:

FUNCTION ZTW_READ_REPORT.

*"----

-

---

""Lokale Schnittstelle:

*" IMPORTING

*" VALUE(PROGRAMM_NAME) TYPE SY-REPID

*" EXPORTING

*" VALUE(LINES_OF_REPORT) TYPE ZTW_TABLE_OF_CODE_LINES

*" VALUE(ERRORSTRING) TYPE STRING

*"----

-

---

errorstring = ''.

refresh lines_of_report.

read report programm_name into lines_of_report.

if lines_of_report is initial.

errorstring = 'No such report!'.

endif.

ENDFUNCTION.

ZTW_TABLE_OF_CODE_LINES is a table based on the structure DDS02.

Very simple, return the coding for a given report name or 'No such report!' in case it does not exist. RFC enable the FM!

Next we run the webservice wizard and create a webservice ZTW_READ_Report and release it for the SOAP runtime.

Now let's get the WSDL URL by selecting the webservice in transaction WSADMIN and clicking the WSDL button. A browser window opens and we copy the URL.

Sidestep: Let's test the service in a PHP script run from the commandline (i.e. without webserver set up).

<?

$login = "USER";

$password = "PW123";

$proxyhost = '111.222.333.444;

$proxyport = 3128;

// WSDL URL PL5

$wsdlurl = "http://us4484.wdf.sap.corp:50084/sap/bc/srt/rfc/sap/ZTW_READ_REPORT?"

. "sap-client=201&sap-user=" . $login . "&sap-password=" . $password . "&sap-language=DE&wsdl=1.1";

$programm_name = $argv[1];

try {

$client = new SoapClient($wsdlurl,

array(

'proxy_host' => $proxyhost,

'proxy_port' => $proxyport,

'login' => $login,

'password' => $password,

'trace' => true,

'exceptions' => true));

}

catch(SoapFault $e) {

echo 'Caught a Constructor Error: - ' . $e->faultstring;

}

if (isset($client)) {

try {

$ra = $client->ZtwReadReport(array(

'ProgrammName' => $programm_name

));

}

catch (SoapFault $e) {

echo 'Caught an Error: - ' . $e->faultstring;

}

if ($ra->Errorstring == "") {

$node = $ra->LinesOfReport;

foreach ($node as $val1) { //item

foreach ($val1 as $val2) { //Line

foreach ($val2 as $val3) { //Value

echo $val3 . "

";

}

}

}

}

else { echo $ra->Errorstring; }

}

else { echo 'No client object!'; }

?>

we can run this program from the (win) commandline with

php scriptname.php reportname

(make sure your php is on the path and that you're in the directory where your script is or use the full path name)

Notice the full WSDL URL http://servername:50084/sap/bc/srt/rfc/sap/ZTW_READ_REPORT?sap-client=201&sap-user=USER&sap-password=PW123&sap-language=DE&wsdl=1.1";

We'll need this URL in the next step.

We open a MS Word Document and go to Tools|Macro|VisualBasic Editor. Inside the Editor we go to menu Tools and find an entry 'web service references' (unfortunately I've a German Version of Word at hand at the moment so I've got to guess a little, what the English menu entries might be called). If you do not find such an entry, your SOAP Toolkit installation did not work.

Choosing this we get a dialog which, in the lower left corner allows to select 'webservice URL'. Enable this and enter the URL mentioned before. Click Search. If everything works, we get a description of our webservice in the right hand side box.

Select the webservice found (mark the checkbox) and click 'Add'. This creates some VBA proxy classes representing your webservice.

In the VisualBasic Editor they can be found on the left hand side box under Project|Class Modules. They are

- clsof_Factory_ZTWREADREPORT

- clsw_ZTWREADREPORTService

- struct_D022S

Let's have a look at clsw_ZTWREADREPORTService. In the comments at the start of the script we find an explanation about the originationg WSDL and something on the usage of this proxy.

'Example:

' Dim ExampleVar as New clsws_ZTWREADREPORTService

' debug.print ExampleVar.wsm_ZtwReadReport("Example Input")

Okay, all that's left to do, is to write a little Macro utilizing this proxy. Here we go:

Insert a new module to your VBA project and create a subroutine inside.

Sub read()

Dim errorstring As String

Dim lor As Variant

Dim ExampleVar As New clsws_ZTWREADREPORTService

Dim report_name As String

report_name = InputBox("Report:")

errorstring = ExampleVar.wsm_ZtwReadReport(report_name, lor)

Selection.TypeText Text:="Report" & report_name

Selection.TypeParagraph

For i = LBound(lor) To UBound(lor)

With Selection

.Font.Name = "Arial monospaced for SAP"

.Font.Size = 10

.TypeText Text:=lor(i).Line & vbCrLf

End With

Next

End Sub

When executed (Tools|Macro|Macros->read), this macro pops up an input box, allowing you to enter a programme name, then downloads the coding of that report, formats it a little (10 pt Arial monospaced for SAP) and pastes it into our word document.

Voilá, done.

What have we learnt so far:

- installing the SOAP toolkit into Office2003

- testing a webservice using commandline PHP

- creating webservice proxy functions in VBA

- allow dynamic user interaction with the document (input box) and therefore subsequently with the webservice

- calling a webservice from within an Office document (i.e. passing data to the ABAP Service & retrieving data from it)

Basically, that is all you need to start. It works the same in every Office application. The better you know VBA now (especially the trilions of classes within the various applications) the fancier applications you can build. I'll try to chip in some ideas thereof in the next days.

anton

Message was edited by: Anton Wenzelhuemer

Message was edited by: Anton Wenzelhuemer

[Former Member]

Example Number 2:

Say we want to calculate the total of two integer numbers in MS Excel. We don't know Excel too well and therefore are not aware that Excel is able to do this.

So we enter the numbers to be summed up in a spreadsheet and call a Netweaver webservice to let the ABAP WAS do the summing and finally enter the results into the Excel spreadsheet.

Note: This example might well solve the original problem in this thread.

Let's start with the webservice in ABAP:

FUNCTION ZTW_ADD.
*"----------------------------------------------------------------------
*"*"Lokale Schnittstelle:
*"  IMPORTING
*"     VALUE(TABLE_OF_SUMMANDS) TYPE  ZTW_TABLE_OF_SUMMANDS
*"  EXPORTING
*"     VALUE(TABLE_OF_SUMS) TYPE  ZTW_TABLE_OF_SUMS
*"----------------------------------------------------------------------

data: sum      type i,
      summands type ztw_summands.

loop at table_of_summands into summands.
  sum = summands-suma + summands-sumb.
  append sum to table_of_sums.
endloop.

ENDFUNCTION.

As we already know, an RFC enabled FM. It takes a table with two columns (the summands) in, and returns a table with the results. The table types used are straight forward, i.e. the components of the linetype structures are integers each.

Let the webservice wizard run across it and find the WSDL URL as shown above. Add username and password to the URL.

Next we create the VBA Macro in MS Excel. We open the visual basic editor and find the wizard for proxy generation at the exact same location as in the MS Word case. Create the proxies and view the comments in the main class here clsws_ZTWADDService. To get a more detailed info on the signature of the call, scroll down to the main method of the class, in my case

Public Function wsm_ZtwAdd(ByVal ar_TableOfSummands As Variant) As Variant

Below that line again we find some comments, explaining structures used.

With that info we go and create a module and a function 'Sum' in it and code the webservice call:

Sub Add()

    Dim col As Integer
    Dim row As Integer
    
    Dim Serv      As New clsws_ZTWADDService
    Dim intable() As struct_ZtwSummands
    Dim outtable  As Variant
    
    ReDim intable(10)
    For i = 0 To 10
        Set intable(i) = New struct_ZtwSummands
    Next i
    'fill table
        For row = 1 To 10
                For col = 1 To 2
                    If Cells(row, col).Value = 0 Then _
                        Cells(row, col).Value = row & col
                Next col
            intable(row - 1).Suma = Cells(row, 1)
            intable(row - 1).Sumb = Cells(row, 2)
        Next row
    
    'call the service
        outtable = Serv.wsm_ZtwAdd(intable)
    
    'fill sum
        For row = 1 To 10
            Cells(row, 3).Value = outtable(row - 1)
        Next row

End Sub

As we can see, columns 1 and 2 of rows 1 to 10 are filled if not filled by the user. The webservice is called, ABAP does the calculations and the results are written into column 3.

Voilá. done.

What have we learnt:

- creating webservice proxies in Excel is completely the same as in Word.

- get some data from the spreadsheet and send it to ABAP

- receive some results from ABAP and parse it into the document

Simple, isn't it? What'll be next? Access? Outlook? Powerpoint? Project? We'll see these days.

anton

BTW: I know that you can sum two numbers in Excel

[Former Member]

Just in case someone still wonders what SOA is about... just this. If you're not satisfied with the summing capabilities of your spreadsheet application keep the application but 'otsource the summing' to a competitor. If there's is a third party which does it even better and cheaper (better and cheaper than an ABAP WAS? Nooo way! ) let them do the summing stuff.

That easy, that cool.

[Former Member]

Hi,

I'm also trying to use SAP seb services in Excel.

I'm well able to generate my proxy function (with the toolkit from Microsoft) in VBA.

My issue is with credentials, in excel i'm not able to fix my user and password when i am using the web service with an error message "No matching authorization schema enable on connector".

In VB I'm perfectly able to fixe credentials as the toolskit is automatical integrate de properties for user in and password in the the proxy class. excel does not do that. Remarque, In excel you have to enter the user and password in the URL. that not the case in VB, the tollokit ask you for a login.

Can someone help me about this issue?

thanks