title: How to pass parameters to SAP BAPI from EXCEL VBA
body: <p>There exist a lot of descriptions on parameters for BAPIs, but they are not understandable.
  I post the solution here, because I myself had many difficulties to achieve a parametrized BAPI call from EXCEL VBA.</p>
  <p>My task was to query SAP. It should be possible (in Excel via a Form) to select</p>
  <ul>
  <li>A date range</li>
  <li>Different states ( e.g. 10, 20, 60)</li>
  <li>Different department codes (e.g. 10, 20, 60)</li>
  <li>Whether to show Z800 projects or not</li>
  </ul>
  <p>To access the BAPI, you have to achieve the following:</p>
  <ol>
  <li>Establish a connection to your SAP instance (there are many examples on the net for this part, so I won’t explicate that, e.g. <a href="https://turbofuture.com/computers/Silent-Connection-with-SAPLOGON-with-RFCSDK-Example-RFC-Excel-VBA" rel="nofollow noreferrer">https://turbofuture.com/computers/Silent-Connection-with-SAPLOGON-with-RFCSDK-Example-RFC-Excel-VBA</a> ). In the example codes you will have something similar to</li>
  </ol>
  <pre><code>    If Not (connection Is Nothing) Then
                  debug.print &quot;connection is successful&quot;
  </code></pre>
  <ol start="2">
  <li>Instantiate the BAPI (there too are many examples on the net for this part, so I will skip that too, e.g. <a href="https://turbofuture.com/computers/Silent-Connection-with-SAPLOGON-with-RFCSDK-Example-RFC-Excel-VBA" rel="nofollow noreferrer">https://turbofuture.com/computers/Silent-Connection-with-SAPLOGON-with-RFCSDK-Example-RFC-Excel-VBA</a> )
  In the example codes you will have something similar to</li>
  </ol>
  <pre><code>    Set functions = sapFunctions(connection)
      Set obSapBapi = functions.Add(BapiName)
  
  </code></pre>
  <p>This assignment is successful, if you see the that the BAPI object contains the BAPI name in debug mode:
  <a href="https://i.stack.imgur.com/FBYo3.png" rel="nofollow noreferrer">Successful BAPI assignment</a>
  If the assignment is not successful, the BAPI object contains &quot;Nothing&quot;.</p>
  <ol start="3">
  <li>Call the BAPI with parameters
  Everywhere you can read “importing” / “exporting”…… and a lot more. But all I wanted to know was, how do I pass the user selection to SAP?
  The most common use case is, that a user selects data in an excel sheet and then starts a program, which fetches data from SAP based on the user selection, and this was mine, too.</li>
  </ol>
  <p>The BAPI can have 2 distinguished parameter types (every param refers to one explicit field in the SAP view):</p>
  <ol>
  <li>Only 1 value is passed: this is .exporting  (e.g. surname = &quot;Smith&quot;)</li>
  <li>More than 1 parameter is passed: this is via a table (e.g. surname = &quot;Smith&quot; or &quot;Myer&quot; or &quot;Pitt&quot;, order date &gt; 4/1/2019 and &lt; 4/15/2019)
  <a href="https://i.stack.imgur.com/FdxDP.png" rel="nofollow noreferrer">Input / Output of a BAPI</a></li>
  </ol>
  <p>Input and Output tables are of the same type. Your SAP department can name you which one is your data table.
  To get all the tables (and their fields) of a BAPI you can run this code (refers to input as well as output tables):</p>
  <pre><code>        If obSapBapi.Call &lt;&gt; False Then
              'Function Call is Successfull
              Dim oTables As Object 
              Set oTables = obSapBapi.Tables     'Tables collection
              Call GetColumnDetails(oTables)
              Set oTables = Nothing
               
              Debug.Print &quot;Function Call is Successfull&quot;
          End If
  </code></pre>
  <p>The Code of the sub:</p>
  <pre><code>    Private Sub GetColumnDetails(ByVal obTables As Object)
      on error resume next
      Dim iLoop As Integer, iColIndx As Integer, iColValuePos As Integer
      Dim iTblCnt As Integer, iColCnt As Integer
      dim iRowCnt As Integer,  iRowIndx As Integer
      Dim oTable As Object, oColumn As Object, iValuePosn As Integer
   
      iTblCnt = obTables.Count
      iValuePosn = 1
      For iLoop = 1 To iTblCnt
          Set oTable = obTables.Item(iLoop)
          iColCnt = oTable.ColumnCount
          iRowCnt = oTable.RowCount
          iColValuePos = 1
          Sheet3.Cells(iValuePosn, 1) =  oTable.Name
          iValuePosn = iValuePosn + 1
          For Each oColumn In oTable.Columns
              Sheet3.Cells(iValuePosn, iColValuePos) = oColumn.Name
              iColValuePos = iColValuePos + 1
          Next oColumn
         Set oTable = Nothing
      Next
      End Sub
  </code></pre>
  <p>Import / Input tables
  Import tables mostly look like:
  <a href="https://i.stack.imgur.com/W64or.png" rel="nofollow noreferrer">SAP import/input table</a></p>
  <p>The single lines are assembled as an SQL-WHERE Statement connected with OR. Description of the Input table:</p>
  <p><strong>Field Zeile (en: row)</strong><br />
  You have to give the row number (i++, starting by 1) !!!</p>
  <p><strong>Field SIGN</strong><br />
  (Only) possible values:<br />
  I: inclusive defined range<br />
  E: exclusive defined range</p>
  <p><strong>Possible values for Field OPTION (=operator):</strong></p>
  <ul>
  <li>EQ   Equal (= Low)</li>
  <li>NE   Not equal (&lt;&gt;  Low)</li>
  <li>BT   Between Low and High, including both (Low &lt;=  x &lt;= High)</li>
  <li>NB   Outside Low and High (x &lt; Low    and x&gt; High)</li>
  <li>CP   Contains pattern</li>
  <li>NP   Does not contain pattern</li>
  <li>LT   Lower than (&lt;Low)</li>
  <li>LE   Lower equal (&lt;= Low)</li>
  <li>GT   Greater than (&gt; low)</li>
  <li>GE   Greater equal (&gt;= low)</li>
  </ul>
  <p><strong>Possible values for Field LOW</strong><br />
  Depends on the data type of the referring field. Examples:</p>
  <ul>
  <li>[C(2)] =&gt; string</li>
  <li>[D(8)] is a date, you should transfer it as string with format “yyyyMMdd”</li>
  <li>[N(2)] =&gt; integer</li>
  </ul>
  <p><strong>Possible values for Field HIGH</strong></p>
  <ul>
  <li>Is empty except OPTION is BT or NB</li>
  </ul>
  <p><strong>Data types</strong></p>
  <pre><code>    SAP BAPI Data Type /  Description   /   Passed from VBA as  
      C (&lt;len&gt;) /           Character    /    string  
      D(8)      /           Date         /    Format$(mydate,”yyyyMMdd”)  
      F(&lt;len&gt;)  /           float        /    Double  
      I(&lt;len&gt;)  /           Integer      /    Integer  
      N(&lt;len&gt;)  /           Numeric      /    Integer / long  
      B         /           Boolean      /    String (length 1)  
  
  </code></pre>
  <p><strong>Example:</strong>
  I described my task above. It should be possible to select</p>
  <ul>
  <li>A date range (=&gt; in SAP a Table named IT_WORKD_RANGE, LOW and HIGH of type date)</li>
  <li>Different states (=&gt; in SAP a Table named IT_STATUS_RANGE, LOW and HIGH of type CHAR 2, e.g. 10, 20, 60)</li>
  <li>Different departments (=&gt; in SAP a Table named IT_ZZIDL_RANGE, LOW and HIGH of type NUMC 2, e.g. 10, 20, 60)</li>
  <li>Whether to show Z800 projects or no (=&gt; in SAP a BOOLEAN named IF_AWART, an exporting parameter, &quot;X&quot; or blank, where blank is default and means FALSE)</li>
  </ul>