VBA: clear data from an object(Array) (SAP Object in VBA)

[anilmalhotra]

I am an SAP developer with little knowledge of VBA. I am creating an VBA to post data to SAP using a BAPI. The coding is working fine & I am able to post data into SAP. My problem is I have several rows in my excel, I have to loop the rows one by one & upload data in SAP, after each upload i need to clear the contents, I am unaware of what syntax needs to be used to clear the data of the object variable. Below is the code snippet..

[/FONT]Set objbapicontrol = CreateObject("SAP.Functions")Set objbapi = objbapicontrol.Add("BAPI_PO_CREATE1")
Set poheader = objbapi.exports.Item("POHEADER")
Set poitems = objbapi.tables.Item("POITEM")


For Each row In [POHEAD].Rows    '##PO Header
 If row.Columns(row.ListObject.ListColumns("SAP_PO_NUM").Index).Value = "" Then
    ponum = (row.Columns(row.ListObject.ListColumns("PONumber").Index).Value)
    poheader.Value("COMP_CODE") = (row.Columns(row.ListObject.ListColumns("COCD").Index).Value)
i = "00001"
    n = 1
'###Loop Detail
 For Each rowd In [PODET].Rows         '##PO Detail
     If (rowd.Columns(rowd.ListObject.ListColumns("PONumber").Index).Value) = ponum Then
     poitem = (rowd.Columns(rowd.ListObject.ListColumns("Itemnumber").Index).Value)
     poitems.Rows.Add
     poitemsx.Rows.Add
     poitems.Value(n, "PO_ITEM") = i
     poitems.Value(n, "MATERIAL") = Material
Next          '##PO Detail
  returnfunc = objbapi.call
  ponumber = objbapi.imports("EXPPURCHASEORDER")
  Set retmess = objbapi.tables.Item("RETURN")
Set poitems = Nothing

next[FONT=Helvetica Neue]

I am using the code <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">"Set POITEMS = NOTHING"</code> but again when I set the object, the previous data is not cleared & duplicate enteries are created in SAP
Thanks in Advance!! Regards, Anil Malhotra

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Your code has these lines:

For Each row In [POHEAD].Rows

For Each rowd In [PODET].Rows

That is saying loop round the rows, in what Excel calls a Named Range, in the worksheet. A Named Range is a group of cells.

The object poitems is really only a temporary variable used as part of the process. The actual data is in the Named Ranges.

So my guess is that you will need to clear the worksheet rather than clear the objects in VBA.

I don't know how your worksheets are created. Is a block of data copied from elsewhere? Is the worksheet supposed to be a copy of all the data fed into SAP? How are the Named Ranges POHEAD and PODET created? Those kinds of questions will need to be answered before I can say much more.

I mean, you could just clear the whole worksheet, or just the area with the data, or just the Named Ranges.

I hope this helps.