EXCEL VBA Macro to Download SAP Table contents

Here is the Code to download any SAP table contents to excel without login in to SAP GUI.

Function Module RFC_READ_TABLE is used to get the table contents.

 Sub Read_Table()  
  Dim LogonControl As Object  
  Dim R3Connection As Object  
  Dim retcd    As Boolean  
  Dim SilentLogon As Boolean  
  Dim returnFunc As Boolean  
  Dim objTableContent As Object  
  Dim tOptions As Object  
  Dim tFields As Object  
  Dim tData As Object  
  Dim RowData$()  
  Dim j&, i&, k&  
 'Set Connection  
  Set LogonControl = CreateObject("SAP.LogonControl.1")  
  Set objBAPIControl = CreateObject("SAP.Functions")  
  Set R3Connection = LogonControl.NewConnection  
  Dim boBapiService As Object  
 'SAP connection  
  R3Connection.client = Sheet4.Cells(3, 2).Value  
  R3Connection.ApplicationServer = Sheet4.Cells(4, 2).Value  
  R3Connection.Language = Sheet4.Cells(5, 2).Value  
  R3Connection.User = ""  
  R3Connection.Password = ""  
  R3Connection.System = Sheet4.Cells(6, 2).Value  
  R3Connection.SystemNumber = Sheet4.Cells(7, 2).Value  
  R3Connection.UseSAPLogonIni = False  
  SilentLogon = False  
  Dim oMsgReturn As Object  
  Dim oMsgText As String  
  retcd = R3Connection.logon(0, SilentLogon)  
  If retcd <> True Then MsgBox "Logon failed": Exit Sub  
  objBAPIControl.Connection = R3Connection  
 'Call the Function Module by BAPI Service  
   Set objTableContent = objBAPIControl.Add("RFC_READ_TABLE")  
   With objTableContent  
     .exports("QUERY_TABLE") = Sheet4.Cells(15, 2).Value  
     .exports("DELIMITER") = Sheet4.Cells(16, 2).Value  
   End With  
   Set tOptions = objTableContent.Tables("OPTIONS")  
   Set tFields = objTableContent.Tables("FIELDS")  
   Set tData = objTableContent.Tables("DATA")  
   tOptions.Rows.Add  
   tOptions(1, "TEXT") = Sheet4.Cells(17, 2).Value  
 '  tOptions.Rows.Add  
 '  tOptions(2, "TEXT") = Sheet4.Cells(4, 2).Value  
 '  tOptions.Rows.Add  
 '  tOptions(3, "TEXT") = Sheet4.Cells(5, 2).Value  
 '  
 '  
 '  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' "  
 '  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"  
   tFields.Rows.Add  
   tFields(1, "FIELDNAME") = Sheet4.Cells(21, 1).Value  
   tFields.Rows.Add  
   tFields(2, "FIELDNAME") = Sheet4.Cells(21, 2).Value  
   tFields.Rows.Add  
   tFields(3, "FIELDNAME") = Sheet4.Cells(21, 3).Value  
   tFields.Rows.Add  
   tFields(4, "FIELDNAME") = Sheet4.Cells(21, 4).Value  
   tFields.Rows.Add  
   tFields(5, "FIELDNAME") = Sheet4.Cells(21, 5).Value  
   tFields.Rows.Add  
   tFields(6, "FIELDNAME") = Sheet4.Cells(21, 6).Value  
   tFields.Rows.Add  
   tFields(7, "FIELDNAME") = Sheet4.Cells(21, 7).Value  
   tFields.Rows.Add  
   tFields(8, "FIELDNAME") = Sheet4.Cells(21, 8).Value  
   tFields.Rows.Add  
   tFields(9, "FIELDNAME") = Sheet4.Cells(21, 9).Value  
 'if value is returned by BAPI call copy it to worksheet  
   returnFunc = objTableContent.Call  
   If returnFunc = True Then  
     j = tData.RowCount  
     MsgBox ("Row count :" & tData.RowCount)  
     If j Then  
       For i = 1 To j  
         RowData = Split(tData(i, "WA"), "|")  
         For k = 1 To 9  
           Sheet4.Cells(21 + i, k).Value = RowData(k - 1)  
         Next  
       Next  
     End If  
   Else  
     MsgBox "Error when accessing BAPI in R/3 ! "  
     Exit Sub  
   End If  
   objBAPIControl.Connection.logoff  
   Set R3Connection = Nothing  
   MsgBox "Done!", 0, "Exit"  
 End Sub  

OUTPUT

How to copy Queries From one InfoCube to Another InfoCube

Enter the T-Code RSZC

Enter the Source InfoCube and Target InfoCube

Select Queries

Then Click Execute


In the next Screen select Query name and Click Transfer Selections



Next screen Give the new name for the copied Query

Click Continue

Then the Pop up Screen shows the Query with Technical name Created Successfully.

Then Click Continue.

How to Restore the Modified Transformation to Active Version of a Transformation

I have made some changes in the Active Transformation and saved it with out activating the transformation.


Now i don't want the changes and i have to restore it to the active version.


Open the transformation in Edit mode

Go to Menu -> Edit -> Return to Active Version

Communication Structure & Transfer Structure in SAP BW

è Communication Structure

The communication structure is localized in the SAP BW and displays the structure of the InfoSource. 

 

è

èIt contains all of the InfoObjects belonging to the InfoSource of the BW system. 

 

Transfer Structure

 

èTransfer Structure is a selection of DataSource fields from a source system. 

 

è

èThe transfer structure provides the BW with all the source system      information available for a business process. 

 

è

èA transfer structure always refers to a DataSource in a source system and an InfoSource in a BW

 

Creating Infopackage in SAP BW

èStep1: Right click on Source system below the selected InfoSource and choose ‘Create Infopackage’. 

 

è

èStep2: Enter Infopackage Description. 

 

è

èStep3: Enter appropriate restrictions in ‘Data Selection’ tab. 

 

Step4: For Flat file data load, enter the path for flat file in ‘External Data’ tab.

 

Step5: Select appropriate processing type in ‘Processing’ tab.

 

Step6: Select Data targets to be loaded in ‘Data Target’ tab. 

 

Step7: Select update type in ‘Update’ tab. 

 

 

Step8: Start Infopackage load in ‘Schedule’ tab. 

 

Creating Update Rule in SAP BW

è 

Step1: Right click on Data Target and then select “Create update rule”. 

è

èStep2: Specify the Update type, update method and unit for key figure calculation. 

è

èStep3: Specify the Update method for the characteristics 

è

èStep4: Specify the Update method for time characteristics. 

è

èStep5: Check and activate the update rule 

For InfoCube we have two update type

l            Addition

l            No Update

There exists three update method for Key figures

l            Source key Fig

l            Formula

l            Routine 

–

Assigning DataSource to InfoSource in SAP BW

è 

Step1: Right click the InfoSource and select ‘Assign DataSource’. 

è

èStep2: Select the Source System of the DataSource. 

è

èStep3: Select the DataSource existing in this source system and confirm assignment. 

Creating InfoSource in SAP BW

èStep1: In InfoSource menu (RSA1 transaction), right click on an Application component and select ‘Create InfSource’. 

è

èStep2: Choose the InfoSource type (Flexible or Direct Update) and provide technical name and description to InfoSource. 

è

èStep3: Enter the InfoObjects to be included in Communication structure. 

è

èStep 4: Save and Activate the InfoSource. 

Creating Flat File Source System in SAP BI/BW

èStep1: In RSA1 transaction, select ‘Source System’. 

è

èStep2: Right click on Source System in right panel and select ‘Create’. 

è

èStep3: Select ‘File System’. 

è

èStep4: Provide a Logical System Name and Source System name. 

Creating InfoSet in SAP BI/BW

è 

Step1: Enter Transaction RSA1. Right Click on InfoArea and select ‘Create InfoSet’. Provide technical name and description for the InfoSet. 

è

èStep2: Choose ODS or InfoObject on which  you want to build InfoSet. 

è

èStep3: Select the characteristics/key figures required in InfoSet design and join the common characteristics via Inner or Left Outer Join. 

è

èStep 4: Save and Activate InfoSet. 

Creating MultiProvider in SAP BI/BW

è 

Step1: Enter Transaction RSA1. Right Click on Infoarea and select ‘Create MultiProvider’. Provide technical name and description for the MultiProvider. 

èStep2: Select Data Targets (InfoCube, ODS and InfoObject or Infosets) on which MultiProvider should be based. 

èStep3: Select the Characteristics which should be part of the MultiProvider design. 

èStep4: Select Time Characteristics and Key figures. 

èStep5: Create dimensions and assign the characteristics to the dimensions. 

èStep6: Identify the characteristics, time characteristics and key figures in ‘Identification’ tab. 

èStep7: Save and Activate the MultiProvider. 

Creating ODS in SAP BW

èStep1: Enter Transaction RSA1. Right click on the InfoArea and choose “Create ODS”. Provide Technical name and description for the ODS. 

è

èStep2: Choose infoobjects in Key field and Data fields of ODS. 

è

èStep3: Adjust ODS settings appropriately. 

è

èStep4: Save and Activate the ODS 

Creating InfoCube in SAP BI/BW

è 

Step1: Enter Transaction RSA1. Right Click on the InfoArea and choose “Create” InfoCube and provide technical name and description for the InfoCube. 

è

èStep2: Select the Characteristics which should be part of the InfoCube design. 

è

èStep3: Select Time Characteristics and Key Figures. 

è

èStep4: Create dimensions and assign the characteristics to the dimensions 

è

èStep5: Save and Activate the InfoCube.