1. Power Bi Export Table To Excel
2. Sap Tables

Dear SAP Community Member, In order to fully benefit from what the SAP Community has to offer. Export to PDF Export to. Downloading internal tables to Excel. Importing from SAP to Excel. Downloaded from SE16 is you want a table dump. In excel to get data from SAP you should use SAP to export the data to excel.

Does anyone know how to use VBA to pull data from SAP Netweaver?

I have a number of daily reports that require exporting data from SAP to Excel and formatting it into a report. I have already written working VBA macros that do the formatting. I have to manually extract the data and run each report macro individually. So much time could be saved if my macro could just go into SAP, grab the data for report #1, format it, grab the data for report #2, etc.

I work with SAP NetWeaver (Release 730, Version 7300.1.3.1079). The reports are just Excel pivot tables and charts.

Thanks!

closed as off-topic by Jean-François Corbett, Sam, gunr2171, user1803551, TylerHMay 21 '15 at 13:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

• 'Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it.' – Jean-François Corbett, Sam, gunr2171, user1803551, TylerH

If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

This all depends on what sort of access you have to your SAP system. An ABAP program that exports the data and/or an RFC that your macro can call to directly get the data or have SAP create the file is probably best.

However as a general rule people looking for this sort of answer are looking for an immediate solution that does not require their IT department to spend months customizing their SAP system.

In that case you probably want to use SAP GUI Scripting. SAP GUI scripting allows you to automate the Windows SAP GUI in much the same way as you automate Excel. In fact you can call the SAP GUI directly from an Excel macro. Read up more on it here. The SAP GUI has a macro recording tool much like Excel does. It records macros in VBScript which is nearly identical to Excel VBA and can usually be copied and pasted into an Excel macro directly.

Here is a simple example based on a SAP system I have access to.

To help find the names of elements such aswnd[1]/tbar[0]/btn[0] you can use script recording.Click the customize local layout button, it probably looks a bit like this:
Then find the Script Recording and Playback menu item.
Within that the More button allows you to see/change the file that the VB Script is recorded to. The output format is a bit messy, it records things like selecting text, clicking inside a text field, etc.

The provided script should work if copied directly into a VBA macro. It uses late binding, the line Set SapGuiAuto = GetObject('SAPGUI') defines the SapGuiAuto object.

If however you want to use early binding so that your VBA editor might show the properties and methods of the objects you are using, you need to add a reference to sapfewse.ocx in the SAP GUI installation folder.

Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vbasapnetweaver or ask your own question.

Skip to end of metadataGo to start of metadata

This utility will help us to maintain SAP tables from an excel spreadsheet without having to go into the SAP Table Maintenance transaction codes. It uses VBA as the underlying technology and two custom RFC enabled function modules in SAP to accomplish the task. This tool can also be used a Data browser (SE16) and can be used to copy data between 2 tables across landscape provided the structures are same.

Tool Interface

The excel sheet has 2 pages, one where will enter the Table name for maintenance and the second sheet where the data will be displayed

Technical Details:

Create the EXCEL Sheet:

Create the push buttons
Check if you have the ‘Developer’ tab in your excel sheet. If not, go to excel options and select the check box as shown below.

Now go to Developer tab and insert 2 buttons.

Assign macro READ_TABLE to download button and UPDATE_TABLE to upload button.

Click on Create. Here I have already created the Macros. We will need these two Macros to pull and update data to and from SAP.

They are associated with the push buttons.

Macro ‘READ_TABLE’ reads the data from SAP and pastes into the corresponding excel cells and ‘UPDATE_TABLE’ updates the data back to SAP.

Paste this code in Macro READ_TABLE.

Public Sub read_table()

Dim functionCtrl As Object ‘Function Control (Collective object)
Dim sapConnection As Object ‘Connection object
Dim theFunc As Object ‘Function object
On Error GoTo err_handler ‘ Error Handler
Set functionCtrl = CreateObject(”SAP.Functions”)
Set sapConnection = functionCtrl.Connection

‘Declaration
Dim returnFunc As Boolean ‘ SY-SUBRC
Dim table_name As String ‘ Table Name
Dim text As Variant ‘ Variable for holding the delimited string
Dim exception As String ‘ Exception from SAP

Power Bi Export Table To Excel

table_name = ActiveSheet.Cells(6, 5).Value
If table_name = “” Then
MsgBox “Please enter a table name”
Exit Sub ‘End program
End If

‘ Log into SAP
If sapConnection.logon(0, False) <> True Then
MsgBox “No connection to R/3!”
Exit Sub ‘End program
End If

‘ Call the Read RFC
Set theFunc = functionCtrl.Add(”YMAINT_TABLE”)

‘ Exporting table name
theFunc.exports(”TABLENAME”) = table_name

‘ Prepare Data sheet for data load
Worksheets(2).Select
Cells.Clear
returnFunc = theFunc.Call
exception = theFunc.exception

‘ Any exceptions ?
If exception = “INVALID_TABLE” Then
MsgBox “Table entered is not present in SAP”
Exit Sub
End If
If exception = “NO_AUTHORITY” Then
MsgBox “You have no authority to view table contents”
Exit Sub
End If

‘ Formatting excel output
Rows(”1:1″).Select
Selection.Font.Bold = True

‘ Map the data to cells
If returnFunc = True Then
Dim objtable As Object
Set objtable = theFunc.tables.Item(”DATA”)
For i = 1 To objtable.RowCount
text = Split(objtable.cell(i, 1), “~”)
For j = 1 To UBound(text)
ActiveSheet.Cells(i, j) = text(j)
Next j
Next i
End If

‘ Autofit the rows and columns
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Exit Sub
err_handler:
If Err.Description = “ActiveX component can’t create object” Then
MsgBox “No connection to SAP available”
Else
If Err.Description = “SAP Remote Function Call” Then
MsgBox “RFC Function module not available in called system”
Else
MsgBox Err.Description
End If
End If
Exit Sub

End Sub

Paste this code in UPDATE_TABLE.
Public Sub update_table()

Dim functionCtrl As Object ‘Function Control (Collective object)
Dim theFunc As Object ‘Function object
Dim table_name As String ‘ Table Name
Dim line As String ‘ String for concatenating
Dim exception As String ‘ SAP Exceptions
On Error GoTo err_handler ‘ Error Handler
Set functionCtrl = CreateObject(”SAP.Functions”)

table_name = ActiveSheet.Cells(6, 5).Value
If table_name = “” Then
MsgBox “Please enter a table name”
Exit Sub ‘End program
End If

‘ Update RFC
Set theFunc = functionCtrl.Add(”YCHANGE_TABLE”)

‘ Exporting table name
theFunc.exports(”TABLENAME”) = table_name

Worksheets(2).Select

Dim objtable As Object
Set objtable = theFunc.tables.Item(”DATA”)
‘ Map data from cells to ~ delimited string

For i = 1 To ActiveSheet.UsedRange.Rows.Count
For j = 1 To ActiveSheet.UsedRange.Columns.Count
objtable.Rows.Add
line = line & ActiveSheet.Cells(1 + i, j) & “~”
Next
objtable.Value(i, 1) = line
line = “”
Next

‘ Call the Update RFC
returnFunc = theFunc.Call
exception = theFunc.exception

‘ Any exceptions ?
If exception = “NO_AUTHORITY” Then
MsgBox “You have no authority to change table contents”
Exit Sub
End If
If exception = “INVALID_TABLE” Then
MsgBox “Table entered is not present in SAP”
Exit Sub
End If
If exception = “MAINTENANCE_NOT_ALLOWED” Then
MsgBox “Table has maintenance not allowed flag set”
Exit Sub
End If
If exception = “NO_DATA_TO_UPDATE” Then
MsgBox “No data to update”
Exit Sub
End If

‘Updated ?
If returnFunc = True Then
MsgBox “Records Updated successfully”
Else
MsgBox “Records could not be Updated”
End If
Exit Sub
err_handler:
If Err.Description = “ActiveX component can’t create object” Then
MsgBox “No connection to SAP available”
Else
If Err.Description = “SAP Remote Function Call” Then
MsgBox “RFC Function module not available in called system”
Else
MsgBox Err.Description
End If
End If
Exit Sub

End Sub

RFC Function modules in SAP

We will need two RFC function modules in SAP for sending and updating data.

RFC ‘YMAINT_TABLE’ will read the data from the SAP Tables and convert them into ‘~’ delimited strings which will be fed into the EXCEL macro ‘READ_TABLE’.

Macro ‘UPDATE_TABLE’ will take the updated excel sheet and convert that to ‘~’ delimited strings which will be transferred to SAP RFC ‘YCHANGE_TABLE’.

The FM Y_SPLIT_DATA is for splitting the ‘~’ delimited strings into their respective table header formats.

Do not change the RFC function module names and if you have to, you will have to make the same changes in the Macros.

The structure is YTAB30K is a structure of length 30000 characters.

Code

FUNCTION ymaint_table.
*”----------------------------------------------------------------------
*”*”Local Interface:
*” IMPORTING
*” VALUE(TABLENAME) TYPE TABNAME
*” EXPORTING
*” VALUE(RETURN) TYPE BAPIRET2
*” TABLES
*” DATA STRUCTURE YTAB30K
*” EXCEPTIONS
*” INVALID_TABLE
*” NO_AUTHORITY
*”----------------------------------------------------------------------
* Declarations
FIELD-SYMBOLS: <data> TYPE ANY TABLE ,
<word> TYPE ANY,
<wa> TYPE ANY.

DATA : line TYPE ytab30k,
lv_line TYPE i,
lv_str TYPE string,
tab_name TYPE tabname,
lv_tabname TYPE tabname,
lt_fields TYPE STANDARD TABLE OF dfies,
ls_fields LIKE LINE OF lt_fields,
datareft TYPE REF TO data,
datarefs TYPE REF TO data,
struc_type TYPE REF TO cl_abap_structdescr,
itab_type TYPE REF TO cl_abap_tabledescr,
comp_tab TYPE cl_abap_structdescr=>component_table.

CONSTANTS: gc_tld VALUE ‘~’.

tab_name = tablename.
TRANSLATE tab_name TO UPPER CASE.
* Check for Authorization
CALL FUNCTION ‘VIEW_AUTHORITY_CHECK’
EXPORTING
view_action = ‘S’
view_name = tab_name
EXCEPTIONS
no_authority = 2
no_clientindependent_authority = 2
no_linedependent_authority = 2
OTHERS = 1.
IF sy-subrc = 2.
RAISE not_authorized.
ELSEIF sy-subrc = 1.
RAISE invalid_table.
ENDIF.

* Create dynamic internal table
struc_type ?= cl_abap_typedescr=>describe_by_name( tab_name ).
comp_tab = struc_type->get_components( ).
struc_type = cl_abap_structdescr=>create( comp_tab ).
itab_type = cl_abap_tabledescr=>create( struc_type ).
CREATE DATA datareft TYPE HANDLE itab_type.
ASSIGN datareft->* TO <data> .
CREATE DATA datareft TYPE HANDLE struc_type.
ASSIGN datarefs->* TO <wa> .

* Get Field information
CALL FUNCTION ‘DDIF_FIELDINFO_GET’
EXPORTING
tabname = tab_name
TABLES
dfies_tab = lt_fields
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
RAISE invalid_table.
ENDIF.
* Create a internal table of ~ delimited strings
LOOP AT lt_fields INTO ls_fields.
CONCATENATE line ls_fields-fieldtext INTO line SEPARATED BY gc_tld.
ENDLOOP.
APPEND line TO data.
CLEAR line.
* Select data from table
SELECT * FROM (tab_name) INTO TABLE <data>.
LOOP AT <data> ASSIGNING <wa>.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <wa> TO <word>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
lv_str = <word>.
CONCATENATE line lv_str INTO line SEPARATED BY gc_tld.
CLEAR lv_str.
ENDDO.
APPEND line TO data.
CLEAR line.
ENDLOOP.
ENDFUNCTION.

Sap Tables

FUNCTION ychange_table.
*”----------------------------------------------------------------------
*”*”Local Interface:
*” IMPORTING
*” VALUE(TABLENAME) TYPE TABNAME
*” EXPORTING
*” VALUE(RETURN) TYPE BAPIRET2
*” TABLES
*” DATA STRUCTURE YTAB30K
*” EXCEPTIONS
*” INVALID_TABLE
*” TABLE_NOT_UPDATED
*” NOT_AUTHORIZED
*” MAINTENANCE_NOT_ALLOWED
*” NO_DATA_TO_UPDATE
*”----------------------------------------------------------------------

* Declarations
FIELD-SYMBOLS: <data> TYPE STANDARD TABLE ,
<word> TYPE ANY,
<wa> TYPE ANY.

DATA : line TYPE string,
lv_valid,
lv_line TYPE i,
tab_name TYPE tabname,
lv_tabname TYPE tabname,
lt_fields TYPE STANDARD TABLE OF dfies,
ls_fields LIKE LINE OF lt_fields,
datareft TYPE REF TO data,
datarefs TYPE REF TO data,
struc_type TYPE REF TO cl_abap_structdescr,
itab_type TYPE REF TO cl_abap_tabledescr,
comp_tab TYPE cl_abap_structdescr=>component_table.

CONSTANTS: gc_tld VALUE ‘~’,
gc_x VALUE ‘X’.

tab_name = tablename.
TRANSLATE tab_name TO UPPER CASE.
* Check for Authorization
CALL FUNCTION ‘VIEW_AUTHORITY_CHECK’
EXPORTING
view_action = ‘S’
view_name = tab_name
EXCEPTIONS
no_authority = 2
no_clientindependent_authority = 2
no_linedependent_authority = 2
OTHERS = 1.
IF sy-subrc = 2.
RAISE not_authorized.
ELSEIF sy-subrc = 1.
RAISE invalid_table.
ENDIF.
SELECT SINGLE tabname FROM dd02l INTO lv_tabname WHERE tabname = tab_name AND mainflag NE ‘N’.
IF sy-subrc NE 0.
RAISE maintenance_not_allowed.
ENDIF.
* Create dynamic internal table
struc_type ?= cl_abap_typedescr=>describe_by_name( tab_name ).
comp_tab = struc_type->get_components( ).
struc_type = cl_abap_structdescr=>create( comp_tab ).
itab_type = cl_abap_tabledescr=>create( struc_type ).
CREATE DATA datareft TYPE HANDLE itab_type.
ASSIGN datareft->* TO <data> .
CREATE DATA datareft TYPE HANDLE struc_type.
ASSIGN datarefs->* TO <wa> .

* Split delimted data to internal table
LOOP AT data INTO line.
IF line CO ‘~’.
CONTINUE.
ENDIF.
CALL FUNCTION ‘Y_SPLIT_DATA’
EXPORTING
iv_data = line
iv_separator = gc_tld
CHANGING
ct_tab_data = <data>.
ENDLOOP.
LOOP AT <data> ASSIGNING <wa>.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <wa> TO <word>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
IF <word> IS NOT INITIAL.
lv_valid = gc_x.
EXIT.
ENDIF.
ENDDO.
IF lv_valid NE gc_x.
DELETE <data> INDEX sy-tabix.
ENDIF.
CLEAR lv_valid.
ENDLOOP.
DESCRIBE TABLE <data> LINES lv_line.
IF lv_line EQ 0.
RAISE no_data_to_update.
ENDIF.
* Modify database from internal table
MODIFY (tab_name) FROM TABLE <data>.
IF sy-subrc NE 0.
RAISE table_not_updated.
ENDIF.

ENDFUNCTION.

FUNCTION Y_SPLIT_DATA .
*”--------------------------------------------------------------------
*”*”Local Interface:
*” IMPORTING
*” REFERENCE(IV_DATA) TYPE STRING
*” REFERENCE(IV_SEPARATOR) TYPE CHAR1
*” CHANGING
*” REFERENCE(CT_TAB_DATA) TYPE TABLE OPTIONAL
*”--------------------------------------------------------------------
DATA:
number_of_fields TYPE i,
found_non_initial_field TYPE c.
* Split record at delimiter and append to data table
DATA: lt_values TYPE STANDARD TABLE OF text1024, “Table of values
ls_value LIKE LINE OF lt_values. “Str for values
* CREATE DATA variable
DATA: lvo_ref TYPE REF TO data.
* Field symbols to access the structure and values
FIELD-SYMBOLS: <fs> TYPE ANY,
<fv> TYPE ANY.

* Create structure like line of INPUT TABLE
CREATE DATA lvo_ref LIKE LINE OF ct_tab_data .
ASSIGN lvo_ref->* TO <fs>.

* Split at the separator
SPLIT iv_data AT iv_separator INTO TABLE lt_values.
* Determine the number of fields that are present (separated by a
*delimiter)
number_of_fields = LINES( lt_values ).

* Skip this record if there are no fields that were separated by a
*delimiter.
CHECK number_of_fields GT 0.

CLEAR found_non_initial_field.
LOOP AT lt_values INTO ls_value.
ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs> TO <fv>.
<fv> = ls_value.
* If any field has a value, then prepare to keep the entire record
IF <fs> IS NOT INITIAL.
found_non_initial_field = ‘X’.
ENDIF.
ENDLOOP.
IF ct_tab_data IS SUPPLIED.
* If any field had a value, then keep the entire record
IF found_non_initial_field EQ ‘X’.
APPEND <fs> TO ct_tab_data.
ENDIF.
ENDIF.

ENDFUNCTION.

Demo:

Jan 3, 2018 - Sai baba songs sai baba bhajan sai baba aarti life and style lifestyle store lifestyle online my lifestyle sai baba songs download lifestyle. Jan 16, 2013 - Home » Devotinoal songs » SHIRDI SAIBABA free songs download. Saibaba Aarti - Om Sri Sainathayanamah Saibaba Aarti - Premeya. Songs free download mp3. Shirdi Sai Baba Aratis. Morning Afternoon Evening Night. Morning Afternoon Evening Night. Listen to Audio. Arati Saibaba.

Give the table name and press Download data to Excel.

Data is downloaded in the Data sheet.

Add a new record.

Go to first sheet and Press Upload to SAP.

Record inserted in SAP.

Notes

· Macros should be enabled in the excel sheet.

· Do not use incase table has more than 1000 entries.

· Modifying the primary key will result in adding a new row with the changed values.

· RFC function modules should be installed in systems which you want to query.