Copy and paste data from SAP to Excel with VBA

[jay_jung]

Hi, All

I'm newer to VBA and SAP

I'm trying to make a macro with VBA, I need a help.

I want to copy a specific data from SAP in T-code ZVIRS and paste it to Excel, but I couldn't make it.

I want to copy a item data in ZVIRS code.

Here is my VBA code.

Is anyone who can help me?

Please and thanks in advance.

Sub SAP_OpenSessionFromLogon()

Dim SapGui

Dim Applic

Dim connection

Dim session

Dim WSHShell

Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus

Set WSHShell = CreateObject("WScript.Shell")

Do Until WSHShell.AppActivate("SAP Logon ")

Application.Wait Now + TimeValue("0:00:01")

Loop

Set WSHShell = Nothing

Set SapGui = GetObject("SAPGUI")

Set Applic = SapGui.GetScriptingEngine

Set connection = Applic.OpenConnection("SAP Production R3", True)

Set session = connection.Children(0)

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

session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "050"

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "UserID"

session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "Mypassword"

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

session.SendCommand ("/nZVIRS")

Dim materialNo As String

Dim plant As String

materialNo = ThisWorkbook.Sheets("Sheet1").Range("A1").Value

plant = "7022"

session.findById("wnd[0]/usr/ctxtP_MATNR").Text = materialNo

session.findById("wnd[0]/usr/ctxtP_WERKS").Text = plant

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

session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").SetFocus

session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").caretPosition = 3

"From here, I don't know how can I make a code for copy and paste from SAP to Excel"

sht = "Sheet1"

With sht

.Cells(2, 1).Value = session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").SetFocus

End With

Set sht = Nothing

MsgBox "Wating"

Set session = Nothing

connection.CloseSession ("ses[0]")

Set connection = Nothing

Set SAP = Nothing

End Sub

[matt]

Please use the CODE button in the editor when pasting code.

Sub SAP_OpenSessionFromLogon()
    Dim SapGui
    Dim Applic
    Dim connection
    Dim session
    Dim WSHShell
    Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus
    Set WSHShell = CreateObject("WScript.Shell")
    Do Until WSHShell.AppActivate("SAP Logon ")
        Application.Wait Now + TimeValue("0:00:01")
    Loop
    Set WSHShell = Nothing
    Set SapGui = GetObject("SAPGUI")
    Set Applic = SapGui.GetScriptingEngine
    Set connection = Applic.OpenConnection("SAP Production R3", True)
    Set session = connection.Children(0)
    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "050"
    session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "UserID"
    session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "Mypassword"
    session.findById("wnd[0]").sendVKey 0
    session.SendCommand ("/nZVIRS")
    Dim materialNo  As String
    Dim plant       As String
    materialNo = ThisWorkbook.Sheets("Sheet1").Range("A1").Value 
    plant = "7022"
    session.findById("wnd[0]/usr/ctxtP_MATNR").Text = materialNo
    session.findById("wnd[0]/usr/ctxtP_WERKS").Text = plant
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").SetFocus
    session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").caretPosition = 3
    "From here, I        't know how can I make a code for copy and paste from SAP to Excel"
    sht = "Sheet1"
    With sht
        .Cells(2, 1).Value = session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").SetFocus
    End With
    Set sht = Nothing
    MsgBox "Wating"
    Set session = Nothing
    connection.CloseSession ("ses[0]")
    Set connection = Nothing
    Set SAP = Nothing
End Sub

[Sandra_Rossi]

Thank you Matthew (I couldn't read the code posted by the OP due to not using the [CODE] button, hopefully next time the OP will format the code).

[Sandra_Rossi]

Did you try anything?

This element seems to be a GuiTableControl object.

You should use its methods to read whatever you want.

See documentation: GuiTableControl

There are answers in the Web concerning this object.

[jay_jung]

Hi, All

Thanks for your comments

I've seen many posts about VBA code by searching here, and I've tried almost everything in them.

But it didn't work.

Since I'm new to VBA, I don't even know what to try.

[jay_jung]

Hi, Siswanto.

I tried many way to get an data from SAP. I just found some codes through a search, and I created the current code while putting it together.

I don't even know what OLE2 means, and I have no knowledge of VBA and SAP.

The fact that I tried various methods is just pasting the code I saw through a search on the Q&A board, and pasting other codes if it didn't work.

Thanks

[Sandra_Rossi]

I don't understand why you are discussing about OLE2 although your question is about SAP GUI Scripting.

What is your client requirement?

As you don't know neither VBA nor SAP GUI Scripting, you need to read more about the forums and documentation.

What did you understand from GuiTableControl (see link posted above), and did you search in the Web for code to extract cell value?

Don't use "SetFocus", it doesn't return the value, it only sets focus on cell.

.Cells(2, 1).Value = session.findById("wnd[0]/usr/tblSAPMZVIS_TCNTRL_OUT/txtZVSIS-ZPOSNR[14,0]").Text

See examples in the answer at excel - Reading text in Table Control - Stack Overflow:

cellText = ...