how to get data from a me1m transaction to excel through scripting

[Former Member]

hi, all the below is a  me1m transaction. i would like to get the vendor names for a part number from sap, as you can see i some vendor names displayed for a part number  I entered, i would like to have a sap scripting that would open me1m transaction and get part number info from for it and copy vendor names for the part number and paste it to the excel sheet, i can open me1m and enter part number to it, but i cant get the data from me1m, so anyone knowledge plse help

the below mentioned is script file.

'vb code

Dim oSapGuiAuto As Object

Dim oGuiApplication As Object

Dim oConnection As Object

Dim oSession As Object

Dim indexOD As Long

Dim lngCountOD As Long

'---------------------------------------------------------------

' Activate Scripting

Set oSapGuiAuto = GetObject("SAPGUI")

Set oGuiApplication = oSapGuiAuto.GetScriptingEngine

If oConnection Is Nothing Then Set oConnection = oGuiApplication.Children(0)

If oSession Is Nothing Then Set oSession = oConnection.Children(0)

If oSession Is Nothing Then Exit Sub

' Exit if you don't sure

If MsgBox("Now connecting [ " & oSession.PassportSystemId & " ], Continue Processing?", _

            vbOKCancel) = vbCancel Then Exit Sub

''

    '//********** Put recorded code here *******************///'

    With oSession

     .findById("wnd[0]").maximize

        .findById("wnd[0]/tbar[0]/okcd").Text = "me1m"

.findById("wnd[0]").sendVKey 0

'part number is taken from excel sheet

.findById("wnd[0]/usr/ctxtIF_MATNR-LOW").Text = "partnumber"

.findById("wnd[0]/usr/ctxtI_EKORG-LOW").Text = "282b"

.findById("wnd[0]/usr/ctxtI_EKORG-LOW").SetFocus

.findById("wnd[0]/usr/ctxtI_EKORG-LOW").caretPosition = 4

.findById("wnd[0]/tbar[1]/btn[8]").press

.findById("wnd[0]").resizeWorkingPane 265,39,false

.findById("wnd[0]/usr/ctxtIF_LIFNR-LOW").text = ""

.findById("wnd[0]/usr/ctxtIF_LIFNR-LOW").setFocus

.findById("wnd[0]/usr/ctxtIF_LIFNR-LOW").caretPosition = 0

findById("wnd[0]/tbar[1]/btn[8]").press

.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").currentCellColumn = "LIFNR"

.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").doubleClickCurrentCell

End With

    '//********** Put recorded code here *******************///'

    Next indexOD

End With

    MsgBox "All DONE!"

' Cleanup

    Set oSession = Nothing

    Set oConnection = Nothing

    Set oGuiApplication = Nothing

    Set oSapGuiAuto = Nothing

    Sheets("input").Activate

End Sub

the issues i am facing ctrl + y and ctrl +c function to get data from sap to excel cannot be scripted. i want some soluction to locate vendor column and get data from it.

[thomas_brutigam2 · Accepted solution]

Hi Deepan

You can find the Column specified Column with this little peace of Code:

Set grid = sapsession.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
        grid.SelectAll
        Set colValues = grid.SelectedColumns()
        IntColCount = colValues.Count
        For i = 0 To IntColCount - 1
            If grid.GetDisplayedColumnTitle(colValues(i)) = "Vendor" Then
                Exit For
            End If
        Next
        v_text = grid.GetCellValue(1, colValues(i))

This returns the first Value in the Vendor Column

have fun

Bg Thomas B

[Former Member · Accepted solution]

hi

Thomas thanks for your reply,

i like to do the code in vb,

so can you please specify like what are the variables you have used and how to declare them,

for example from your code ,

do i have to do like,

Dim grid,colvalues,v_text as object, or can u specify all the variables you have used and how to specify it asap,

Thank you in advance,

Anticipating your reply.,

[thomas_brutigam2 · Accepted solution]

Ok, here a littlte Excursion how to specify a variable:

In your "Example"

Dim grid,colvalues,v_text as object,    

only grid is declared as Object - everything else becomes a Variant-

So you have to do like this:

Dim grid as Object
Dim colvalues 'can be blank!
Dim v_text as String

PS: You can switch of the Variable-Declaration:

Option Explicit Off

This should work ...

[Former Member · Accepted solution]

Hi,

thomas  I faced a runtime error issue with the code you have given,

  v_text = grid.GetCellValue(1, colValues(i)) 

it is showing error in the above  code only.

so please help me with this.  i like to assign v_text value to a cell in excel

Range("C10").Value = v_text

like this

please help me on this,

Thank you for your replies.

[thomas_brutigam2 · Accepted solution]

Hi deepan,

what Error-Code is it ?

For this I need more Information-

[script_man · Accepted solution]

Hi Deepan and Thomas,

could it be that you can also make it easier?

for example:

1. Set grid = sapsession.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell") 
2. v_text = grid.GetCellValue(1, "Vendor") 
3. Range("C10").Value = v_text

But you can also look at another example:

Regards,

ScriptMan

[thomas_brutigam2 · Accepted solution]

Hi Script,

that would be nice if this would work - but afaik SAP awaits the Short-ID of the Column.

[script_man · Accepted solution]

Hi Thomas,

Of course, the column name in the command is not "Vendor". This must first to be found with the Script-Recorder.

Here, for example as:

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi,

Scriptman,

Set grid = .FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")

v_text = grid.GetCellValue(1, "LIFNR")

Range("C10").Value = v_text.

i used this code and i get a run time error .

[Former Member · Accepted solution]

hi,

thanks scriptman and thomas too, the code worked, issue is i have only one row with value and thomas have given reference to row 1,whereas sap row starts from row0,so i changed code

v_text = grid.GetCellValue(0 , "LIFNR") and it worked, thank you both the gentlemen for your help in time.