EXcel and SAP interface

M

MGHT

New Member
Joined
Feb 18, 2008
Messages
22
I often need to retrive info from SAP and fill in an excel document with it. Is there a book or a web page where I can find macros that will help me do that? Any advice on this?:confused:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi there, maybe I can (try to) help you, I've been in your position as well :)

First some questions:
- do you have some support on the SAP-side? Someone from IT to help you work things out?
- do you need to be able to read any table from SAP or just some predefined views?

If you need full access, better hope your IT-SAP guy is willing to open up SAP to your Excel queries. In my case here, they were not, for reasons of security (understandable).
Another possibility is having your SAP-guy implement some RFC's (Remote Function Calls) that you can then call from Excel to get results. That is what I got up and running here at the moment, and I have so say, these Excel-files are very popular :cool:
If you want to do that, I could provide you with some code on how to call an RFC from Excel. How to implement an RFC in SAP, that is out of my league, I cannot give any clues about that...
I can also provide you with a PDF-file that explains how to do the first, that is, access all SAP-tables via Excel. But this will only work when the SAP-server is allowing such access. Check with your SAP-guy, you will need his support anyway to debug...

ciao,
H
 
Upvote 0
Thanks!! I will surely do that and I appreciate all the help you can give me!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 
Upvote 0
Re: Excel and SAP interface

Good find eeek18... that is a file I've been playing with too in the beginning. Until I found that it wouldn't work for me because the IT-department blocked that kind of access to the SAP-server. That took me quite some time to find out though, because the error-messages led me to believe the problem lied in Excel.

Anyway, here is some code, adapted to hide our company secrets :)
I've adapted the code outside Excel, so it's possible there are typos,
the original code however works like a charm, take my word for it :biggrin:

Code: 
Option Explicit
Global MY_RFC_TABLE_01 As Object      'SAP table as defined in the RFC
Global MY_RFC_TABLE_02 As Object      'SAP table as defined in the RFC
'The RFC Function I use is defined as follows: 
'As parameters I give a material-code number for a bill-of-materials (structured) to SAP
'Then the RFC fills MY_RFC_TABLE_01 with the structured BOM, one line per material
'Or I give as parameter a list of material-code numbers to SAP
'Then the RFC fills MY_RFC_TABLE_02 with the material info, one line per material
Public Sub ConnectSap(SAP As Object)
    Dim ConnString As String
    Dim ConnectSap As String
    Set SAP = CreateObject("SAP.Functions")
    SAP.Connection.System = "ZZZ"     'SAP Server ID
    SAP.Connection.SystemNumber = 0     'SAP System Number
    SAP.Connection.Messageserver = "SAPCLUSTER"  'SAP Message Server Name (for loadbalancing SAP-clusters)
    SAP.Connection.GroupName = "SAPGROUP"   'SAP Group Name    (for loadbalancing SAP-clusters)
    SAP.Connection.client = "400"     'SAP Client ID
    SAP.Connection.user = "USERNAME"    'SAP Username
    SAP.Connection.Password = "PASSWORD"   'SAP Password
    SAP.Connection.language = "EN"     'SAP Language
    ConnString = SAP.Connection.logon(0, True)
    If ConnString <> True Then
        ConnectSap = "Connection failed!"
    Else
        ConnectSap = "Connection successful."
    End If
    If ConnectSap = "Connection failed!" Then
        Call MsgBox(ConnectSap, vbOKOnly, "SAP Connection")
        End
    End If
End Sub
Public Sub DisconnectSap(SAP As Object)
    SAP.Connection.LOGOFF
    'Call MsgBox("Connection closed.", vbOKOnly, "SAP Connection")
End Sub
Public Sub FillFlatList(ByRef pFlatList As Worksheet, Optional ByVal RowNumber As Long = 1, Optional ByVal ColumnNumber As Long = 1)
 'This Sub fills the SAP-table with the necessary parameter values before calling the RFC
    Dim TABLEROW As Long
    TABLEROW = 1
    While pFlatList.Cells(RowNumber, ColumnNumber).Value <> "" And pFlatList.Cells(RowNumber, ColumnNumber).Value <> "END1"
        MY_RFC_TABLE_02.Rows.Add  'adds a new row in the SAP-table
        MY_RFC_TABLE_02.Value(TABLEROW, "MATNR") = pFlatList.Cells(RowNumber, ColumnNumber).Value  'assigns value to the materialnumber parameter
        TABLEROW = TABLEROW + 1   'increments SAP-table rowcounter
        RowNumber = RowNumber + 1  'increments Excel-sheet rowcounter
    Wend
End Sub
Public Sub GetSapFlatList(ByRef TargetSheet As Worksheet, Optional ByVal StartRow As Long = 1, _
                    Optional ByVal StartColumn As Long = 1)
    Dim SAP As Object           'SAP Connection object
    Dim RFC As Object           'RFC Function object
    Dim ROW As Object           'ROW object in SAP-table
    Dim Result As Boolean
    'Open the connection to SAP
    Call ConnectSap(SAP)
    Set RFC = SAP.Add("MY_RFC_FUNCTION")
    Set MY_RFC_TABLE_02 = RFC.tables("MY_RFC_TABLE_02")
    'Call fill_selection_table for parameters for flat list
    Call FillFlatList(TargetSheet, StartRow, StartColumn)
    Result = RFC.Call  'after this line, SAP filled the MY_RFC_TABLE_02 table with the results
    If Result = False Then
        MsgBox RFC.EXCEPTION
        Call DisconnectSap(SAP)
        Exit Sub
    End If
    'Close the SAP connection
    Call DisconnectSap(SAP)
    'Extract filled SAP tables to Excel worksheet
    For Each ROW In MY_RFC_TABLE_02.Rows
        With TargetSheet
            .Cells(StartRow, StartColumn + 0).Value = ROW("MATNR")               'Material Number as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 1).Value = ROW("DATAFIELD1")          'Material Datafield 1 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 2).Value = ROW("DATAFIELD2")            'Material Datafield 2 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 3).Value = ROW("DATAFIELD3")         'Material Datafield 3 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 4).Value = ROW("DATAFIELD4")             'Material Datafield 4 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 5).Value = ROW("DATAFIELD5")        'Material Datafield 5 as defined in SAP-RFC
        End With
        StartRow = StartRow + 1
    Next
End Sub
Public Sub GetSapStructBom(ByVal Topcode As Variant, ByRef TargetSheet As Worksheet, _
                    Optional ByVal StartRow As Long = 1, Optional ByVal StartColumn As Long = 1)
    Dim SAP As Object           'SAP Connection object
    Dim RFC As Object           'RFC Function object
    Dim RFCPARAM As Object      'RFC Function parameter (Sapcode)
    Dim ROW As Object           'ROW object in SAP-table
    Dim Result As Boolean       'Result of the Remote Function Call
    'Open the SAP connection
    Call ConnectSap(SAP)
    Set RFC = SAP.Add("MY_RFC_FUNCTION")
    Set MY_RFC_TABLE_01 = RFC.tables("MY_RFC_TABLE_01")
    'Setting the input parameter for the RFC
    Set RFCPARAM = RFC.exports("MATNR")   
    RFCPARAM.Value = Topcode     'sets the parameter for SAP
    'Call the Remote Function
    Result = RFC.Call
    'Check for Call result
    If Result = False Then
        MsgBox RFC.EXCEPTION
        Call DisconnectSap(SAP)
        Exit Sub
    End If
    'Close the SAP connection
    Call DisconnectSap(SAP)
    'Fill sheet with SAP data
    For Each ROW In MY_RFC_TABLE_01.Rows
        With TargetSheet
            .Cells(StartRow, StartColumn + 0).Value = ROW("MATNR")               'Material Number as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 1).Value = ROW("DATAFIELD1")          'Material Datafield 1 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 2).Value = ROW("DATAFIELD2")            'Material Datafield 2 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 3).Value = ROW("DATAFIELD3")         'Material Datafield 3 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 4).Value = ROW("DATAFIELD4")             'Material Datafield 4 as defined in SAP-RFC
            .Cells(StartRow, StartColumn + 5).Value = ROW("DATAFIELD5")        'Material Datafield 5 as defined in SAP-RFC
        End With
        StartRow = StartRow + 1
    Next
End Sub
'Examples of function calls to retrieve SAP-info. These functions handle the connection to SAP
'themselves, via the provided functions ConnectSap and DisconnectSap. You can also do this yourself
'by calling them directly, but it is easier to let the first function handle it, that way you
'can transparantly call the function without worrying about the SAP-connection.
Public Sub Test()
 'uses ActiveSheet.Cells(2, 3).Value as parameter for the RFC and
 'returns the results to the Activesheet, starting on row 6, column 1
 Call GetSapStructBom(ActiveSheet.Cells(2, 3).Value, ActiveSheet, 6, 1)
 'uses the values in the Activesheet as parameters (more precisely, the values in
 'column 1, starting at row 6) and returns the results to the same location
 Call GetSapFlatList(ActiveSheet, 6, 1)
End Sub

It is best to put all the SAP-handling code in a separate module in your Excelfile, that way you can easily export/import it into other Excel applications.
Feel free to ask questions of course...

ciao,
H
 
Upvote 0
I personally use the cheap "Personal Data Mining" tool (http://www.pdm.lu/).
I export all I need from Sap to an SQL-Server database (or an Access database), using this PDM tool.
From then I have the choice for many different ways of working: MS-queries, pivottables, VBA functions using ADO, ... I use typically all of these methods.

I have called this "BW without BW".

In my company we are talking about using BW since a long time, but it didn't come to our desktop yet. Therefore I was very happy to be able to clone data tables to my prefered database.

Another possible solution would be based on the Oracle ODBC driver, but I found it would be more difficult. Most people in my compagny have the drivers for sqlserver and can use my excel files immediately without problems. It is also safer to link to a clone. And finally the PDM tool works immediately if your pc has an sap access.
 
Upvote 0
Wow!!! all of this is quit a bit of info!! thanks I do apreciate all the input you can giveme cus now i now there is more people that works with this stuff out there!!

I will try all of your advices for sure and I will let you know what happend.

Thanks!! and talk to you soon
 
Upvote 0
Don't know if it is even mentionable within this thread but couldn't you just use TCode "SQVI"? The only real requirement is knowing the table that the information you need is in...which is quite easy to find. No real reason why you should not have access to sqvi. If you are restricted and the SAP guy tells you you are not allowed to view it tell him you will start messing around with XK99
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
VBA code to grab a file path from an Excel cell
Replies
3
Views
410
fwk
F
N
  • Question
VBA to convert Salesforce Timesheet Data Dump to SAP Upload
Replies
0
Views
253
nickspray
N
M
  • Question
How to find more than one word in Excel?
3 4 5
Replies
49
Views
613
Cubist
Cubist
S
  • Question
Power Excel 2017 & sample file
Replies
0
Views
118
sammynam
S
C
  • Question
Extracting Specific Fields from multiple PDFs with VBA (Excel 2019)
Replies
1
Views
288
chickyguy
C

Forum statistics

Threads
1,215,310
Messages
6,124,184
Members
449,147
Latest member
sweetkt327

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top