vba Excel VBA代码转到特定的SAP列并找到特定的数字

Excel VBA code to go to specific SAP column and find specific number

提问by josh cunningham

This is my first forum post ever. I've been looking everywhere and cannot find the info for which I'm searching. If I do or say something incorrectly, please forgive me.

这是我第一次在论坛发帖。我到处找，但找不到我要搜索的信息。如果我做错了什么或说错了什么，请原谅我。

I have created an Excel spreadsheet that extracts live data from a query in SAP and pastes that info back into Excel Extraction Pic. From there, I'm opening transaction ME38 to update schedule lines based off the extracted data. I need to go to the "scheduled.." column (which I know to be "txtEKET-MENGE") and change the number shown to the "Qty Delivered" (which is a part of the extraction); however, I need to only do this on certain cells in the "Schedule..." column ("txtEKET-ETENR") [data listed on extraction as well]. SAP Table Pic

我创建了一个 Excel 电子表格，它从 SAP 中的查询中提取实时数据并将该信息粘贴回 Excel Extraction Pic。从那里，我打开事务 ME38 以根据提取的数据更新计划行。我需要转到“预定..”列（我知道它是“txtEKET-MENGE”）并将显示的数字更改为“已交付数量”（这是提取的一部分）；但是，我只需要在“计划...”列（“txtEKET-ETENR”）[提取时列出的数据]中的某些单元格上执行此操作。 SAP表图片

Here is what I've done so far.

这是我到目前为止所做的。

Set xclapp = CreateObject("Excel.Application")
Set xclwbk = ThisWorkbook
Set xclsht = xclwbk.Sheets("Sheet1")
For k = 2 To ActiveCell.SpecialCells(11).Row
For j = 1 To ActiveCell.SpecialCells(11).Column
If j = 14 Then Purch = xclsht.Cells(k, j).Value
If j = 15 Then Item = xclsht.Cells(k, j).Value
If j = 16 Then SLine = xclsht.Cells(k, j).Value
If j = 8 Then PGI = xclsht.Cells(k, j).Value
Next
myTransaction = "ME38"

Session.FindById("wnd[0]/tbar[0]/okcd").Text = "/n" & myTransaction

Session.FindById("wnd[0]").sendVKey 0

On Error Resume Next
Session.FindById("wnd[0]/usr/ctxtRM06E-EVRTN").Text = Purch
Session.FindById("wnd[0]/usr/ctxtRM06E-EVRTN").caretPosition = 10
Session.FindById("wnd[0]").sendVKey 0
Session.FindById("wnd[0]/usr/txtRM06E-EBELP").Text = Item
Session.FindById("wnd[0]/usr/txtRM06E-EBELP").caretPosition = 3
Session.FindById("wnd[0]").sendVKey 0
Session.FindById("wnd[0]/tbar[1]/btn[30]").press
Session.FindById("wnd[0]/tbar[1]/btn[2]").press
Call SelectRowOnGrid
Session.FindById("wnd[0]/mbar/menu[0]/menu[0]").Select
Session.FindById("wnd[0]").Close
  On Error Resume Next

  Session.FindById("wnd[1]/usr/btnSPOP-OPTION1").press
Next
End If
If Err.Number <> 0 Then

'The Excel worksheet has e.g. 3 columns of data and an error column.

xclsht.Cells(j, 21).Value = "Here is an error."

Else

xclsht.Cells(j, 21).Value = "O.K."

End If

On Error GoTo 0

The following is the Function SelectRowOnGrid.

下面是函数 SelectRowOnGrid。

Function SelectRowOnGrid()
Dim grid As SAPFEWSELib.GuiTableControl
Dim columnname As SAPFEWSELib.GuiTableColumn
Dim texttofind As String
Set grid = Session.FindById("wnd[0]/usr/tblSAPMM06ETC_1117/")
Set columnname = Session.FindById("wnd[0]/usr/tblSAPMM06ETC_1117/txtEKET-  ETENR")
texttofind = xclsht.Cells(k, 16).Value

    For k = 0 To grid.RowCount - 1

If grid.GetCellValue(k, columnname) = texttofind Then
grid.SetCurrentCell
grid.DoubleClickCurrentCell
End If

    Next k

End Function

When it gets to "Set grid..." it ends the function and does nothing else.

当它到达“设置网格...”时，它会结束该功能并且不执行任何其他操作。

Any insight would be greatly appreciated.

任何见解将不胜感激。

回答by ScriptMan

I will give you an example of how to deal with a table in SAP. In your case you use the commands for a GRID and this is not correct.

我给大家举个例子，说明如何在SAP中处理一张表。在您的情况下，您使用 GRID 的命令，这是不正确的。

for example:

例如：

myFile = "z:\tmp\test.xlsx"
mySheet = "Test"

Set xclApp = CreateObject("Excel.Application")
Set xclwbk = xclapp.Workbooks.Open(myFile)
set xclsht = xclwbk.Sheets(mySheet)

xclApp.Visible = True
xclapp.DisplayAlerts = false

k = 1

do

set myTable = session.findById("wnd[0]/usr/ssubITEMS:SAPLFSKB:0100/tblSAPLFSKBTABLE")

 if k = 1 then
 'rows = myTable.RowCount
  cols = myTable.Columns.Count
  vRows = myTable.VisibleRowCount
  for j = 0 to cols - 1
   xclsht.Cells(k,j + 1).Value = myTable.columns.elementAt(j).title
  next
  k = k + 1
 end if

for i = 0 to vRows - 1
l = 1
 for j = 0 to Cols - 1
  on error resume next
  myVariable = trim(myTable.GetCell(i,j).Text) 
  if err.number <> 0 then  exit for      
  on error goto 0
  if left(right(myVariable,3),1) = "," then 
   myVariable = replace(myVariable, "." , "")
   myVariable = replace(myVariable, "," , "")
   xclsht.Cells(k,l).Value = myVariable/100
  else 
   xclsht.Cells(k,l).Value = myVariable 
  end if
  l = l + 1
 next
 if err.number <> 0 then  exit for       
 k = k + 1
next
if err.number <> 0 then  exit do       
myTable.VerticalScrollbar.Position = myTable.VerticalScrollbar.Position + vRows

Loop 
xclapp.ActiveWorkbook.Save

Set xclwbk = Nothing
Set xclsheet = Nothing
set xclapp = Nothing

Regards, ScriptMan

问候， ScriptMan