Copy / paste dynamic named range from Excel to SAP with VBA-CodePudding

I'm trying to copy a dynamic (named) range of cells from Excel to SAP with the help of a VBA script (manually this would be done through a copy / paste from a clipboard ). The range of values from Excel need to be copied into the multiple selection window of SAP. But unfortunately it doesn't work and I don't know how to solve it. This is where I get an error.

Vendors = Sheets("vendors").Range("UniqueVendors").Value

Can anyone help me with this?

Please see here below the code I'm using:

Public SapGuiAuto, WScript, msgcol
Public objGui  As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession

Sub SAPDownloadReport()

Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
Set objConn = objGui.Children(0)
Set session = objConn.Children(0)
Dim CompanyCode As String
Dim ClearingStartDate As Date
Dim ClearingEndDate As Date
Dim SAPLayout As String
Dim FolderPath As String
Dim Filename As String
Dim Vendors As Integer


CompanyCode = Sheets("report").Range("B2").Value
ClearingStartDate = Sheets("report").Range("B3").Value
ClearingEndDate = Sheets("report").Range("B4").Value
SAPLayout = Sheets("report").Range("B5").Value
FolderPath = Sheets("report").Range("B6").Value
Vendors = Sheets("vendors").Range("UniqueVendors").Value

'Insert your SAP Script here

session.FindById("wnd[0]").maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nFBL1N"
session.FindById("wnd[0]").sendVKey 0
session.FindById("wnd[0]/usr/btn%_KD_LIFNR_%_APP_%-VALU_PUSH").press
session.FindById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = Vendors
session.FindById("wnd[1]").sendVKey 0
session.FindById("wnd[1]/tbar[0]/btn[8]").press
session.FindById("wnd[0]/usr/radX_CLSEL").Select
session.FindById("wnd[0]/usr/ctxtKD_BUKRS-LOW").Text = CompanyCode
session.FindById("wnd[0]/usr/ctxtSO_AUGDT-LOW").SetFocus
session.FindById("wnd[0]/usr/ctxtSO_AUGDT-LOW").caretPosition = 0
session.FindById("wnd[0]").sendVKey 4
session.FindById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = ClearingStartDate
session.FindById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20211001,20211001"
session.FindById("wnd[0]/usr/ctxtSO_AUGDT-HIGH").SetFocus
session.FindById("wnd[0]/usr/ctxtSO_AUGDT-HIGH").caretPosition = 0
session.FindById("wnd[0]").sendVKey 4
session.FindById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").focusDate = ClearingEndDate
session.FindById("wnd[1]/usr/cntlCONTAINER/shellcont/shell").selectionInterval = "20211105,20211105"
session.FindById("wnd[0]/usr/ctxtPA_VARI").Text = SAPLayout
session.FindById("wnd[0]/usr/ctxtPA_VARI").SetFocus
session.FindById("wnd[0]/usr/ctxtPA_VARI").caretPosition = 12
session.FindById("wnd[0]/tbar[1]/btn[8]").press
session.FindById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.FindById("wnd[1]/usr/ctxtDY_PATH").Text = FolderPath
session.FindById("wnd[1]/usr/ctxtDY_FILENAME").Text = "EXPORT.XLSX"
session.FindById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 5
session.FindById("wnd[1]/tbar[0]/btn[11]").press


MsgBox "Extraction done"

End Sub

(Maybe copying a Named Range isn't the solution maybe copying the Clipboard from Excel or something different is?)

CodePudding user response：

Don't know the SAP part but instead of copying you are trying to assign your range to an integer. Replace your "vendors = .." line with:

Sheets("vendors").Range("UniqueVendors").Copy

So no need to assign to anything.

CodePudding user response：

If the Excel data is in the clipboard with the .Copy command, it can be pasted into SAP as follows:

...
session.findById("wnd[0]/usr/btn%_KD_LIFNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
...

Regards, ScriptMan