cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Grid Copy to excel

former_member607385
Discoverer
0 Kudos

Hoping someone can help my insanity. I have been up and down forums looking all over but have yet to find an answer.

I am not really good with coding or writing scripts but here is what I am trying to accomplish.

I want to write a macro that calls CRMD_ORDER and pulls certain information to excel. I have been successful in pulling text but not table contents. which I need also. Below is what I have and where I am stuck. I have included pictures also so hopefully that helps.

Any help is appreciated!

Sub Extract_Internal_Notes()
MsgBox "Close all sessions and only have 1 CRM session open."


'Activate SAP
    Set SAPGUIAuto = GetObject("SAPGUI")  'Get the SAP GUI Scripting object
    Set SAPApp = SAPGUIAuto.GetScriptingEngine 'Get the currently running SAP GUI
    Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
    Set session = SAPCon.Children(0) 'Get the first session (window) on that connection


'Navigate to the CRMD_ORDER transaction screen
    session.StartTransaction "crmd_order"


    Range("A1").Select
    Do Until ActiveCell.Value = ""
        'Open and send order number
            session.findById("wnd[0]").sendVKey 17
            session.findById("wnd[1]/usr/ctxtGV_OBJECT_ID").Text = ActiveCell.Value
            session.findById("wnd[1]/tbar[0]/btn[0]").press
                                                                                  '----------------------------------------------------------------
                                                                                  '0110<--Change 0120 to 0110 if it is not working on this computer!
            session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0213/subSUBSCR01:SAPLCRM_SERVICE_UI:0426/subSUBSCR02:SAPLCRM_SERVICE_UI:7153/subSUBSCR03:SAPLCOM_TEXT_MAINTENANCE:2020/cmbCOMT_TEXT_SCREEN_DYN_2020-TDID").SetFocus
            session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0213/subSUBSCR01:SAPLCRM_SERVICE_UI:0426/subSUBSCR02:SAPLCRM_SERVICE_UI:7153/subSUBSCR03:SAPLCOM_TEXT_MAINTENANCE:2020/cmbCOMT_TEXT_SCREEN_DYN_2020-TDID").Key = "ZSOW"
            ActiveCell.Offset(0, 1).Value = session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0213/subSUBSCR01:SAPLCRM_SERVICE_UI:0426/subSUBSCR02:SAPLCRM_SERVICE_UI:7153/subSUBSCR03:SAPLCOM_TEXT_MAINTENANCE:2020/cntlTEXT_CONTROL_2020/shellcont/shell").Text
        ActiveCell.Offset(1, 0).Select
        
        session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/subAREA1:SAPLCRM_1O_GEN_UI:5010/cntlFCODE_TB_AREA/shellcont/shell").pressButton "SERVICE_POSITIONEN"
        session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0214/subSUBSCR01:SAPLCRM_SERVICE_UI:3201/tabsTABSTRIP_SRVO_CO/tabpT\SRVO_CO03").Select
        session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0214/subSUBSCR01:SAPLCRM_SERVICE_UI:3201/tabsTABSTRIP_SRVO_CO/tabpT\SRVO_CO03/ssubITEM_LIST:SAPLCRM_SERVICE_UI:7400/cntlSRV_MAT_ITEM_LIST/shellcont/shell").SelectAll
         
        
    Loop
    
    
End Sub

Accepted Solutions (1)

Accepted Solutions (1)

script_man
Active Contributor

Hi Michael,

First, I would record a helper script that reveals the internal column names.

Proceed as follows:

1. Start the script recorder

2. When you have arrived in the relevant table, mark the 1st column named Item and sort it in descending order.

3. Repeat this procedure with all the columns that you want to transfer to Excel later.

4. After the last column, stop the recording.

5. Open the recorded script

6. The lines with the commands (e.g. session.findbyid(....).selectColumn "ORDER_NR" ) contain the searched internal column names.

7. If you know all column names, you can put together your own script manually.

for example:

...
'The following command can be deactivated.
'session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0214/subSUBSCR01:SAPLCRM_SERVICE_UI:3201/tabsTABSTRIP_SRVO_CO/tabpT\SRVO_CO03/ssubITEM_LIST:SAPLCRM_SERVICE_UI:7400/cntlSRV_MAT_ITEM_LIST/shellcont/shell").SelectAll

set myGrid = session.findById("wnd[0]/usr/ssubSUBSCREEN_1O_MAIN:SAPLCRM_1O_MANAG_UI:0110/subSUBSCREEN_1O_WORKA:SAPLCRM_1O_WORKA_UI:2100/subSCR_1O_MAINTAIN:SAPLCRM_1O_UI:1100/subSCR_1O_MAINTAIN:SAPLCRM_SERVICE_UI:0101/ssubSCRAREA0:SAPLCRM_SERVICE_UI:0214/subSUBSCR01:SAPLCRM_SERVICE_UI:3201/tabsTABSTRIP_SRVO_CO/tabpT\SRVO_CO03/ssubITEM_LIST:SAPLCRM_SERVICE_UI:7400/cntlSRV_MAT_ITEM_LIST/shellcont/shell") 


j = 3  'e.g. 3. Row in Excel
k = 1  'e.g. 1. Column in Excel 
for i = 0 to myGrid.rowCount - 1
 'The column names are to be replaced!
 ActiveSheet.Cells(j + i , k).Value = myGrid.getcellvalue ( i , "ORDER_NR")      
 ActiveSheet.Cells(j + i , k + 1).Value = myGrid.getcellvalue ( i , "PRODUCT_XYZ") 
 ActiveSheet.Cells(j + i , k + 2).Value = myGrid.getcellvalue ( i , "XYZ_QUANTITY") 
 ...
next   

loop

End Sub

Regards,

ScriptMan

Answers (0)