Multiple selection - VBA

Former Member — Mon, 02 Feb 2015 14:29:38 GMT

Hello All,

first of all, I hope that my English will be understandable.

Description:

I must create a report for sales orders by planned deliver date. Most of info what I need are in VBAP, but there is no delivery date there, only in VBAK. I download VBAK by VDATU, result is a list with 500-3k pcs of orders. Next step shouId be I take these order numbers and "insert" them into VBAP-VBELN, but I'm not able to find out how can I do it in VBA (multiple selection, therefore I download that by ERDAT (large interval, because the orders can arrive to system more weeks before delivery date). Result is a code what is downloading VBAP ~ten minutes... It is not acceptable. I would like to choose a simplier example (where problem is the same): MARA.

I have only 2-300 article number per list but I have to download 80k articles because I can filter only prefix and H% is not so exact filter...

One-dimensional array is the only one solution what I tried for that but RFC call is false in that case.

Sub MARA() '(user As String, password As String)

Const TABLA As String = "MARA"

Const UZI1 As String = "there is no connection to R/3"

Const UZI2 As String = "something wrong"

Const CLIENT As String = "300"

Const LANGUAGE As String = "your logon language"

Const SYSTEM As String = "your system"

Const user As String = "your username"

Const password As String = "your pw"

Dim Tomb As Long

Dim LTomb As Long

’store material numbers from A column of xls into matDatabank

LTomb = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

Tomb = 2

If LTomb = 2 Then

matDatabank = "'" & Worksheets(1).Cells(Rand, 1).Value & "'"

Else

Do While Worksheets(1).Cells(Tomb, 1).Value <> "" And Tomb < LTomb

matDatabank = matDatabank + "'" & Worksheets(1).Cells(Tomb, 1).Value & "', "

Tomb = Tomb + 1

Loop

matDatabank = matDatabank + "'" & Worksheets(1).Cells(Tomb, 1).Value & "'"

End If

matDatabank = Left(matDatabank, Len(matDatabank) - 1)

Set conn = CreateObject("SAP.functions")

conn.Connection.SYSTEM = SYSTEM

conn.Connection.CLIENT = CLIENT

conn.Connection.LANGUAGE = LANGUAGE

conn.Connection.user = user

conn.Connection.password = password

retcd = conn.Connection.logon(0, True)

If retcd <> True Then

MsgBox UZI1, vbCritical

Exit Sub

End If

Set RFC_READ_TABLE = conn.Add("RFC_READ_TABLE")

Set equery_tab = RFC_READ_TABLE.Exports("QUERY_TABLE")

Set toptions = RFC_READ_TABLE.Tables("OPTIONS")

Set tdata = RFC_READ_TABLE.Tables("DATA")

Set tfields = RFC_READ_TABLE.Tables("FIELDS")

equery_tab.Value = TABLA

toptions.AppendRow

toptions(1, "TEXT") = "MATNR IN" & "(" & matDatabank & ")"

’HM* selection needs more minutes because of huge amount of materials

'toptions.AppendRow

'toptions(1, "TEXT") = "MATNR like 'HM%'"

tfields.AppendRow

tfields(1, "FIELDNAME") = "MATNR"

tfields.AppendRow

tfields(2, "FIELDNAME") = "MATKL"

If RFC_READ_TABLE.Call = True Then

MsgBox "success"

Else: MsgBox (UZI2)

End If

conn.Connection.logoff

End Sub

Thank you for you help!

Regards,

Csaba

Re: Multiple selection - VBA

Former Member — Tue, 03 Feb 2015 10:33:47 GMT

I tried this too, unsuccessfully:

x = Range("A65536").End(xlUp).Row

ReDim matDatabank(x)

For i = 2 To x

matDatabank(i) = "'" & Cells(i, 1).Value & "'"

Next i

For i = 2 To x

toptions.AppendRow

toptions(i - 1, "TEXT") = "MATNR EQ " & matDatabank(i)

Next i

Please help how can I use one dimensional array as selection.

Thanks,

Csaba

topic Multiple selection - VBA in Application Development and Automation Discussions

Multiple selection - VBA

Re: Multiple selection - VBA