cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Gathering MHTML reports

Go to solution
Former Member

on ‎02-07-2014 8:28 AM

0 Kudos

Dear All,

At the moment I am using SAP P12.

From T-code IW33 I am trying to gather data from MHTML.

I have tried to follow some scripts which looked fantastic, but just weren't able to apply to what I use.

The below is as far as I got.

Sub GetPlanCostData()
'---------------------------------
  '---------------------------------
' Parameters


'---------------------------------
   '-------- SAP LOGIN --------------
   '---------------------------------


    'If Right(CLPdirName, 1) <> "\" Then CLPdirName = CLPdirName & "\"

    On Error Resume Next

    If SAPApp Is Nothing Then
        Set SapGuiApp = GetObject("SAPGUI")
        Set SAPApp = SapGuiApp.GetScriptingEngine
       ' If SAPApp Is Nothing Then GoTo Exit_AllReport
    End If
    If Connection Is Nothing Then
        Set Connection = SAPApp.Children(0)
    End If
    If session Is Nothing Then
        Set session = Connection.Children(0)
    End If


    If Err = 0 Then flgSapIsOpen = True
    Err.Clear
   
    On Error GoTo 0

    If flgSapIsOpen = False Then
        Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
        Set Connection = SapGuiApp.OpenConnection(CLPsapName, True, False)
      '  If Connection Is Nothing Then GoTo Exit_AllReport
   '?     Set session = Connection.Children(0)

        If Not IsObject(Connection) Then
            Set Connection = SAPApp.Children(0)
        End If
        If Not IsObject(session) Then
            Set session = Connection.Children(0)
        End If

    End If

'If Not IsObject(Application) Then
'  Set SapGuiAuto = GetObject("SAPGUI")
  ' Set Application = SapGuiAuto.GetScriptingEngine
'End If
If Not IsObject(Connection) Then
   Set Connection = Application.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If

'Start Processing in SAP
i = 2

Do While Cells(i, 1) <> ""


SO = Cells(i, 1)


session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/niw33"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = SO
session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").caretPosition = 7
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1100/tabsTS_1100/tabpKOAU").Select
session.findById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1107/tabsTS_1100/tabpKOAU/ssubSUB_AUFTRAG:SAPLICO1:1100/btnPUSH1").press
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press

i = i + 1
Loop

End Sub

I am very new to this, and I wanted to make something similar to what I was given some time ago.

Different t-code but gathers information I need.

Sub GetPlanCostData()
'---------------------------------
  '---------------------------------
' Parameters
FilePath = "C:\cemacro\"
'Sheets.Add.Name = "RESULTS"


' Clearthe folder
On Error Resume Next
    Kill "C:\cemacro\*.xls"
   On Error GoTo 0
On Error Resume Next
Kill "C:\cemacro\*.dat"
On Error GoTo 0

'---------------------------------
   '-------- SAP LOGIN --------------
   '---------------------------------
               
     Para_check = True
 
    CLPsapName = ActiveSheet.Range("F29").Value
    CLPuserid = ActiveSheet.Range("F30").Value
    CLPpasswd = ActiveSheet.Range("F31").Value
    'CLPdirName = ActiveSheet.Range("C5").Value
    CLPrptName = "Worksheet in Basis (1)"

    'If Right(CLPdirName, 1) <> "\" Then CLPdirName = CLPdirName & "\"

    On Error Resume Next

    If SAPApp Is Nothing Then
        Set SapGuiApp = GetObject("SAPGUI")
        Set SAPApp = SapGuiApp.GetScriptingEngine
       ' If SAPApp Is Nothing Then GoTo Exit_AllReport
    End If
    If Connection Is Nothing Then
        Set Connection = SAPApp.Children(0)
    End If
    If session Is Nothing Then
        Set session = Connection.Children(0)
    End If


    If Err = 0 Then flgSapIsOpen = True
    Err.Clear
   
    On Error GoTo 0

    If flgSapIsOpen = False Then
        Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
        Set Connection = SapGuiApp.OpenConnection(CLPsapName, True, False)
      '  If Connection Is Nothing Then GoTo Exit_AllReport
   '?     Set session = Connection.Children(0)

        If Not IsObject(Connection) Then
            Set Connection = SAPApp.Children(0)
        End If
        If Not IsObject(session) Then
            Set session = Connection.Children(0)
        End If

     

    End If

'If Not IsObject(Application) Then
'  Set SapGuiAuto = GetObject("SAPGUI")
  ' Set Application = SapGuiAuto.GetScriptingEngine
'End If
If Not IsObject(Connection) Then
   Set Connection = Application.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If
'==============================================
'Clear out the status field in B-column
i = 2
Do While Cells(i, 1) <> ""
i = i + 1
Cells(i, 2) = ""
Loop

'==============================================

'Start Processing in SAP
i = 2

Do While Cells(i, 1) <> ""


WBS = Cells(i, 1)

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/ns_alr_87013542"
session.findById("wnd[0]").sendVKey 0

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

' Blank fields in screen
session.findById("wnd[0]/usr/ctxtCN_PROJN-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtCN_PROJN-HIGH").Text = ""
session.findById("wnd[0]/usr/ctxtCN_VBELN-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtCN_VBELN-HIGH").Text = ""
session.findById("wnd[0]/usr/ctxtCN_PSPNR-HIGH").Text = ""
session.findById("wnd[0]/usr/ctxtCN_NETNR-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtCN_NETNR-HIGH").Text = ""
session.findById("wnd[0]/usr/ctxtCN_ACTVT-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtCN_ACTVT-HIGH").Text = ""
session.findById("wnd[0]/usr/ctxtCN_MATNR-LOW").Text = ""
session.findById("wnd[0]/usr/ctxtCN_MATNR-HIGH").Text = ""
session.findById("wnd[0]/usr/ctxt$6-KOKRS").Text = "1000"
session.findById("wnd[0]/usr/ctxt$6-VERP").Text = "0"
session.findById("wnd[0]/usr/txt$6-GJAHV").Text = "2000"
session.findById("wnd[0]/usr/txt$6-GJAHB").Text = "2100"
session.findById("wnd[0]/usr/ctxt$6-PERBV").Text = "1"
session.findById("wnd[0]/usr/ctxt$6-PERBB").Text = "12"
session.findById("wnd[0]/usr/ctxt$6-KSTAR").Text = ""


session.findById("wnd[0]/usr/ctxtCN_PSPNR-LOW").Text = WBS
session.findById("wnd[0]/usr/ctxt_6-KSTAR-LOW").Text = "400000"
session.findById("wnd[0]/usr/ctxt_6-KSTAR-HIGH").Text = "920000"

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

If session.findById("wnd[0]/sbar").Text = "Report 6PP1-001 does not contain any data pages." Then
' This means no Actual Cost, no Commitment and no Plan Cost exist.
Cells(i, 2) = "No Actual Cost, no Commitment and no Plan Cost exist"
session.findById("wnd[0]/tbar[0]/btn[3]").press


Else 'If data exist for the WBS in S_ALR, then save it into a file

'session.findById("wnd[0]/shellcont/shell/shellcont[2]/shell").hierarchyHeaderWidth = 232 ' Clear?
session.findById("wnd[0]/mbar/menu[0]/menu[3]").Select
session.findById("wnd[1]/usr/radLGRWO-X_EXPONLY1").Select
session.findById("wnd[1]/usr/ctxtLGRWO-OUT_FILE").Text = FilePath & WBS & ".dat"
session.findById("wnd[1]/usr/radLGRWO-X_EXPONLY1").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[2]/usr/btnSPOP-VAROPTION2").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[1]/usr/btnBUTTON_YES").press



End If  'End If for checking if data exist for the WBS in S_ALR

i = i + 1
Loop



' Parameters
'FilePath = Cells(2, 3)
'Sheets.Add.Name = "RESULTS"

'------------------  Read the files
AddTime = Format(Now(), "yyyymmddhhmmss")
SheetName = "RESULTS" & AddTime

Sheets.Add.Name = SheetName
Sheets("Macro2").Activate
i = 2


k = 2
'================Start reading data files
'For each WBS in column A in Macro2 sheet
Do While ThisWorkbook.Sheets("Macro2").Cells(i, 1) <> ""
' Check if any datafile exist, by checking status column B in Macro 2 sheet
If ThisWorkbook.Sheets("Macro2").Cells(i, 2) <> "No Actual Cost, no Commitment and no Plan Cost exist" Then

WBS = ThisWorkbook.Sheets("Macro2").Cells(i, 1)

Fil = FilePath & WBS & ".xls"

Name FilePath & WBS & ".dat" As Fil


'Workbooks.Open Filename:=Fil
    Set wbkA = Workbooks.Open(Filename:=Fil)
   
   
    ' Count Rows in WBS file
    j = 2
    Do While Sheets(WBS).Cells(j, 1) <> ""
      j = j + 1
     
    Loop
   
   
    Range_String = "A" & 2 & ":Q" & j
    Range(Range_String).Copy
    ThisWorkbook.Activate
    Sheets(SheetName).Activate
   
    '*** Select the destination cell and copy
    Range_Target = "B" & k
    Range(Range_Target).Select
    ActiveSheet.Paste
    ActiveSheet.Cells(k, 1) = WBS
    
     ' ----- Close WBS file
    Application.DisplayAlerts = False
    wbkA.Activate
    wbkA.Close
    Application.DisplayAlerts = True

k = k + j - 3 ' This variable keep track of printing position
End If ' End if checking the B-column in Macro2 sheet
i = i + 1
Loop

RESULT_sheet = SheetName

i = 2

WBS = ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1)

Do While ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 2) <> ""

' Calculate diff between Plan Cost and Assigned cost, if Plan cost is to low, print  mount
If CDec(ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 6)) < CDec(ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 5)) Then
ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 19) = CDec(ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 5)) - CDec(ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 6))
End If

If Left(ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 2), 3) <> "All" Then

If ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1) <> "" Then
WBS = ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1)
End If

If ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1) = "" Then
ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1) = WBS
End If

End If

i = i + 1
Loop
i = i - 1
'Range_String = "A" & i & ":R" & i
'ThisWorkbook.Sheets(RESULT_sheet).Range(Range_String) = ""
'ThisWorkbook.Sheets(RESULT_sheet).Range(Range_String).Delete

'Cells(Range_String) = ""
Range_String = i & ":" & i

Rows(Range_String).Select
Selection.Delete Shift:=xlUp


Columns("G:R").Select
Selection.Delete Shift:=xlToLeft

ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 1) = "WBS"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 2) = "Cost Element"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 3) = "Actual Cost"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 4) = "Commitments"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 5) = "Assigned Cost"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 6) = "Plan Cost"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 7) = "Adjustment needed"

ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 😎 = "Description"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 9) = "Amount"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 10) = "Curr"
ThisWorkbook.Sheets(RESULT_sheet).Cells(1, 11) = "CE"


'==== Create PRIM

i = 2
WBSold = ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1)


Do While ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1) <> ""

WBS = ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 1)

'Mark cells for each new WBS to make more easy to read
If WBS <> WBSold Then
RangeBoarder = "A" & i & ":K" & i
Range(RangeBoarder).Borders(xlEdgeTop).LineStyle = xlDash
End If

CE = Left(ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 2), 6)

' If external cost adjustment needed, then pepare PRIM
If Left(CE, 1) = "4" And ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 7) <> "" Then

TodayDate = Format(Now(), "yyyymmdd")

ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 😎 = "PRIM " & CE & " Plan Cost Adjustment"
ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 9) = ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 7)
ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 10) = "KRW"
ThisWorkbook.Sheets(RESULT_sheet).Cells(i, 11) = CE

End If

WBSold = WBS
i = i + 1
Loop

      
' Resizing columne
Columns("A").ColumnWidth = 18.15
Columns("B").ColumnWidth = 38
Columns("C").ColumnWidth = 10
Columns("D").ColumnWidth = 11.9
Columns("E").ColumnWidth = 12.6
Columns("F").ColumnWidth = 12.6
Columns("G").ColumnWidth = 16.6
Columns("H").ColumnWidth = 29.5
Columns("I").ColumnWidth = 8
Columns("J").ColumnWidth = 3.71
Columns("K").ColumnWidth = 6
               


End Sub

However, I am nowhere near gathering all that excel data.

Your help will be very very much appreciated!!

BR,

Cathy

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

holger_khn
Contributor
‎02-11-2014 12:07 AM
0 Kudos

Hello Cathy.

As MHTML is an link to external Excel we will not be able to read Excel in place inside SAP IW33 cost tab.

But you can read directly from ALV-grid in cost tab:



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


    Session.FindById("wnd[0]/tbar[0]/okcd").Text = "/niw33"


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


    Session.FindById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = SO


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


    Session.FindById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1107/tabsTS_1100/tabpKOAU/ssubSUB_AUFTRAG:SAPLICO1:1100/btnPUSH1").press


    ThisWorkbook.Sheets(2).Activate


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


    Set Cols = Table_Message.ColumnOrder()


    ROW_COUNT = Table_Message.RowCount() - 1


    COL_COUNT = Table_Message.ColumnCount() - 1


    


    For i = 0 To ROW_COUNT


        For j = 0 To COL_COUNT


            DoEvents


            If i = 0 Then ThisWorkbook.Sheets(2).Cells(i + 1, j + 1).Value = Table_Message.GetDisplayedColumnTitle(CStr(Cols(j)))


            ThisWorkbook.Sheets(2).Cells(i + 2, j + 1).Value = Table_Message.GetCellValue(i, CStr(Cols(j)))


        Next


    Next


    


Columns.AutoFit
Show replies
Show replies
Former Member
‎02-11-2014 2:02 AM
0 Kudos

Hello Holgar!

Thank you very much for your reply.

My purpose was to be able to gather all the ALV-grid info into one excel file.

(For many diff. order numbers, all gathered into one file)

When I tried the above script, the furthest it took me was to display mode...

Is there any way I am able to transfer this into excel file (and save)?

Thank you once again!

BR, Cathy

* What I have so far *

Sub GetPlanCostData()
'---------------------------------
  '---------------------------------
' Parameters


'---------------------------------
   '-------- SAP LOGIN --------------
   '---------------------------------


    'If Right(CLPdirName, 1) <> "\" Then CLPdirName = CLPdirName & "\"

    On Error Resume Next

    If SAPApp Is Nothing Then
        Set SapGuiApp = GetObject("SAPGUI")
        Set SAPApp = SapGuiApp.GetScriptingEngine
       ' If SAPApp Is Nothing Then GoTo Exit_AllReport
    End If
    If Connection Is Nothing Then
        Set Connection = SAPApp.Children(0)
    End If
    If Session Is Nothing Then
        Set Session = Connection.Children(0)
    End If

Set GRID = Session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
GRID.setCurrentCell -1, ""
Row = GRID.currentCellRow
CELL = GRID.getcellvalue(Row, "COLUMN_NAME")

' Get data for paging-down

RowCount = GRID.RowCount

VisibleRows = GRID.VisibleRowCount

VisiblePages = (RowCount / VisibleRows) - 1

' Get current column selected by user

SelectedColumn = GRID.CurrentCellColumn

GRID.selectColumn SelectedColumn

GRID.FirstVisibleRow = GRID.RowCount - 1

'Page to bottom of ALV

For Pages = 1 To VisiblePages

    GRID.FirstVisibleRow = (Pages * VisibleRows)

Next

' Select all text

GRID.contextMenu

GRID.SelectContextMenuItemByPosition "0"

' Return to first row

GRID.FirstVisibleRow = 0

GRID.ClearSelection

GRID.currentCellRow = 0

GRID.CurrentCellColumn = SelectedColumn

'MsgBox "Number of records: " & Grid.RowCount, vbInformation, "Count of Records"

' Cancel popup message (Not all data has been copied into the clipboard)

On Error Resume Next

    Session.FindById("wnd[1]/tbar[0]/btn[0]").press

On Error GoTo 0

    If Err = 0 Then flgSapIsOpen = True
    Err.Clear
   
    On Error GoTo 0

    If flgSapIsOpen = False Then
        Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
        Set Connection = SapGuiApp.OpenConnection(CLPsapName, True, False)
      '  If Connection Is Nothing Then GoTo Exit_AllReport
   '?     Set session = Connection.Children(0)

        If Not IsObject(Connection) Then
            Set Connection = SAPApp.Children(0)
        End If
        If Not IsObject(Session) Then
            Set Session = Connection.Children(0)
        End If

    End If

'If Not IsObject(Application) Then
'  Set SapGuiAuto = GetObject("SAPGUI")
  ' Set Application = SapGuiAuto.GetScriptingEngine
'End If
If Not IsObject(Connection) Then
   Set Connection = Application.Children(0)
End If
If Not IsObject(Session) Then
   Set Session = Connection.Children(0)
End If
If IsObject(wscript) Then
   wscript.ConnectObject Session, "on"
   wscript.ConnectObject Application, "on"
End If

'Start Processing in SAP
i = 2

SO = Cells(i, 1)


    Session.FindById("wnd[0]").maximize
    Session.FindById("wnd[0]/tbar[0]/okcd").Text = "/niw33"
    Session.FindById("wnd[0]").sendVKey 0
    Session.FindById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = SO
    Session.FindById("wnd[0]/tbar[1]/btn[8]").press
    Session.FindById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1107/tabsTS_1100/tabpKOAU/ssubSUB_AUFTRAG:SAPLICO1:1100/btnPUSH1").press
    ThisWorkbook.Sheets(2).Activate
    Set Table_Message = Session.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell")
    Set Cols = Table_Message.ColumnOrder()
    ROW_COUNT = Table_Message.RowCount() - 1
    COL_COUNT = Table_Message.ColumnCount() - 1
   
    For i = 0 To ROW_COUNT
        For j = 0 To COL_COUNT
            DoEvents
            If i = 0 Then ThisWorkbook.Sheets(2).Cells(i + 1, j + 1).Value = Table_Message.GetDisplayedColumnTitle(CStr(Cols(j)))
            ThisWorkbook.Sheets(2).Cells(i + 2, j + 1).Value = Table_Message.getcellvalue(i, CStr(Cols(j)))
        Next
    Next
   
Columns.AutoFit


SAP_Workbook = "Worksheet in Basis (1)"
EXCEL_Path = "C:\SAP\"
myWorkbook = "Report.xlsx"

End Sub

holger_khn
Contributor
‎03-03-2014 12:30 AM
0 Kudos

Hi Cathy.

My code is catching the SAP ALV cell value and write it into second sheet from your workbook where code is embedded.

Can you explain more in Detail what from my code is not working for you?

Thanks.

Cheers

Holger

Answers (0)

Ask a Question