Skip to Content

Adding rows in excel vba from SAP

I'm writing macros to automate a process and I need to be able to add the total quantity from a column in SAP in vba. The number of rows will be larger and smaller each time so I need to keep that in account. How do I go about doing that and storing the total number so that I can put it in another field in SAP?

question.png (17.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Feb 02 at 04:44 PM

    Hello Casey,

    sure, please take a look at your ID, it contains tblSAPMV50ATC_LIPS_OVER. The prefix tbl means that you don't have a GridView, as I assume, rather a TableControl. Here an example how to do the same with a TableControl. In this example I sum the column Steuer, which is column six.

    Set scrollBar = session.findById("wnd[0]/usr/tblSAPMBIBSTC535").VerticalScrollbar
    For i = 0 To scrollBar.Maximum
      session.findById("wnd[0]/usr/tblSAPMBIBSTC535").VerticalScrollbar.Position(i)
      Steuer = Steuer + CDbl(session.findById("wnd[0]/usr/tblSAPMBIBSTC535").GetCell(0, 6).Text)
    Next
    MsgBox CStr(Steuer)
    

    I read always the row 0 and set the scrollbar with each loop one position further.

    Best regards
    Stefan


    Add comment
    10|10000 characters needed characters exceeded

  • Jan 27 at 05:44 AM

    Hello Casey,

    welcome in the SAP Community.

    As far as I can see you have GridView control. In this case it is very easy, use the method RowCount to get the total number of rows. Here an example:

    cntRows = session.findById("wnd[0]/usr/cntlBCALVC_EVENT1_CONT1/shellcont/shell").RowCount
    MsgBox CStr(cntRows)
    

    The variable cntRows contains the total number of rows of the GridView.

    Best regards
    Stefan


    cntrows.jpg (67.6 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Sorry for asking the question poorly, but I need to loop through and add the total Qty's up. So in this case it would be 1+1+1+1+1 = 5 and I need to store that 5 value. I just dont know how to write this loop. thanks.

  • Jan 30 at 04:34 AM

    Hello Casey,

    sorry for this misunderstanding, you want to sum up all entries. Here an example how to do that.

    Set table = session.findById("wnd[0]/usr/cntlBCALV_GRID_DEMO_0100_CONT1/shellcont/shell")
    Set Columns = table.ColumnOrder()
    rowTitle = CStr(Columns(7)) 'Belegt
    For i = 0 To table.RowCount - 1
      table.firstVisibleRow = i
      seatsOCC = seatsOCC + CInt(table.GetCellValue(i, rowTitle))
    Next
    MsgBox CStr(seatsOCC)
    

    In my example I sum up the column Belegt and store it in the variable seatsOCC.

    Best regards
    Stefan


    seatsoccsum.jpg (293.9 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01 at 07:18 AM

    Hello Casey,

    first of all it works, also in VBA.

    You use for your session the variable name SAPSession, but you set the variable table with session.FindById..., here it is necessary to use also SAPSession.

    Please use the directive Option Explicit on top of your code. This helps to prevent such kinds of errors.

    Best regards
    Stefan


    addcolumn.jpg (80.8 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • I did the exact same code as you and am getting a Run-time error '13' : Type Mismatch on my line:

      Set Table = session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV50A:1102/tblSAPMV50ATC_LIPS_OVER")

      Why is that?

      I checked out your previous posts and i believe I have the correct References selected:

      question2.png

      Thank you!

      question2.png (62.4 kB)