Macro: Excel to SAP

[Former Member]

Hi everyone! Iu2019m new in SAP, I hope someone can give me a hand. First I would like to know if its possible to connect from Excel to SAP by running a macro in Excel??? And then get information from different transactions and bring it back to excel, is it possible?? if so can someone help me with some VBA code?? Thanks!

[clinton_jones2]

Some good advice and snippets provided by the other posters here, but a lot of the approaches do rely on using GUI Scripting to achieve the screen scraping that you indicated you want to do. GUI Scripting is often found to be disabled on productive systems especially in companies governed by SOX.

What is not clear, is what the reason is for you wanting to do this? It may well be that the data that you want to extract can be easily obtained using a standard SAP report or a BAPI or a BDC or equivalent from a 3rd party.

Additionally consider the supportability of this approach. From a technical perspective the use of a macro that calls a gui scripting session may be technically feasible but do you really want to torture your business users with this kludge?

[Former Member]

Hello & Good day to you.

Here is a youtube link to How [Excel SAP Loader|http://www.youtube.com/watch?v=FqKlf2Ud7MU] works

There are 3 steps involved:

1. Extracts data from a client specific Excel File (Sales Order in this case),

2. Transforms the product details (Gauge, Type, Degree, Color) into SAP's Item description and then retrieves the matched Item IDs along with the Stock-in-Hand status.

3. Loads the transformed data into relevant SAP Object with all required fields and then creates a Draft Document into SAP

This draft document can be accessed from within SAP. This allows the user to recheck if there is anything that needs to be changed and then add the loaded draft as final (Sales Order) document into SAP.

I hope these guys at [ExcelSAP.com|http://www.excelsap.com/] can come up with a customized solution address your specific need.

[Former Member]

Hello Camilolop!

The example ScriptMan provided to me in the following thread was really helpful:

At the end of that thread he shows an example on how you can call the remote enabled function module RFC_TABLE_READ and then fetch data from a specified table.

You basically just need to change the login criteria so that it matches the system you would like to connect to. Then decide table to fetch data from and what delimiter to use, in the following example you would fetch from table "LFBW" and set the delimiter (how to seperate the data that is fetched).

With fb
        .exports("QUERY_TABLE") = "LFBW"
        .exports("DELIMITER") = "|"

next critical point would be when you set what search criterias you'd like to have when fetching data from the table:

 tOptions.Rows.Add
    tOptions(1, "TEXT") = "BUKRS = '1000' "
    tOptions.Rows.Add
    tOptions(2, "TEXT") = "AND WT_EXDT GE '20110101' "
    tOptions.Rows.Add
    tOptions(3, "TEXT") = "AND WT_EXDT LE '20111231' "

And then define which fields you would like to be fetched:

tFields.Rows.Add
    tFields(1, "FIELDNAME") = "BUKRS"
    tFields.Rows.Add
    tFields(2, "FIELDNAME") = "LIFNR"
    tFields.Rows.Add
    tFields(3, "FIELDNAME") = "WT_SUBJCT"
    tFields.Rows.Add
    tFields(4, "FIELDNAME") = "WT_WTSTCD"
    tFields.Rows.Add
    tFields(5, "FIELDNAME") = "WT_WITHCD"
    tFields.Rows.Add
    tFields(6, "FIELDNAME") = "WT_EXNR"
    tFields.Rows.Add
    tFields(7, "FIELDNAME") = "WT_EXRT"
    tFields.Rows.Add
    tFields(8, "FIELDNAME") = "WT_EXDF"
    tFields.Rows.Add
    tFields(9, "FIELDNAME") = "WT_EXDT"

after you perform fb.call if the operation is sucessful tData will be the object that contains the fetched data. And from this point you can just manipulate the data however you'd like. As an easy example you could just:

MsgBox tData(i, "WA")

to get the picture:)

I would recommend you running RFC_READ_TABLE in SE37 in your SAP system first to understand how the RFC works!

Hope this helps!

Regards,

Ernst

[Former Member]

This message was moderated.

[Former Member]

Follow the instructions in this [Excel Macro to read Table|http://wiki.sdn.sap.com/wiki/display/Snippets/ReadAnySAPTablewithMicrosoftExcel] that will get you to the PDF document. You can ignore the ABAP part but use the VBA part to follow the SAP connection and other code details.

(You will need the SAP GUI installed as a prerequisite which copies the required RFC libraries). Hope this helps.

[thomas_brutigam2]

Hi camilolop

Sure it's possible to do that things you want ...

If youre familiar with VB(A) then you can get everything by recording a Script and modify it in VBA.

To record a Script you have to enable SAP-Scripting (Alt+F12) Options (Script-Recorder ...etc)

Further information read the Postings in this Forum which are handling about Excel / VBA

There are many threads about it ...

for exampe this one:

or this

or this

The best way to learn this is Learning by Doing (and try and error) scnr

so if there are any Questions about special Scripting technics - go ahead an ask ...

greetings

Thomas B