基于Excel列值设置SAP Excel脚本VBA代码字段值求助

Got it, let's tackle this issue where you need to swap that hardcoded 4 in your SAP VBA script with dynamic data from Excel. Here's a step-by-step solution that’ll make this work smoothly:

• First, grab the dynamic value from Excel
  Start by defining a variable to store the value from your Excel sheet. Let’s say your target data is in Sheet1 cell A1—adjust the sheet name and cell reference to match your actual setup:

  ' Define variable (use Integer if the value is a whole number, String if it's alphanumeric)
  Dim dynamicValue As Integer
  dynamicValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
  

  Pro tip: Add a quick check to make sure the cell isn’t empty to avoid errors:

  If IsEmpty(dynamicValue) Then
      MsgBox "No value found in Excel cell! Please check and try again."
      Exit Sub
  End If
  

• Replace the hardcoded 4 with your variable
  Now, update your SAP findById line by replacing the static 4 with the variable. The key here is correctly concatenating the variable into the string path—watch the quotes carefully:
  Original code snippet (with hardcoded value):

  Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV50A:1202/tblSAPMV50AT...[4,0]").Text = "YourValue"
  

  Modified code with dynamic value:

  Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV50A:1202/tblSAPMV50AT...[" & dynamicValue & ",0]").Text = "YourValue"
  

  Notice how we use & to stitch the variable into the string, keeping the surrounding quotes intact.

• Additional checks for reliability

  • Ensure the data type of dynamicValue matches what SAP expects. If the parameter is a string (like a column ID with letters), switch the variable type to String.
  • Test with different values from Excel to confirm the SAP control responds correctly—sometimes SAP uses 1-based indexing, so make sure your Excel data aligns with that.

Here’s a full working example to put it all together:

Sub UpdateSAPWithDynamicValue()
    Dim SapGuiAuto As Object
    Dim Application As Object
    Dim Connection As Object
    Dim Session As Object
    Dim dynamicValue As Integer
    
    ' Get dynamic value from Excel (adjust sheet/cell as needed)
    dynamicValue = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    
    ' Check for empty value
    If IsEmpty(dynamicValue) Then
        MsgBox "Excel cell is empty! Please enter a valid value."
        Exit Sub
    End If
    
    ' Connect to SAP (assuming SAP is already running)
    Set SapGuiAuto = GetObject("SAPGUI")
    Set Application = SapGuiAuto.GetScriptingEngine
    Set Connection = Application.Children(0)
    Set Session = Connection.Children(0)
    
    ' Update SAP field with dynamic value
    Session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV50A:1202/tblSAPMV50ATCTRL_U_ERF_AUFTRAG/ctxtVBAK-KUNNR[" & dynamicValue & ",0]").Text = "12345"
End Sub

内容的提问来源于stack exchange，提问作者Skavis